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
请问怎样才能统计出正确的均值？
最佳答案
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
因为B表的需要grou by的 是动态的 非常感谢
