首页 新闻 赞助 找找看

sql 分组去除重复聚合

0
悬赏园豆:10 [已解决问题] 解决于 2014-07-01 18:16

有表A 

select * from A 

ID Number
1 2
2 3
3 4
4 2

ID Date
1 2012-03-01 00:00:00.000
1 2012-04-01 00:00:00.000
1 2012-05-01 00:00:00.000
1 2013-05-01 00:00:00.000
2 2013-04-01 00:00:00.000
2 2013-12-01 00:00:00.000
3 2014-01-01 00:00:00.000
3 2014-03-01 00:00:00.000
4 2012-04-01 00:00:00.000
4 2012-06-01 00:00:00.000

select * from A left join B on A.ID=B.ID

ID Number ID Date
1 2 1 2012-03-01 00:00:00.000
1 2 1 2012-04-01 00:00:00.000
1 2 1 2012-05-01 00:00:00.000
1 2 1 2013-05-01 00:00:00.000
2 3 2 2013-04-01 00:00:00.000
2 3 2 2013-12-01 00:00:00.000
3 4 3 2014-01-01 00:00:00.000
3 4 3 2014-03-01 00:00:00.000
4 2 4 2012-04-01 00:00:00.000
4 2 4 2012-06-01 00:00:00.000

select Year(B.Date),count(A.ID),avg(A.Number*1.0) from A left join B on A.ID=B.ID
GROUP BY Year(B.Date)

(无列名) (无列名) (无列名)
2012       5       2.000000
2013       3       2.666666
2014       2       4.000000

这样统计的count 都是不正确的  

select Year(B.Date),count(distinct A.ID),avg(A.Number*1.0) from A left join B on A.ID=B.ID
GROUP BY Year(B.Date)

(无列名) (无列名) (无列名)
2012       2        2.000000
2013       2        2.666666
2014       1        4.000000

count 统计的结果是正确的,AVG却不能distinct  因为 ID 1和4  具有相同的number

请问怎样才能统计出正确的均值?

猎骑兵的主页 猎骑兵 | 菜鸟二级 | 园豆:211
提问于:2014-06-23 10:22
< >
分享
最佳答案
0

select date,ID,avg(Number*1.0) from

(

select distinct (a.id),number,Year(b.date) date from A left join B on A.ID=B.ID

)
GROUP BY ID,date

大概是这样了,采用子查询

收获园豆:10
公寓城影子传说 | 初学一级 |园豆:134 | 2014-06-23 10:50

因为B表的需要grou by的 是动态的     非常感谢

猎骑兵 | 园豆:211 (菜鸟二级) | 2014-06-23 10:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册