--建立环境
create table table1
(
ID int,
CompanyName varchar(10),
RegDate datetime,
AuditingDate datetime,
LoginDate datetime
)insert into table1 select 1, '企业A', '2005-3-1', '2005-3-2', '2005-3-1'
insert into table1 select 2, '企业B', '2005-3-2', '2005-3-2', '2005-3-3'
insert into table1 select 3, '企业C', '2005-3-8', '2005-3-8', '2005-3-8'
insert into table1 select 4, '企业D', '2005-3-8', '2005-3-8', '2005-3-8'
insert into table1 select 5, '企业E', '2005-3-8', '2005-3-8', '2005-3-9'
insert into table1 select 6, '企业F', '2005-3-8' , '2005-3-10', '2005-3-10'
insert into table1 select 7, '企业G', '2005-3-11', '2005-3-12', '2005-3-15'
insert into table1 select 8, '企业H', '2005-3-12', '2005-3-12', '2005-3-12'--语句
select convert(varchar(10),isnull(isnull(RegDate,AuditingDate),LoginDate),120) as 日期,
isnull(注册数,0) as 注册数,isnull(审核数,0) as 审核数,isnull(首次登陆数,0) as 首次登陆数
from
(select RegDate,count(1) as 注册数
from table1
group by RegDate
)t1 full join
(select AuditingDate,count(1) as 审核数
from table1
group by AuditingDate
)t2 on t1.RegDate = t2.AuditingDate
full join
(select LoginDate,count(1) as 首次登陆数
from table1
group by LoginDate
)t3 on t1.RegDate = t3.LoginDate
order by 日期--结果
2005-03-01 1 0 1
2005-03-02 1 2 0
2005-03-03 0 0 1
2005-03-08 4 3 2
2005-03-09 0 0 1
2005-03-10 0 0 1
2005-03-10 0 1 0
2005-03-11 1 0 0
2005-03-12 1 2 1
2005-03-15 0 0 1
create table table1
(
ID int,
CompanyName varchar(10),
RegDate datetime,
AuditingDate datetime,
LoginDate datetime
)insert into table1 select 1, '企业A', '2005-3-1', '2005-3-2', '2005-3-1'
insert into table1 select 2, '企业B', '2005-3-2', '2005-3-2', '2005-3-3'
insert into table1 select 3, '企业C', '2005-3-8', '2005-3-8', '2005-3-8'
insert into table1 select 4, '企业D', '2005-3-8', '2005-3-8', '2005-3-8'
insert into table1 select 5, '企业E', '2005-3-8', '2005-3-8', '2005-3-9'
insert into table1 select 6, '企业F', '2005-3-8' , '2005-3-10', '2005-3-10'
insert into table1 select 7, '企业G', '2005-3-11', '2005-3-12', '2005-3-15'
insert into table1 select 8, '企业H', '2005-3-12', '2005-3-12', '2005-3-12'--语句
select convert(varchar(10),isnull(isnull(RegDate,AuditingDate),LoginDate),120) as 日期,
isnull(注册数,0) as 注册数,isnull(审核数,0) as 审核数,isnull(首次登陆数,0) as 首次登陆数
from
(select RegDate,count(1) as 注册数
from table1
group by RegDate
)t1 full join
(select AuditingDate,count(1) as 审核数
from table1
group by AuditingDate
)t2 on t1.RegDate = t2.AuditingDate
full join
(select LoginDate,count(1) as 首次登陆数
from table1
group by LoginDate
)t3 on t1.RegDate = t3.LoginDate
order by 日期--结果
2005-03-01 1 0 1
2005-03-02 1 2 0
2005-03-03 0 0 1
2005-03-08 4 3 2
2005-03-09 0 0 1
2005-03-10 0 0 1
2005-03-10 0 1 0
2005-03-11 1 0 0
2005-03-12 1 2 1
2005-03-15 0 0 1
isnull(注册数,0) as 注册数,isnull(审核数,0) as 审核数,isnull(首次登陆数,0) as 首次登陆数
from
(select RegDate,count(1) as 注册数
from table1
group by RegDate
)t1 full join
(select AuditingDate,count(1) as 审核数
from table1
group by AuditingDate
)t2 on t1.RegDate = t2.AuditingDate
full join
(select LoginDate,count(1) as 首次登陆数
from table1
group by LoginDate
)t3 on t1.RegDate = t3.LoginDate or t2.AuditingDate = t3.LoginDate
order by 日期--结果
2005-03-01 1 0 1
2005-03-02 1 2 0
2005-03-03 0 0 1
2005-03-08 4 3 2
2005-03-09 0 0 1
2005-03-10 0 1 1
2005-03-11 1 0 0
2005-03-12 1 2 1
2005-03-15 0 0 1
from ( select distinct RegDate date from tab_a
union select distinct AuditDate from tab_a
union select distinct LoginDate from tab_a ) DS left outer join
(select D.Date, count(A.Id) RegNum
from tab_a A,
( select distinct RegDate date from tab_a
union select distinct AuditDate from tab_a
union select distinct LoginDate from tab_a ) D
where A.RegDate = D.Date
group by D.Date) X on DS.Date = X.Date
left outer join
(select D.Date, count(A.Id) AuditNum
from tab_a A,
( select distinct RegDate date from tab_a
union select distinct AuditDate from tab_a
union select distinct LoginDate from tab_a ) D
where A.AuditDate = D.Date
group by D.Date) Y on DS.Date = Y.Date
left outer join
(select D.Date, count(A.Id) LoginNum
from tab_a A,
( select distinct RegDate date from tab_a
union select distinct AuditDate from tab_a
union select distinct LoginDate from tab_a ) D
where A.LoginDate = D.Date
group by D.Date) Z on DS.Date = Z.Date
select 日期,
sum(注册数) as 注册数,sum(审核数) as 审核数,sum(首次登陆数) as 首次登陆数
from
(select RegDate as 日期,count(1) as 注册数,0 as 审核数,0 as 首次登陆数
from table1
group by RegDate
union all
select AuditingDate,0,count(1) as 审核数,0
from table1
group by AuditingDate
union all
select LoginDate,0,0,count(1) as 首次登陆数
from table1
group by LoginDate
) as t
group by 日期
(
ID int,
CompanyName varchar(10),
RegDate datetime,
AuditingDate datetime,
LoginDate datetime
)insert into table1 select 1, '企业A', '2005-3-1', '2005-3-2', '2005-3-1'
insert into table1 select 2, '企业B', '2005-3-2', '2005-3-2', '2005-3-3'
insert into table1 select 3, '企业C', '2005-3-8', '2005-3-8', '2005-3-8'
insert into table1 select 4, '企业D', '2005-3-8', '2005-3-8', '2005-3-8'
insert into table1 select 5, '企业E', '2005-3-8', '2005-3-8', '2005-3-9'
insert into table1 select 6, '企业F', '2005-3-8' , '2005-3-10', '2005-3-10'
insert into table1 select 7, '企业G', '2005-3-11', '2005-3-12', '2005-3-15'
insert into table1 select 8, '企业H', '2005-3-12', '2005-3-12', '2005-3-12'select allTime 日期,
(select count(*) from table1 where regdate=a.allTIme) 注册数,
(select count(*) from table1 where AuditingDate=a.allTIme) 审核数,
(select count(*) from table1 where LoginDate=a.allTIme) 首次登陆数
from
(select regdate allTime from table1
union select auditingdate from table1
union select logindate from table1) a
Haiwer(海阔天空) 的也比較好,不過執行速度上可能會慢一點,畢竟多次Union 之後還要重新 Group
( 企业编号 Int,
企业名 Nvarchar(10),
注册时间 Varchar(10),
审核时间 Varchar(10),
首次登陆时间 Varchar(10))
Insert TEST Select 1, N'企业A', '2005-3-1', '2005-3-2', '2005-3-1'
Union All Select 2, N'企业B', '2005-3-2', '2005-3-2', '2005-3-3'
Union All Select 3, N'企业C', '2005-3-8', '2005-3-8', '2005-3-8'
Union All Select 4, N'企业D', '2005-3-8', '2005-3-8', '2005-3-8'
Union All Select 5, N'企业E', '2005-3-8', '2005-3-8', '2005-3-9'
Union All Select 6, N'企业F', '2005-3-8', '2005-3-10', '2005-3-10'
Union All Select 7, N'企业G', '2005-3-11', '2005-3-12', '2005-3-15'
Union All Select 8, N'企业H', '2005-3-12', '2005-3-12', '2005-3-12'
GO
Select A.日期,
SUM(B.注册数) As 注册数,
SUM(B.审核数) As 审核数,
SUM(B.首次登陆数) As 首次登陆数
From (Select 注册时间 As 日期 From TEST
Union
Select 审核时间 From TEST
Union
Select 首次登陆时间 From TEST) A
Left Join
(Select 注册时间 As 时间,Count(企业编号) As 注册数,0 As 审核数 ,0 As 首次登陆数 From TEST Group By 注册时间
Union
Select 审核时间,0,Count(企业编号) As 审核数,0 From TEST Group By 审核时间
Union
Select 首次登陆时间,0,0,Count(企业编号) As 首次登陆数 From TEST Group By 首次登陆时间) B
On A.日期=B.时间
Group By A.日期
Order By Cast(A.日期 As DateTime)
GO
Drop Table TEST
--Result
/*
日期 注册数 审核数 首次登陆数
2005-3-1 1 0 1
2005-3-2 1 2 0
2005-3-3 0 0 1
2005-3-8 4 3 2
2005-3-9 0 0 1
2005-3-10 0 1 1
2005-3-11 1 0 0
2005-3-12 1 2 1
2005-3-15 0 0 1
*/
SUM(注册数) As 注册数,
SUM(审核数) As 审核数,
SUM(首次登陆数) As 首次登陆数
From
(Select 注册时间 As 日期,Count(企业编号) As 注册数,0 As 审核数 ,0 As 首次登陆数 From TEST Group By 注册时间
Union
Select 审核时间,0,Count(企业编号) As 审核数,0 From TEST Group By 审核时间
Union
Select 首次登陆时间,0,0,Count(企业编号) As 首次登陆数 From TEST Group By 首次登陆时间) A
Group By 日期
Order By Cast(日期 As DateTime)實際和Haiwer(海阔天空) 是一樣的
oop80(RedSky) 才是正解,沒有 join 隻是復合查詢,速度會比較快,
Haiwer(海阔天空) 的也比較好,不過執行速度上可能會慢一點,畢竟多次Union 之後還要重新 Group
--------------------------------------------------------------------
但是oop80(RedSky)用了三個子查詢,效率不一定高。
sum(注册数) as 注册数,sum(审核数) as 审核数,sum(首次登陆数) as 首次登陆数
from
(
select RegDate as 日期,1 as 注册数,0 as 审核数,0 as 首次登陆数
from table1
union all
select AuditingDate,0,1 as 审核数,0
from table1
union all
select LoginDate,0,0,1 as 首次登陆数
from table1
) as t
group by 日期
--一次group 用 union all 不用 union
多路子查詢要看查詢什麼表,在資源沖突的大數據表情況下可能會慢一些,
但是Union 基本上就不能用於大數據表的查詢,除非硬件超級爽,Union 是耗內存的祖宗。
表示一下崇拜! :)