数据库面试题

[shexinwei]

今天去腾讯实习生面试，面试官给了一个题目。

现在有30张表，每张表记录了当天的登录用户信息（一个用户由于时间不同可以有多项），每张表大约有8亿项。

现在要求使用SQL操作查找出这个月登陆次数最多的前N个用户。

大家多多提供思路啊。

后面还会继续加分。

[tanguolovepig]

select * from tablename where DATEDIFF(day,RecordTime,getdate())<=30 order by(numline)

[csdn网速很慢]

登陆次数前N的用户至少在一个表排前N
每张表前TOP N找出 N*30条排序

[DanDing2015]

学习学习

[aleaf]

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个的话自己再加个限制语句

[aleaf]

应该是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