当前位置: 代码迷 >> Sql Server >> 多表数据调用统计,大哥们帮帮小弟我。多谢各位了
  详细解决方案

多表数据调用统计,大哥们帮帮小弟我。多谢各位了

热度:91   发布时间:2016-04-27 16:27:44.0
多表数据调用统计,大哥们帮帮我。谢谢各位了。
这个是调用一个表的发贴排行,怎么才能改成六个表的呀?

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

  相关解决方案