有数据表 a
字段 name a b c d
001 1 2 3 4
001 5 6 7 4
001 2 3 4 8
002 1 2 3 4
002 3 2 2 5
003 2 2 2 4
问题：根据name 求出 a，b，c 三列的平均值？并且，再根据 name 求出 d 列里，小于等于4的个数比例和大于4个数的比例，每一个不同的name都要算。最好能放在一个表里！！
例如： name a平均 b平均 c平均 d小、等于deng4 d大于4
001 2.66 3.66 4.66 66% 33%
002 2 2 2.5 50% 50%
003 2 2 2 100% 0
最后，最好有个统计，就是所有的平均值和比例
比如： 总计 2.22 2.55 3.03 66% 33%
------解决方案--------------------
- SQL code
SELECT NAME,AVG(A) AS A,AVG(B) AS B,AVG(C) AS C,SUM(CASE WHEN D<=4 THEN 1 ELSE 0 END)*100.0/SUM(1) AS [<=4],SUM(CASE WHEN >4 THEN 1 ELSE 0 END)*100.0/SUM(1) AS [>4]FROM AGROUP BY NAMEUNION ALLSELECT NAME = '总计',AVG(A) AS A,AVG(B) AS B,AVG(C) AS C,SUM(CASE WHEN D<=4 THEN 1 ELSE 0 END)*100.0/SUM(1) AS [<=4],SUM(CASE WHEN >4 THEN 1 ELSE 0 END)*100.0/SUM(1) AS [>4]FROM A
------解决方案--------------------
- SQL code
--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([name] VARCHAR(3),[a] INT,[b] INT,[c] INT,[d] INT)INSERT [ta]SELECT '001',1,2,3,4 UNION ALLSELECT '001',5,6,7,4 UNION ALLSELECT '001',2,3,4,8 UNION ALLSELECT '002',1,2,3,4 UNION ALLSELECT '002',3,2,2,5 UNION ALLSELECT '003',2,2,2,4--------------开始查询--------------------------SELECT [name], AVG([a] * 1.0), AVG([b] * 1.0), AVG([c] * 1.0), SUM(CASE WHEN [d]<=4 THEN 1 ELSE 0 END) * 100.0 / COUNT([name])FROM [ta]GROUP BY [name]WITH ROLLUP ----------------结果----------------------------/* name ---- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------001 2.666666 3.666666 4.666666 66.666666666666002 2.000000 2.000000 2.500000 50.000000000000003 2.000000 2.000000 2.000000 100.000000000000NULL 2.333333 2.833333 3.500000 66.666666666666(4 行受影响)*/
------解决方案--------------------
- SQL code
--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([name] VARCHAR(3),[a] INT,[b] INT,[c] INT,[d] INT)INSERT [ta]SELECT '001',1,2,3,4 UNION ALLSELECT '001',5,6,7,4 UNION ALLSELECT '001',2,3,4,8 UNION ALLSELECT '002',1,2,3,4 UNION ALLSELECT '002',3,2,2,5 UNION ALLSELECT '003',2,2,2,4--------------开始查询--------------------------SELECT [name], AVG([a] * 1.0), AVG([b] * 1.0), AVG([c] * 1.0), SUM(CASE WHEN [d]<=4 THEN 1 ELSE 0 END) * 100.0 / COUNT([name])FROM [ta]GROUP BY [name]UNION ALL SELECT '统计', AVG([a] * 1.0), AVG([b] * 1.0), AVG([c] * 1.0), SUM(CASE WHEN [d]<=4 THEN 1 ELSE 0 END) * 100.0 / COUNT([name])FROM [ta]----------------结果----------------------------/* name ---- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------001 2.666666 3.666666 4.666666 66.666666666666002 2.000000 2.000000 2.500000 50.000000000000003 2.000000 2.000000 2.000000 100.000000000000统计 2.333333 2.833333 3.500000 66.666666666666(4 行受影响)*/