怎么样才查出哪些行参与了平均数计算?平均数的计算方法是:(先算的是a的并且在范围内的平均值,b是比例,然后是统计)SELECT [name],
AVG(a) as a,
SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS b
FROM ta
GROUP BY name
having
AVG(a)>'1'UNION ALL
SELECT '统计',
AVG(a) as a,
AVG(b) as b
FROM(
SELECT [name],
AVG(a) as a,
SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS b
FROM ta
GROUP BY name
having
AVG(a)>'1'
)ta
数据库是:数据随意name a b
AVG(a) as a,
SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS b
FROM ta
GROUP BY name
having
AVG(a)>'1'UNION ALL
SELECT '统计',
AVG(a) as a,
AVG(b) as b
FROM(
SELECT [name],
AVG(a) as a,
SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS b
FROM ta
GROUP BY name
having
AVG(a)>'1'
)ta
数据库是:数据随意name a b
name a b
001 1 2
001 5 6
001 2 3
002 1 2
002 3 2
003 2 2
平均和比例的结果
name a b
001 2.66666666666667 66.666666666666
002 2 100.000000000000
003 2 100.000000000000
统计 2.22222222222222 88.888888888888
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[name] varchar(3),
[a] int,
[b] int
)
insert [test]
select '001',1,2 union all
select '001',5,6 union all
select '001',2,3 union all
select '002',1,2 union all
select '002',3,2 union all
select '003',2,2
select
[name],
AVG(a*1.0) as a,
SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS b
from
test
group by
name
union all
select '合计',AVG(a),SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b)
from test
/*
name a b
-----------------------------------------
001 2.666666 66.666666666666
002 2.000000 100.000000000000
003 2.000000 100.000000000000
合计 2.000000 83.333333333333
*/--统计的最后字段b结果怎么不一样
where name in
(
SELECT [name]
FROM ta
GROUP BY name
having
AVG(a)>1
)这样吗?