sql 语句 内容如图所示
0
最佳答案
0
楼上的都行，1楼的可以稍微修改下，改为纯连接，而不是子查询+连接。
CREATE TABLE #t(code VARCHAR(50),num INT) INSERT INTO #t VALUES('01',10),('01',20),('02',30) SELECT * FROM #t --方案一： SELECT t1.code,COUNT(t2.code),COUNT(t3.num),COUNT(t4.num) FROM (select distinct code from #t) t1 LEFT JOIN #t t2 ON t2.num<11 AND t2.code=t1.code LEFT JOIN #t t3 ON t3.num>=11 AND t3.num<=20 AND t3.code=t1.code LEFT JOIN #t t4 ON t4.num>20 AND t4.code = t1.code GROUP BY t1.code --方案二 SELECT code, SUM(CASE WHEN t1.num<11 THEN 1 ELSE 0 END), SUM(CASE WHEN t1.num BETWEEN 11 AND 20 THEN 1 ELSE 0 END), SUM(CASE WHEN t1.num>20 THEN 1 ELSE 0 END) FROM #t t1 GROUP BY t1.code
测试语句，呵呵~
收获园豆：1
其他回答(2)
0
select t1.类型,t1.数量 as 金额<11,t2.数量 as 11<=金额<=20,t3.数量 as 金额>20
from
(select 类型,count(*) 数量 from t group by 类型 where t.金额<11)t1 join
(select 类型,count(*) 数量 from t group by 类型 where t.金额<=20 and t.金额 >=11)t2
on t1.类型=t2.类型 join
(select 类型,count(*) 数量 from t group by 类型 where t.金额>20)t3
on t2.类型 = t3.类型
大概思路是这样，我这里没有数据库，写的可能有错误，就是个大概思路，你可以参考一下
收获园豆：3
0
select
类型，
sum(case when 金额<11 then 1 else 0 end) as col1,
sum(case when 金额>=11 and 金额<=20 then 1 else 0 end) as col2,
sum(case when 金额>20 then 1 else 0 end) as col3
from table
group by 类型
收获园豆：1
