，求好人

求救，求好人！
怎么样才查出哪些行参与了平均数计算？

平均数的计算方法是：(先算的是a的并且在范围内的平均值，b是比例，然后是统计)

SQL code

SELECT  [name], AVG(a) as a, SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS bFROM  taGROUP BY namehaving AVG(a)>'1'UNION ALL SELECT  '统计', AVG(a) as a,AVG(b) as bFROM(SELECT  [name], AVG(a) as a, SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS bFROM  taGROUP BY namehaving AVG(a)>'1')ta

表里的数据
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


我不是要求平均值啊！！！

我平均值的计算现在是这个写法

我现在想知道的是：：：：参与我这个平均值计算的[color=#0000FF]行有哪些，然后把这下行查出来[/color]！

[解决办法]

SQL code

select * from tbwhere name in(select name from(SELECT  [name], AVG(a) as a, SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS bFROM  taGROUP BY namehaving AVG(a)>'1'UNION ALL SELECT  '统计', AVG(a) as a,AVG(b) as bFROM(SELECT  [name], AVG(a) as a, SUM(CASE WHEN b<=5 THEN 1 ELSE 0 END) * 100.0 / COUNT(b) AS bFROM  taGROUP BY namehaving AVG(a)>'1')ta)t)
[解决办法]
把AVG去掉就行了
[解决办法]
去掉所有avg就可以了 

 

查看更多 下一篇