现有3张表table1, table2,table3,每个表中分别有保存user_id的字段fld01, fld01, fld03, 并且每张表中都有created字段,现在要统计在某一时间段内这三张表中保存的user的个数(其中同一个用户只能统计一次,不能累加),如:
在2008-12-24到2008-12-25这两天:
table1保存的user id有:1,2,3
table2保存的user id有: 2,4,5
talbe3保存的user id有:7
则统计出的不重复user id有:1,2,3,4,5,7共6个,所以返回结果应该是6请问各位老大这个SQL怎么写啊?
在2008-12-24到2008-12-25这两天:
table1保存的user id有:1,2,3
table2保存的user id有: 2,4,5
talbe3保存的user id有:7
则统计出的不重复user id有:1,2,3,4,5,7共6个,所以返回结果应该是6请问各位老大这个SQL怎么写啊?
(
select fld01 from table1 where created...
union
...
union
...
)
((select fld01 from table1)
union (select fld02 from table1)
union (select fld03 from table1)
union (select fld01 from table2)
union (select fld02 from table2)
union (select fld03 from table2)
union (select fld01 from table3)
union (select fld02 from table3)
union (select fld03 from table3))
((select fld01,created from table1)
union (select,created fld02 from table1)
union (select,created fld03 from table1)
union (select,created fld01 from table2)
union (select,created fld02 from table2)
union (select,created fld03 from table2)
union (select,created fld01 from table3)
union (select,created fld02 from table3)
union (select,created fld03 from table3))
where created between 开始时间 and 终止时间
select fld01 user_id from table1 where created between '2008-12-24' and '2008-12-25'
union
select fld02 user_id from table2 where created between '2008-12-24' and '2008-12-25'
union
select fld03 user_id from table3 where created between '2008-12-24' and '2008-12-25'
)
select count(distinct user_id) from
select fld01 user_id from table1 where created between '2008-12-24' and '2008-12-25'
union
select fld02 user_id from table2 where created between '2008-12-24' and '2008-12-25'
union
select fld03 user_id from table3 where created between '2008-12-24' and '2008-12-25'
)
select fld01 user_id from table1 where created between '2008-12-24' and '2008-12-25'
union
select fld02 user_id from table2 where created between '2008-12-24' and '2008-12-25'
union
select fld03 user_id from table3 where created between '2008-12-24' and '2008-12-25'
) 正解
union就是可以过滤重复id
当然也可以用distinct
来去掉重复字段
union all需要用distinct
select count(user_id) from(
select fld01 user_id from table1 where created between '2008-12-24' and '2008-12-25'
union
select fld02 user_id from table2 where created between '2008-12-24' and '2008-12-25'
union
select fld03 user_id from table3 where created between '2008-12-24' and '2008-12-25'
)
就可以了!
select fld01 user_id from table1 where created between '2008-12-24' and '2008-12-25'
union
select fld02 user_id from table2 where created between '2008-12-24' and '2008-12-25'
union
select fld03 user_id from table3 where created between '2008-12-24' and '2008-12-25'
) 这个应该是正确的
select count(distinct user_id)
from table1,table2,table3
where created between 'time1' and 'time2' 加union的话效率高一点