求教，急！两天都没有解决，关于三个表连接之后的数据去除重复的问题。
本帖最后由 liao_1000 于 2013-10-09 16:04:10 编辑 关于三个表连接之后的数据去除重复的问题。
一、CUST表（用于记录公司）：
NO（公司编号）,COM(公司名)
1 公司A
2 公司B
3 公司C
二、LM表（用于记录公司的联系人,一个公司多个联系人）：
NO（公司编号），M_LINK（联系人)
1 张1
1 李2
1 何3
2 赵4
2 王5
2 钱6
3 李7
三、LOG表（用于记录联系日志）：
NO(公司编号)，G_LINK(联系人名)，TIME(时间)
1 张1 2013/09/12
1 李2 2013/10/12
1 李2 2013/10/12(有可能跟上一条完全相同的，怎么去重？)
2 赵4 2013/11/11
2 王5 2013/11/11（可能时间相同，人不同）
2 钱6 2013/08/03
3 李7 2013/11/11
1 张1 2013/09/11
三个表连接之后的数据去除重复想得到的效果是：只出现每个公司的一个联系人的其中一个最新日志记录
COM(公司名),LINK(联系人名)，TIME(时间)
公司A 李2 2013/10/12
公司B 赵4 2013/11/11
公司C 李7 2013/11/11 多表连接 去重
[解决办法]
create table CUST
(
no int,
comnvarchar(10)
)
go
create table LM
(
no int,
comnvarchar(10),
M_LINK nvarchar(10)
)
go
create table [LOG]
(
no int,
M_LINK nvarchar(10),
TIME datetime
)
go
insert into cust values(1,'公司A')
insert into cust values(2,'公司B')
insert into cust values(3,'公司C')
insert into lm values(1,'公司A','张1')
insert into lm values(1,'公司A','李2')
insert into lm values(1,'公司A','何3')
insert into lm values(2,'公司B','赵4')
insert into lm values(2,'公司B','王5')
insert into lm values(2,'公司B','钱6')
insert into lm values(3,'公司C','李7')
insert into [log] values(1,'张1','2013/09/12')
insert into [log] values(2,'李2','2013/10/12')
insert into [log] values(3,'李2','2013/10/12')
insert into [log] values(4,'赵4','2013/11/11')
insert into [log] values(5,'王5','2013/11/11')
insert into [log] values(6,'钱6','2013/08/03')
insert into [log] values(7,'李7','2013/11/11')
insert into [log] values(8,'张1','2013/09/11')
select b.com,b.m_link,max(c.time) from lm b,
[log] c
where b.m_link = c.m_link
group by b.com,b.m_link
;with CUST(NO,COM) as
(
select 1,'公司A'
union all select 2,'公司B'
union all select 3,'公司C'
),
LM(NO,COM,M_LINK) as
(
select 1,'公司A','张1'
union all select 1,'公司A','李2'
union all select 1,'公司A','何3'
union all select 2,'公司B','赵4'
union all select 2,'公司B','王5'
union all select 2,'公司B','钱6'
union all select 3,'公司C','李7'
),
[LOG](NO,G_LINK,[TIME]) AS
(
select 1,'张1','2013/09/12'
union all select 2,'李2','2013/10/12'
union all select 3,'李2','2013/10/12'
union all select 4,'赵4','2013/11/11'
union all select 5,'王5','2013/11/11'
union all select 6,'钱6','2013/08/03'
union all select 7,'李7','2013/11/11'
union all select 8,'张1','2013/09/11'
)
select COM,M_LINK,[time]
from
(select a.*,b.TIME,rn=ROW_NUMBER() over(partition by a.com order by b.time desc)
from LM a
left join [LOG] b on a.M_LINK=b.G_LINK
)t
where rn=1
/*
COMM_LINKtime
---------------------
公司A李22013/10/12
公司B赵42013/11/11
公司C李72013/11/11
*/
create table CUST
(NO int,COM varchar(10))
insert into CUST
select 1, '公司A' union all
select 2, '公司B' union all
select 3, '公司C'
create table LM
(NO int,M_LINK varchar(10),DEPT varchar(10),POSTION varchar(10))
insert into LM
select 1, '张1', '部门1', '职务1' union all
select 1, '李2', '部门2', '职务2' union all
select 1, '何3', '部门3', '职务3' union all
select 2, '赵4', '部门4', '职务4' union all
select 2, '王5', '部门5', '职务5' union all
select 2, '钱6', '部门6', '职务6' union all
select 3, '李7', '部门7', '职务7'
create table LOG
(NO int,G_LINK varchar(10),TIME varchar(16))
insert into LOG
select 1, '张1', '2013/09/12' union all
select 1, '李2', '2013/10/12' union all
select 1, '李2', '2013/10/12' union all
select 2, '赵4', '2013/11/11' union all
select 2, '王5', '2013/11/11' union all
select 2, '钱6', '2013/08/03' union all
select 3, '李7', '2013/11/11' union all
select 1, '张1', '2013/09/11'
select b.COM,a.G_LINK,a.TIME,c.DEPT,c.POSTION
from
(select NO,G_LINK,TIME,
row_number() over(partition by NO
order by cast(TIME as datetime) desc,G_LINK desc) 'rn'
from (select distinct NO,G_LINK,TIME from log) t
) a
inner join CUST b on a.NO=b.NO
inner join LM c on a.NO=c.NO and a.G_LINK=c.M_LINK
where a.rn=1
/*
COM G_LINK TIME DEPT POSTION
---------- ---------- ---------------- ---------- ----------
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 2013/11/11 部门4 职务4
公司C 李7 2013/11/11 部门7 职务7
(3 row(s) affected)
*/
create table CUST (no int,com varchar(20))
insert into CUST
select 1 as no,'公司A' as com
union all select 2,'公司B'
union all select 3,'公司C'
create table LM(no int,m_link varchar(20),dept varchar(30),position varchar(30))
insert into lm
select 1 ,'张1','部门1', '职务1'
union all select 1,'李2','部门2', '职务2'
union all select 1,'何3','部门3', '职务3'
union all select 2,'赵4','部门4', '职务4'
union all select 2,'王5','部门5', '职务5'
union all select 2,'钱6','部门6', '职务6'
union all select 3,'李7','部门7', '职务7'
create table LOG(no int,g_link varchar(20),time varchar(10))
insert into log
select 1 as no ,'张1' as g_link,'2013/09/12' as time
union all select 1,'李2','2013/10/12'
union all select 1,'李2','2013/10/12'
--union all select 2,'赵4','2013/11/11'
--union all select 2,'王5','2013/11/11'
--union all select 2,'钱6','2013/08/03'
--union all select 3,'李7','2013/11/11'
union all select 1,'张1','2013/09/11'
--查询
;with r
as
(
select distinct
no,
m_link,
dept,
position,
time
from
(
--选出日期最晚的,但会有重复的数据
select lm.no,
lm.m_link,
lm.dept,
lm.position,
lg.time,
dense_rank() over(partition by lm.no --这里由原来的lg.no改为lm.no
order by time desc,lm.m_link desc) as rownum
from LM
LEFt JOIN LOG lg
on Lm.no = lg.no
and LM.m_link = lg.g_link
)a
where a.rownum = 1
)
select com,
m_link,
time,
dept,
position
from r
inner join CUST c
on r.no = c.no
/*
com m_linktime deptposition
公司A李2 2013/10/12部门2职务2
公司B赵4 NULL 部门4职务4
公司C李7 NULL 部门7职务7
*/
--查询
;with rr
as
(
select distinct
l.no,
l.g_link,
l.time
from log L
inner join
(
select no,
MAX(time) as time
from LOG
group by no
)ll
on l.no = ll.no
and l.time = ll.time
),
r
as
(
select lm.*,rr.time
from lm
left join rr
on lm.no = rr.no
and lm.m_link = rr.g_link
)
select --c.no,
c.com,
(
select top 1 r.m_link from r where r.no = c.no
order by r.time desc,r.m_link desc
) as m_link,
(
select top 1 r.time from r where r.no = c.no
order by r.time desc,r.m_link desc
) as time,
(
select top 1 r.dept from r where r.no = c.no
order by r.time desc,r.m_link desc
) as dept,
(
select top 1 r.position from r where r.no = c.no
order by r.time desc,r.m_link desc
) as position
from CUST c
/*
com m_linktime deptposition
公司A李2 2013/10/12部门2职务2
公司B赵4 NULL 部门4职务4
公司C李7 NULL 部门7职务7
*/
