select A.aduser,count(*) as total from
(select * from table1 union all
select * from table2 union all
select * from table3 union all
select * from table4 union all
select * from table5 ) A group by A.aduser
(select * from table1 union all
select * from table2 union all
select * from table3 union all
select * from table4 union all
select * from table5 ) A group by A.aduser
From
(select aduser from 表1
union all select aduser from 表2
union all select aduser from 表3
union all select aduser from 表4
union all select aduser from 表5) T
group by aduser
(select * from table1 union all
select * from table2 union all
select * from table3 union all
select * from table4 union all
select * from table5 ) A group by A.aduser order by count(*)
Select aduser,Count(*) As Total from
(
Select * from 表1
Union All
Select * from 表2
Union All
Select * from 表3
Union All
Select * from 表4
Union All
Select * from 表5
) A
Group By aduser
Order By Total Desc,aduser
where t1.aduser = t2.aduser
group by t1.aduser
if not exists(select * from sysobjects where name='表1')
begin
create table 表1
(
ID int identity(1,1),
adduser varchar(20) default '',
addtime datetime default (getdate())
)
end
else
begin
truncate table 表1
endif not exists(select * from sysobjects where name='表2')
begin
create table 表2
(
ID int identity(1,1),
adduser varchar(20) default '',
addtime datetime default (getdate())
)
end
else
begin
truncate table 表2
endif not exists(select * from sysobjects where name='表3')
begin
create table 表3
(
ID int identity(1,1),
adduser varchar(20) default '',
addtime datetime default (getdate())
)
end
else
begin
truncate table 表3
end
--添加测试数据
insert into 表1(adduser)
select 'aaa' union select 'bbb' union select 'ccc' union select 'aaa'insert into 表2(adduser)
select 'aaa' union select 'bbb' union select 'ccc' union select 'aaa'insert into 表3(adduser)
select 'aaa' union select 'bbb' union select 'ccc' union select 'aaa'
--创建临时表
create table #temp
(
adduser varchar(20),
addtime datetime,
)
insert into #temp
select adduser,addtime from 表1
union all
select adduser,addtime from 表2
union all
select adduser,addtime from 表3select adduser,count(*)as times from #temp where datediff(day,getdate(),addtime)=0 group by adduser--清除临时表
drop table #temp