求一个复杂的sql语句怎么写？
有数据表 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%
[解决办法]
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
[解决办法]
--> 测试数据：[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 行受影响)*/
[解决办法]
--> 测试数据：[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 行受影响)*/
[解决办法]
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a')BEGIN DROP TABLE AENDGOCREATE TABLE a( name VARCHAR(10), a INT, b INT, c INT, d INT)GOINSERT INTO aSELECT '001', 1, 2, 3, 4 UNION SELECT '001', 5, 6, 7, 4 UNIONSELECT '001', 2, 3, 4, 8 UNIONSELECT '002', 1, 2, 3, 4 UNIONSELECT '002', 3, 2, 2, 5 UNIONSELECT '003', 2, 2, 2, 4GOWITH t AS (SELECT name,SUM(a) / CAST(COUNT(1) AS DECIMAL(10,2)) AS avga,SUM(b) / CAST(COUNT(1) AS DECIMAL(10,2)) AS avgb,SUM(c) / CAST(COUNT(1) AS DECIMAL(10,2)) AS avgc,SUM(CASE WHEN d <= 4 THEN 1 ELSE 0 END) AS small4,SUM(CASE WHEN d > 4 THEN 1 ELSE 0 END) AS big4FROM aGROUP BY name),m AS(SELECT name,avga,avgb,avgc,RTRIM(small4 * 100 / (small4 + big4)) + '%' as small4,RTRIM(big4 * 100 / (small4 + big4)) + '%' as big4FROM t)SELECT * FROM mUNIONSELECT '总计',avg(t.avga),avg(T.avgb),avg(T.avgc),RTRIM(SUM(t.small4) * 100 / (SUM(t.small4) + SUM(t.big4))) + '%' as small4,RTRIM(SUM(t.big4) * 100 / (SUM(t.small4) + SUM(t.big4))) + '%' as big4FROM tname avga avgb avgc small4 big4001 2.66666666666 3.66666666666 4.66666666666 66% 33%002 2.00000000000 2.00000000000 2.50000000000 50% 50%003 2.00000000000 2.00000000000 2.00000000000 100% 0%总计 2.22222222222 2.55555555555 3.05555555555 66% 33%