日期:2014-05-17 浏览次数:20622 次
select 日期,count(*) as 品种数, sum(case when 价格 >= 10 then 1 else 0 end) as 大于等于10品种数 from tb group by 日期
------解决方案--------------------
--> 测试数据:@T declare @T table([日期] datetime,[品种] varchar(1),[价格] int) insert @T select '2012/1/1','a',8 union all select '2012/1/1','b',7 union all select '2012/1/1','c',11 union all select '2012/1/1','d',12 union all select '2012/1/1','e',9 union all select '2012/1/1','f',10 union all select '2012/1/1','g',13 union all select '2012/1/2','a',11 union all select '2012/1/2','b',9 union all select '2012/1/2','c',8 union all select '2012/1/2','d',7 union all select '2012/1/2','e',10 union all select '2012/1/2','f',11 union all select '2012/1/3','a',10 union all select '2012/1/3','b',11 union all select '2012/1/3','c',9 union all select '2012/1/3','d',11 union all select '2012/1/3','e',12 union all select '2012/1/3','f',8 union all select '2012/1/3','g',7 union all select '2012/1/3','h',9 union all select '2012/1/3','i',10 union all select '2012/1/3','j',12 union all select '2012/1/3','k',8 select 日期, count(1) as 品种数, sum(case when 价格>10 then 1 else 0 end) as 大于等于10元品种数 from @T group by 日期 /* 日期 品种数 大于等于10元品种数 ----------------------- ----------- ----------- 2012-01-01 00:00:00.000 7 3 2012-01-02 00:00:00.000 6 2 2012-01-03 00:00:00.000 11 4 */