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' 无效。
怎么办?为什么?
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 server 权限不够,连接不到数据库,各位大虾救命呀
- 函数返回表值,和返回多语句表值怎样理解?
- sql2000 查询语句急!!!!!!!
- 关于日期格式
- 组合SQL语句与参数的问题
- 为什么建立字段时,会出现默认的约束,怎么才能在删除字段的同时,先删除约束。
- 请看一下这个错误,是什么原因?
- 用数据库的备份文件还原数据库后,再使用还原后的数据库日志就用不了了,我用的工具是:Log Explorer for SQL Server
- 关于binary,varBinary数据类型的问题?
- 请教高手,关于在Linux RedHat 下安装ODBC.
- 请帮我看看如何建这么一种表
- ALTER TABLE 公式问题
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
消息 207,级别 16,状态 1,第 5 行
列名 'd1' 无效。
消息 207,级别 16,状态 1,第 6 行
列名 'd2' 无效。
消息 207,级别 16,状态 1,第 23 行
列名 'd1' 无效。
消息 207,级别 16,状态 1,第 24 行
列名 'd2' 无效。
消息 207,级别 16,状态 1,第 16 行
列名 'd' 无效。
消息 207,级别 16,状态 1,第 17 行
列名 'd' 无效。
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
消息 207,级别 16,状态 1,第 17 行
列名 'd' 无效。
消息 207,级别 16,状态 1,第 18 行
列名 'd' 无效。而且count里面怎么是1了
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(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(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(d2) AS d2 就可以了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