多表数据调用统计，大哥们帮帮我。谢谢各位了。
这个是调用一个表的发贴排行，怎么才能改成六个表的呀？
select top 10 username,count(username) from Dv_bbs1 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
表分别是Dv_bbs1、Dv_bbs2、Dv_bbs3、Dv_bb4、Dv_bbs5、Dv_bbs6
[解决办法]
Dv_bbs1、Dv_bbs2、Dv_bbs3、Dv_bb4、Dv_bbs5、Dv_bbs6
表结构一样吗？
select top 10 username,count(username) from
(
select username from Dv_bbs1 WHERE datediff(day,DateAndTime,Getdate())=0
union
select username from Dv_bbs2 WHERE datediff(day,DateAndTime,Getdate())=0
union
select username from Dv_bbs3 WHERE datediff(day,DateAndTime,Getdate())=0
union
select username from Dv_bbs4 WHERE datediff(day,DateAndTime,Getdate())=0
union
select username from Dv_bbs5 WHERE datediff(day,DateAndTime,Getdate())=0
union
select username from Dv_bbs6 WHERE datediff(day,DateAndTime,Getdate())=0
)
group by username order by count(username) desc
[解决办法]
select * from
(
select top 10 username,count(username) as 发帖数,1 as 论坛ID from Dv_bbs1 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
)
union all
(
select top 10 username,count(username),2 from Dv_bbs2 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
)
union all
(
select top 10 username,count(username),3 from Dv_bbs3 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
)
union all
(
select top 10 username,count(username),4 from Dv_bbs4 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
)
union all
(
select top 10 username,count(username),5 from Dv_bbs5 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
)
union all
(
select top 10 username,count(username),6 from Dv_bbs6 WHERE datediff(day,DateAndTime,Getdate())=0 group by username order by count(username) desc
) order by 论坛ID
[解决办法]
楼上正解
[解决办法]
更正——
select top 10 username,count(username) from
(
select username from Dv_bbs1 WHERE datediff(day,DateAndTime,Getdate())=0
union all
select username from Dv_bbs2 WHERE datediff(day,DateAndTime,Getdate())=0
union all
select username from Dv_bbs3 WHERE datediff(day,DateAndTime,Getdate())=0
union all
select username from Dv_bbs4 WHERE datediff(day,DateAndTime,Getdate())=0
union all
select username from Dv_bbs5 WHERE datediff(day,DateAndTime,Getdate())=0
union all
select username from Dv_bbs6 WHERE datediff(day,DateAndTime,Getdate())=0
)
group by username order by count(username) desc
