日期:2014-05-18  浏览次数:20503 次

求解啊?
SELECT name, 
AVG(a) as a, 
AVG(b) as b, 
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6


UNION ALL
SELECT '统计', 
AVG(a) as a, 
AVG(b) as b, 
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM 
(SELECT [name], 
AVG(a) as a, 
AVG(b) as b, 
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6)
ta


消息 207,级别 16,状态 1,第 17 行
列名 'd' 无效。
消息 207,级别 16,状态 1,第 17 行
列名 'd' 无效。
消息 207,级别 16,状态 1,第 18 行
列名 'd' 无效。
消息 207,级别 16,状态 1,第 18 行
列名 'd' 无效。


怎么办?为什么?

------解决方案--------------------
SQL code
SELECT name,  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d1<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d2>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6
UNION ALL
SELECT '统计',  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d1<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d2>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM  
(SELECT [name],  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d1<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d2>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6)
ta

------解决方案--------------------
SQL code
SELECT name,  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(1) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(1) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6


UNION ALL
SELECT '统计',  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(1) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(1) AS d2
FROM  
(SELECT [name],  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(1) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(1) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6)t

------解决方案--------------------
姐姐搞错了,修改的不是你的原版的,不过也是一样的

SUM(d1) AS d1,
SUM(d2) AS d2 就可以了

SQL code
SELECT name,  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
FROM [ta]
GROUP BY [name]
having AVG(a)<=6


UNION ALL
SELECT '统计',  
AVG(a) as a,  
AVG(b) as b,  
AVG(c) as c,
SUM(d1) AS d1,
SUM(d2) AS d2
FROM  
(
    SELECT [name],  
    AVG(a) as a,  
    AVG(b) as b,  
    AVG(c) as c,
    SUM(CASE WHEN d<5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d1,
    SUM(CASE WHEN d>=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(d) AS d2
    FROM [ta]
    GROUP BY [name]
    having AVG(a)<=6
) ta