一对多查询问题，大家帮个忙吧
稍微复杂一点的一对多查询，试了半天也没达成目标
希望各路大神帮忙解答
表数据如下
------------------------------------------------
ID USER ACCESS_PAGE ACCESS_DATE_TIME
1 A PAGE-A 2013/10/01
2 A PAGE-A 2013/10/02
3 A PAGE-A 2013/10/03
4 A PAGE-B 2013/10/01
5 A PAGE-B 2013/10/02
6 B PAGE-A 2013/10/03
7 C PAGE-B 2013/10/05
8 D PAGE-A 2013/10/01
9 D PAGE-A 2013/10/09
10 D PAGE-B 2013/10/11
-------------------------------------------------
上表是对用户访问页面的一个记录表
想求出PAGE-A和PAGE-B都访问了的用户的最新访问时间
并总结为一行数据
要求最后输出的结果如下
----------------------------------------
USER ACCESS_PAGE_A_TIME ACCESS_PAGE_B_TIME
A 2013/10/03 2013/10/02
D 2013/10/09 2013/10/11
-----------------------------------------
麻烦各位了，拜谢
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-17 16:16:52
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[USER] varchar(1),[ACCESS_PAGE] varchar(6),[ACCESS_DATE_TIME] datetime)
insert [huang]
select 1,'A','PAGE-A','2013/10/01' union all
select 2,'A','PAGE-A','2013/10/02' union all
select 3,'A','PAGE-A','2013/10/03' union all
select 4,'A','PAGE-B','2013/10/01' union all
select 5,'A','PAGE-B','2013/10/02' union all
select 6,'B','PAGE-A','2013/10/03' union all
select 7,'C','PAGE-B','2013/10/05' union all
select 8,'D','PAGE-A','2013/10/01' union all
select 9,'D','PAGE-A','2013/10/09' union all
select 10,'D','PAGE-B','2013/10/11'
--------------开始查询--------------------------
select [user],MAX(CASE WHEN [ACCESS_PAGE]='PAGE-A' THEN [ACCESS_DATE_TIME] ELSE NULL END ) [ACCESS_PAGE_A_TIME]
,MAX(CASE WHEN [ACCESS_PAGE]='PAGE-B' THEN [ACCESS_DATE_TIME] ELSE NULL END ) [ACCESS_PAGE_B_TIME]
from [huang] a
WHERE EXISTS (SELECT 1 FROM huang b WHERE a.[USER]=b.[USER] AND b.access_page='PAGE-A')
AND EXISTS (SELECT 1 FROM huang c WHERE a.[USER]=c.[USER] AND c.access_page='PAGE-B')
GROUP BY [user]
----------------结果----------------------------
/*
user ACCESS_PAGE_A_TIME ACCESS_PAGE_B_TIME
---- ----------------------- -----------------------
A 2013-10-03 00:00:00.000 2013-10-02 00:00:00.000
D 2013-10-09 00:00:00.000 2013-10-11 00:00:00.000
*/
;with cte(ID,[USER],ACCESS_PAGE,ACCESS_DATE_TIME ) as
(
select 1,'A','PAGE-A','2013/10/01'
union all select 2,'A','PAGE-A','2013/10/02'
union all select 3,'A','PAGE-A','2013/10/03'
union all select 4,'A','PAGE-B','2013/10/01'
union all select 5,'A','PAGE-B','2013/10/02'
union all select 6,'B','PAGE-A','2013/10/03'
union all select 7,'C','PAGE-B','2013/10/05'
union all select 8,'D','PAGE-A','2013/10/01'
union all select 9,'D','PAGE-A','2013/10/09'
union all select 10,'D','PAGE-B','2013/10/11'
)
select *
from
(
select [USER],MAX(case when ACCESS_PAGE='PAGE-A' then ACCESS_DATE_TIME else null end) as ACCESS_PAGE_A_TIME
,MAX(case when ACCESS_PAGE='PAGE-B' then ACCESS_DATE_TIME else null end) as ACCESS_PAGE_B_TIME
from cte
group by [USER]
)t
where ACCESS_PAGE_A_TIME is not null and ACCESS_PAGE_B_TIME is not null
/*
USERACCESS_PAGE_A_TIMEACCESS_PAGE_B_TIME
A2013/10/032013/10/02
D2013/10/092013/10/11
*/
