22,295
社区成员
发帖
与我相关
我的任务
分享求一个复杂的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%
字段 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%
...全文
请发表友善的回复…
发表回复
catcatcat12345 2012-06-29
- 打赏
- 举报
谢谢大家，csdn好有爱啊！
筱筱澄 2012-06-29
- 打赏
- 举报
我的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 行受影响)
*/
孤独加百列 2012-06-29
- 打赏
- 举报
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
GO
WITH 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 t
name 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%
筱筱澄 2012-06-29
- 打赏
- 举报
--> 测试数据：[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 行受影响)
*/
筱筱澄 2012-06-29
- 打赏
- 举报
--> 测试数据：[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 行受影响)
*/
昵称被占用了 2012-06-29
- 打赏
- 举报
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
