即使表中不存在月份，也显示所有月份名称 [英] Display all the month names even the months donot exists in the table

问题描述

我有带有以下数据的表名工资单"

I have table names ''payroll'' with following data

month , pay
January , 1200
March , 1500
December , 2000

我想要以下结果

I want the following result

Janury , 1200
February , 00
March , 1500
April , 00
May , 00
June , 00
July , 00
August , 00
September , 00
October , 00
November , 00
December , 00

推荐答案

朋友，
尝试此代码

Hi Friend,
Try this code

SELECT * INTO #tmppay 
from (
SELECT number, DATENAME(MONTH, '2012-' + CAST(number as varchar(2)) + '-1') monthname,'00' as pay
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12
) as tp1
select tmp.[monthname], isnull(p.Pay,00) from #tmppay tmp 
left  outer JOIN  pay p on tmp.[monthname]=p.[month]
drop TABLE #tmppay

朋友，

您可以尝试上述代码的替代方法

Hi Friend,

you can try the alternate of above code

with TempPay(number,[monthname],pay)
as
(
SELECT number, DATENAME(MONTH, '2012-' + CAST(number as varchar(2)) + '-1') monthname,00 as pay
FROM master..spt_values
 --inner join pay on [month]= [monthname]
WHERE Type = 'P' and number between 1 and 12

)
select tp.[monthname], isnull(p.Pay,0) as pay from TempPay tp

LEFT outer JOIN  pay p on p.[month]=tp.[monthname]

您也可以尝试这样....

Hi,

You can try like this also....

SELECT T.monthname 'Month', ISNULL(P.pay,0) 'Pay'
FROM payroll P
LEFT OUTER JOIN (SELECT DATENAME(MONTH, '2013-' + CAST(number as varchar(2)) + '-1') monthname
                 FROM master..spt_values  
                 WHERE Type = 'P' and number between 1 and 12) ON T.monthname=P.month

问候，
GVPrabu

Regards,
GVPrabu

这篇关于即使表中不存在月份，也显示所有月份名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！