表 1
select number as 小时 from master..spt_values a where type='p' and number<24
表2
select hous as 小时, count(hous) AS 数量 from(select top 100 DATEPART(hh,CreateDate) AS hous from [HY_T_CARD_INFO])[HY_T_CARD_INFO] group by hous大家看出来了吧。。小时是共同的。 表1的
是0-24小时都有。。
表2的24小时里有些时段没有。要求结果这样 小时 数量
0 51
1 23
2 37
3 22
4 3
5 54
6 87
7 14
. .
. .
. .
select number as 小时 from master..spt_values a where type='p' and number<24
表2
select hous as 小时, count(hous) AS 数量 from(select top 100 DATEPART(hh,CreateDate) AS hous from [HY_T_CARD_INFO])[HY_T_CARD_INFO] group by hous大家看出来了吧。。小时是共同的。 表1的
是0-24小时都有。。
表2的24小时里有些时段没有。要求结果这样 小时 数量
0 51
1 23
2 37
3 22
4 3
5 54
6 87
7 14
. .
. .
. .
数量=(select 数量 from tb where 小时=a.number)
from master..spt_values a
where type='p' and number<24
select hous as 小时, count(hous) AS 数量 from(select top 100 DATEPART(hh,CreateDate) AS hous from [HY_T_CARD_INFO])[HY_T_CARD_INFO] group by hous
)b on a.number=b.小时
where a.type='p' and a.number<24
,count(b.CreateDate) as 数量
from master..spt_values a
left join [HY_T_CARD_INFO])[HY_T_CARD_INFO] as b
on a.number= DATEPART(hh,b.CreateDate)
where a.type='p' and a.number<24
group by a.number