SQL 查询 统计

[wangshunqi]

table登记
Id Int（4）
BeChecker Nvarchar（16）
Dept Nvarchar（16）
Explain Nvarchar（256）
Resutl Chra（1）
Checker Nvarchar（16）
CheckTime Datetime
在这样一张表中
统计每个用发表的数据，
月份 1 2 3 4 5 6 7 8 9 10 11 12
名字
姓名1
姓名2

以上面这种形式去统计，有什么好的办法，因为是使用asp 所以请说说思路
Select BeChecker,month(CheckTime),count(Id) as 总数 from 查岗登记 where year(CheckTime)='2009' group by month(CheckTime),BeChecker
我使用了这种查询语句，但只能查出存在的，需要的是每个月都显示，没有的就显示为0，应该怎么去写这条语句

[_NET2004]

你把你字段说明一下啥

[ljhcy99]

Select 查岗登记 .BeChecker,month(查岗登记 .CheckTime) as month,count(查岗登记 .Id) as 总数
from
(select 1 as mon
union all
select 2 as mon
... 1月写到12 月，我省略了
union all
select 12 as mon) A
left join 查岗登记
on(查岗登记 .month=A.mon and year(查岗登记 .CheckTime)='2009' )
group by month(CheckTime),BeChecker

[CutBug]

sum case就可以了

[wengyuli]

isnull(字段,0)

若为空显示0

[CutBug]

Select BeChecker,
sum(case when month(CheckTime)=1 then 1 else 0 end) as 1月,
sum(case when month(CheckTime)=2 then 1 else 0 end) as 2月,
....
sum(case when month(CheckTime)=12 then 1 else 0 end) as 12月
from 查岗登记 where year(CheckTime)='2009' group by BeChecker 

[Terry717]

Select BeChecker,
sum(case when month(CheckTime)=1 then 1 else 0 end) as month1,
sum(case when month(CheckTime)=2 then 1 else 0 end) as month2,
sum(case when month(CheckTime)=3 then 1 else 0 end) as month3,
sum(case when month(CheckTime)=4 then 1 else 0 end) as month4,
sum(case when month(CheckTime)=5 then 1 else 0 end) as month5,
sum(case when month(CheckTime)=6 then 1 else 0 end) as month6,
sum(case when month(CheckTime)=7 then 1 else 0 end) as month7,
sum(case when month(CheckTime)=8 then 1 else 0 end) as month8,
sum(case when month(CheckTime)=9 then 1 else 0 end) as month9,
sum(case when month(CheckTime)=10 then 1 else 0 end) as month10,
sum(case when month(CheckTime)=11 then 1 else 0 end) as month11,
sum(case when month(CheckTime)=12 then 1 else 0 end) as month12
from 查岗登记where year(CheckTime)='2009' group by BeChecker 

[OPHenry]

up

[wangshunqi]

谢谢各位了
学艺不精啊，数据库连个皮笔都不懂，羞愧啊

[ASPNETDB]

create table [t_demo_EqDate]([Id] int,[EqId] int,[EqValue] numeric(5,3),[EqTime] datetime)
insert [t_demo_EqDate]
select 91,1,92.712,'2008-3-31' union all
select 92,1,52.507,'2008-4-1' union all
select 93,1,63.6,'2008-4-2' union all
select 94,1,38.347,'2008-4-3' union all
select 95,1,68.051,'2008-4-4' union all
select 96,1,68.975,'2008-4-5' union all
select 97,1,4.905,'2008-4-6' union all
select 98,1,41.767,'2008-4-7' union all
select 99,1,48.011,'2008-4-8' union all
select 100,1,90.509,'2008-4-9' union all
select 101,2,83.558,'2008-1-1' union all
select 102,2,27.526,'2008-1-2' union all
select 103,2,72.113,'2008-1-3' union all
select 104,2,95.702,'2008-1-4' union all
select 105,2,74.275,'2008-1-5' union all
select 106,2,20.829,'2008-1-6' union all
select 107,2,40.767,'2008-1-7' union all
select 108,2,56.027,'2008-1-8' union all
select 109,2,83.615,'2008-1-9' union all
select 110,2,69.288,'2008-1-10' union all
select 111,2,38.632,'2008-1-11'

---查询---

select
cast(year(EqTime) as varchar(10))+'年'+right('00'+cast(datepart(m,EqTime) as varchar(2)),2)+'月' as 月,
sum(EqValue) as EqValue
from [t_demo_EqDate]
group by cast(year(EqTime) as varchar(10))+'年'+right('00'+cast(datepart(m,EqTime) as varchar(2)),2)+'月'