27,579
社区成员
发帖
与我相关
我的任务
分享一对多查询问题，大家帮个忙吧
稍微复杂一点的一对多查询，试了半天也没达成目标
希望各路大神帮忙解答
表数据如下
------------------------------------------------
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
-----------------------------------------
麻烦各位了，拜谢
希望各路大神帮忙解答
表数据如下
------------------------------------------------
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
-----------------------------------------
麻烦各位了，拜谢
...全文
请发表友善的回复…
发表回复
LongRui888 2013-10-17
- 打赏
- 举报
呵呵，你结贴真快呀：
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 distinct
[user],
a as ACCESS_PAGE_A_TIME,
b as ACCESS_PAGE_B_TIME
from
(
select id,
[user],
[ACCESS_PAGE],
[ACCESS_DATE_TIME],
max(case when [ACCESS_PAGE] = 'PAGE-A' then [ACCESS_DATE_TIME] else null end) over(partition by [user]) as a,
max(case when [ACCESS_PAGE] = 'PAGE-B' then [ACCESS_DATE_TIME] else null end) over(partition by [user]) as b
from huang
)t
where a is not null and b is not null
/*
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
*/
biyoutzx 2013-10-17
- 打赏
- 举报
感谢大家快速的回复！
苦逼的程序员 2013-10-17
- 打赏
- 举报
顶一个
Andy__Huang 2013-10-17
- 打赏
- 举报
;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
/*
USER ACCESS_PAGE_A_TIME ACCESS_PAGE_B_TIME
A 2013/10/03 2013/10/02
D 2013/10/09 2013/10/11
*/
Andy__Huang 2013-10-17
- 打赏
- 举报
select *
from
(
select *,rn=ROW_NUMBER() over(order by ACCESS_DATE_TIME desc) from TB_DataPlat
)t
where rn=1
發糞塗牆 2013-10-17
- 打赏
- 举报
----------------------------------------------------------------
-- 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
*/
