SELECT b.year, b.month, Amount = ISNULL(b.Amount, 0) FROM ( SELECT DISTINCT YEAR(CreateTime) AS 'Year' FROM dbo.[Contract] ) c CROSS JOIN ( SELECT number FROM master.dbo.spt_values WHERE type = 'p' AND number BETWEEN 1 AND 12 ) a LEFT JOIN ( SELECT YEAR(CreateTime) AS 'Year' , MONTH(CreateTime) AS 'Month' , SUM(ContractAmount) AS 'Amount' FROM dbo.[Contract] GROUP BY YEAR(CreateTime) , MONTH(CreateTime) ) b ON c.year = b.Year AND a.number = b.Month
create table Contract (ContractDate varchar(16), ContractAmount decimal(10,2))insert into Contract select '2013-07-03', 20000.00 union all select '2013-06-26', 450000.00 union all select '2013-06-26', 20000.00 union all select '2013-07-05', 20000.00 union all select '2013-07-19', 56000.00 union all select '2013-07-19', 7000.00 select a.year,a.month,isnull(b.Amount,0) '金额' from (select 2013 'Year', number 'Month' from master.dbo.spt_values where type='P' and number between 1 and 12) a left join (select year(ContractDate) 'Year', month(ContractDate) 'Month', sum(ContractAmount) 'Amount' from dbo.[Contract] group by year(ContractDate),month(ContractDate)) b on a.Year=b.Year and a.Month=b.Month/* year month 金额 ----------- ----------- -------------------- 2013 1 0.00 2013 2 0.00 2013 3 0.00 2013 4 0.00 2013 5 0.00 2013 6 470000.00 2013 7 103000.00 2013 8 0.00 2013 9 0.00 2013 10 0.00 2013 11 0.00 2013 12 0.00(12 row(s) affected) */
b.year,
b.month,
Amount = ISNULL(b.Amount, 0)
FROM
(
SELECT DISTINCT YEAR(CreateTime) AS 'Year' FROM dbo.[Contract]
) c
CROSS JOIN
(
SELECT number FROM master.dbo.spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 12
) a
LEFT JOIN
(
SELECT YEAR(CreateTime) AS 'Year' ,
MONTH(CreateTime) AS 'Month' ,
SUM(ContractAmount) AS 'Amount'
FROM dbo.[Contract]
GROUP BY YEAR(CreateTime) ,
MONTH(CreateTime)
) b
ON c.year = b.Year
AND a.number = b.Month
SELECT
c.year,
month = a.number,
Amount = ISNULL(b.Amount, 0)
create table Contract
(ContractDate varchar(16), ContractAmount decimal(10,2))insert into Contract
select '2013-07-03', 20000.00 union all
select '2013-06-26', 450000.00 union all
select '2013-06-26', 20000.00 union all
select '2013-07-05', 20000.00 union all
select '2013-07-19', 56000.00 union all
select '2013-07-19', 7000.00
select a.year,a.month,isnull(b.Amount,0) '金额'
from
(select 2013 'Year',
number 'Month'
from master.dbo.spt_values
where type='P' and number between 1 and 12) a
left join
(select year(ContractDate) 'Year',
month(ContractDate) 'Month',
sum(ContractAmount) 'Amount'
from dbo.[Contract]
group by year(ContractDate),month(ContractDate)) b on a.Year=b.Year and a.Month=b.Month/*
year month 金额
----------- ----------- --------------------
2013 1 0.00
2013 2 0.00
2013 3 0.00
2013 4 0.00
2013 5 0.00
2013 6 470000.00
2013 7 103000.00
2013 8 0.00
2013 9 0.00
2013 10 0.00
2013 11 0.00
2013 12 0.00(12 row(s) affected)
*/