今天去腾讯实习生面试，面试官给了一个题目。
现在有30张表，每张表记录了当天的登录用户信息（一个用户由于时间不同可以有多项），每张表大约有8亿项。
现在要求使用SQL操作查找出这个月登陆次数最多的前N个用户。
大家多多提供思路啊。
后面还会继续加分。
------解决方案--------------------------------------------------------
select * from tablename where DATEDIFF(day,RecordTime,getdate())<=30 order by(numline)
------解决方案--------------------------------------------------------
登陆次数前N的用户至少在一个表排前N
每张表前TOP N找出 N*30条排序
------解决方案--------------------------------------------------------
学习学习
------解决方案--------------------------------------------------------
select username,sum(cnt) as cnt from
(
select username,count(*) as cnt from tbl_name1
union
select username,count(*) as cnt from tbl_name2
......
union
select username,count(*) as cnt from tbl_name30
)
order by cnt desc
这是全部的倒序，想取前N个的话自己再加个限制语句
------解决方案--------------------------------------------------------
应该是union all
select username,sum(cnt) as cnt from
(
select username,count(*) as cnt from tbl_name1 group by username
union all
select username,count(*) as cnt from tbl_name2 group by username
......
union all
select username,count(*) as cnt from tbl_name30 group by username
)
group by username
order by cnt desc
或者干脆就
select username,count(*) as cnt from
(
select username from tbl_name1
union all
select username from tbl_name2
......
union all
select username from tbl_name30
)
group by username
order by cnt desc