求一个复杂的sql语句怎么写？

Question

有数据表 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%
 

Answer 1

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 A
GROUP BY NAME
UNION ALL
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 A

Answer 2

--> 测试数据：[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 ALL
SELECT '001',5,6,7,4 UNION ALL
SELECT '001',2,3,4,8 UNION ALL
SELECT '002',1,2,3,4 UNION ALL
SELECT '002',3,2,2,5 UNION ALL
SELECT '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.666666666666
002  2.000000                                2.000000                                2.500000                                50.000000000000
003  2.000000                                2.000000                                2.000000                                100.000000000000
NULL 2.333333                                2.833333                                3.500000                                66.666666666666(4 行受影响)
*/

Answer 3

--> 测试数据：[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 ALL
SELECT '001',5,6,7,4 UNION ALL
SELECT '001',2,3,4,8 UNION ALL
SELECT '002',1,2,3,4 UNION ALL
SELECT '002',3,2,2,5 UNION ALL
SELECT '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.666666666666
002  2.000000                                2.000000                                2.500000                                50.000000000000
003  2.000000                                2.000000                                2.000000                                100.000000000000
统计   2.333333                                2.833333                                3.500000                                66.666666666666(4 行受影响)
*/

Answer 4

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a')
BEGIN
DROP TABLE A
END
GO
CREATE TABLE a
(
name VARCHAR(10),
a INT,
b INT,
c INT,
d INT
)
GO
INSERT INTO a
SELECT  '001', 1, 2, 3, 4 UNION   
SELECT  '001', 5, 6, 7, 4 UNION
SELECT  '001', 2, 3, 4, 8 UNION
SELECT '002', 1, 2, 3, 4 UNION
SELECT  '002', 3, 2, 2, 5 UNION
SELECT  '003', 2, 2, 2, 4
GOWITH 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 big4
FROM a
GROUP BY name),
m AS
(
SELECT name,avga,avgb,avgc,RTRIM(small4 * 100  / (small4 + big4)) + '%' as small4,RTRIM(big4 * 100  / (small4 + big4)) + '%' as big4
FROM t
)SELECT * FROM m
UNION
SELECT '总计',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 big4
FROM tname avga avgb avgc small4 big4
001 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%

Answer 5

我的3楼不见了
--> 测试数据：[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 ALL
SELECT '001',5,6,7,4 UNION ALL
SELECT '001',2,3,4,8 UNION ALL
SELECT '002',1,2,3,4 UNION ALL
SELECT '002',3,2,2,5 UNION ALL
SELECT '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.666666666666
002  2.000000                                2.000000                                2.500000                                50.000000000000
003  2.000000                                2.000000                                2.000000                                100.000000000000
统计   2.333333                                2.833333                                3.500000                                66.666666666666(4 行受影响)
*/