storage表显示两个字段outDate,title
要求计算每一天的title为100的有多少个表里面例如有2006-3-1 100
2006-3-1 100
2006-3-2 100
2006-3-4 111
2006-3-5 100此时要实现是显示出2006-3-1 2
2006-3-2 1
2006-3-4 0 /*此行也可以不显示*/
2006-3-5 1
要求计算每一天的title为100的有多少个表里面例如有2006-3-1 100
2006-3-1 100
2006-3-2 100
2006-3-4 111
2006-3-5 100此时要实现是显示出2006-3-1 2
2006-3-2 1
2006-3-4 0 /*此行也可以不显示*/
2006-3-5 1
where title=100
group by outDate
from Storage
where title=100 and outDate=(select distinct outDate
from StorageOut
where title=100)可以的话请帮忙指正
insert into @storage select '2006-3-1',100
insert into @storage select '2006-3-1',100
insert into @storage select '2006-3-2',100
insert into @storage select '2006-3-4',111
insert into @storage select '2006-3-5',100select outDate,sum(case title when 100 then 1 else 0 end) as num from @storage group by outDate/*
outDate num
------------------------------------------------------ -----------
2006-03-01 00:00:00.000 2
2006-03-02 00:00:00.000 1
2006-03-04 00:00:00.000 0
2006-03-05 00:00:00.000 1
*/
insert into @t select '2006-3-1' ,100
union all select '2006-3-1' ,100
union all select '2006-3-2' ,100
union all select '2006-3-4' ,111
union all select '2006-3-5' ,100select outDate,count(*) from @t where title=100 group by outDate
insert into @storage select '2006-3-1',100
insert into @storage select '2006-3-1',100
insert into @storage select '2006-3-2',100
insert into @storage select '2006-3-4',111
insert into @storage select '2006-3-5',100select outDate,sum(case title when 100 then 1 else 0 end) as num from @storage group by outDate/*
outDate num
------------------------------------------------------ -----------
2006-03-01 00:00:00.000 2
2006-03-02 00:00:00.000 1
2006-03-04 00:00:00.000 0
2006-03-05 00:00:00.000 1
*/
insert into @t select '2006-3-1',100
union all select '2006-3-1',100
union all select '2006-3-2',100
union all select '2006-3-4',111
union all select '2006-3-5',100select
a,
个数=sum(case when b=100 then 1 else 0 end)
from @t
group by a/*
a 个数
----------------------- -----------
2006-03-01 00:00:00.000 2
2006-03-02 00:00:00.000 1
2006-03-04 00:00:00.000 0
2006-03-05 00:00:00.000 1(4 行受影响)
*/
count(title) as qty
from storage with(nolock)
where isnull(title,0) = 100
group by outdate
insert into storage select '2006-3-1', '100'
union all select '2006-3-1', '100'
union all select '2006-3-2', '100'
union all select '2006-3-4', '111'
union all select '2006-3-5', '100'select outDate,count(*) from storage
where title=100
group by outDate
drop table storage