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
create table CUST ( no int, com nvarchar(10) ) go create table LM ( no int, com nvarchar(10), M_LINK nvarchar(10) ) gocreate table [LOG] ( no int, M_LINK nvarchar(10), TIME datetime ) goinsert 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/* COM M_LINK time --------------------- 公司A 李2 2013/10/12 公司B 赵4 2013/11/11 公司C 李7 2013/11/11 */
LM表没有公司名称,要通过NO(公司编号)来连接。刚才发帖填错,以现在的为准。
LM表没有公司名称,要通过NO(公司编号)来连接。刚才发帖填错,以现在的帖子为准。谢谢大神。
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))insert into LM select 1, '张1' union all select 1, '李2' union all select 1, '何3' union all select 2, '赵4' union all select 2, '王5' union all select 2, '钱6' union all select 3, '李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 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 where a.rn=1/* COM G_LINK TIME ---------- ---------- ---------------- 公司A 李2 2013/10/12 公司B 赵4 2013/11/11 公司C 李7 2013/11/11(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)) insert into lm select 1 as no,'张1' as M_LINK union all select 1,'李2' union all select 1,'何3' union all select 2,'赵4' union all select 2,'王5' union all select 2,'钱6' union all select 3,'李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 * from ( --选出日期最晚的,但会有重复的数据 select *,dense_rank() over(partition by no order by time desc) as rownum from LOG )a where a.rownum = 1 )select com, g_link, time from ( select r.no, c.com, r.g_link, r.time,
l.no as lm_no,
--针对重复数据,或者是同一时间但人名不同的 --按照LM表中数据插入的前后顺序排序,取尽早的数据,比如这里取的是"赵4",而不是"王5" ROW_NUMBER() over(partition by l.no order by l.no) as rownum from r inner join LM l on r.no = l.no and r.g_link = l.m_link inner join CUST c on l.no = c.no )a where a.rownum = 1 /* com g_link time 公司A 李2 2013/10/12 公司B 赵4 2013/11/11 公司C 李7 2013/11/11*/
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 * from ( --选出日期最晚的,但会有重复的数据 select *,dense_rank() over(partition by no order by time desc) as rownum from LOG )a where a.rownum = 1 )select com, g_link, dept, position, time from ( select r.no, c.com, r.g_link, r.time,
l.no as lm_no, l.dept, l.position,
--针对重复数据,或者是同一时间但人名不同的 --按照LM表中数据插入的前后顺序排序,取尽早的数据,比如这里取的是"赵4",而不是"王5" ROW_NUMBER() over(partition by l.no order by l.no) as rownum from r inner join LM l on r.no = l.no and r.g_link = l.m_link inner join CUST c on l.no = c.no )a where a.rownum = 1 /* com g_link dept position time 公司A 李2 部门2 职务2 2013/10/12 公司B 赵4 部门4 职务4 2013/11/11 公司C 李7 部门7 职务7 2013/11/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 lg.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_link time dept position 公司A 李2 2013/10/12 部门2 职务2 公司B 赵4 2013/11/11 部门4 职务4 公司C 李7 NULL 部门7 职务7 */
--查询 ;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 lg.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_link time dept position 公司A 李2 2013/10/12 部门2 职务2 公司B 赵4 2013/11/11 部门4 职务4 公司C 李7 NULL 部门7 职务7 */
--查询 ;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 lg.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_link time dept position 公司A 李2 2013/10/12 部门2 职务2 公司B 赵4 2013/11/11 部门4 职务4 公司C 李7 NULL 部门7 职务7 */
对于下面这条sql语句怎么修改成上面的效果,即使为空也显示NULL出来?? 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
对于下面这条sql语句怎么修改成上面的效果,即使为空也显示NULL出来?? 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这个语句这么写以a为主,内连接其他2个表,是没办法实现你要的结果的, 必须以LM和cust为主表,左联LOG表,修改后基本上和我上面写的差不多
经过测试,这个还是有点点缺陷呢,就是当删去LOG中公司B的所有联系记录,公司C的李7就不显示了。 LOG表只留下: 1 张1 2013/09/12 1 李2 2013/10/12 1 李2 2013/10/12 1 张1 2013/09/11 查出来的结果是如下,这个要怎么解决? com m_link time dept position 公司A 李2 2013/10/12 部门2 职务2 公司B 赵4 NULL 部门4 职务4
修改了一下,可以实现你的要求:先建表,插入数据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_link time dept position 公司A 李2 2013/10/12 部门2 职务2 公司B 赵4 NULL 部门4 职务4 公司C 李7 NULL 部门7 职务7 */
我们的是SQL Server 2008,在里面可以执行成功,但是放到我们的软件平台中就不支持了。 有没有其他办法实现呢,不用rank,dense_rank,row_number。
我们的是SQL Server 2008,在里面可以执行成功,但是放到我们的软件平台中就不支持了。 有没有其他办法实现呢,不用rank,dense_rank,row_number。哦 ,那你们的软件平台,如果这个语句,不是纯SQL,比如:select * into 临时表 from ...select * from 临时表这样能运行不?
我们的是SQL Server 2008,在里面可以执行成功,但是放到我们的软件平台中就不支持了。 有没有其他办法实现呢,不用rank,dense_rank,row_number。哦 ,那你们的软件平台,如果这个语句,不是纯SQL,比如:select * into 临时表 from ...select * from 临时表这样能运行不? 这个可以。
可以封装为存储过程,不建议在前端程序中写大段的TSQL.
改写了一下,你在你们的软件平台再试试看:--查询 ;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_link time dept position 公司A 李2 2013/10/12 部门2 职务2 公司B 赵4 NULL 部门4 职务4 公司C 李7 NULL 部门7 职务7 */
用 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 就可以了
[log] c
where b.m_link = c.m_link
group by b.com,b.m_link
(
no int,
com nvarchar(10)
)
go
create table LM
(
no int,
com nvarchar(10),
M_LINK nvarchar(10)
)
gocreate table [LOG]
(
no int,
M_LINK nvarchar(10),
TIME datetime
)
goinsert 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
(
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/*
COM M_LINK time
---------------------
公司A 李2 2013/10/12
公司B 赵4 2013/11/11
公司C 李7 2013/11/11
*/
LM表没有公司名称,要通过NO(公司编号)来连接。刚才发帖填错,以现在的为准。
LM表没有公司名称,要通过NO(公司编号)来连接。刚才发帖填错,以现在的帖子为准。谢谢大神。
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))insert into LM
select 1, '张1' union all
select 1, '李2' union all
select 1, '何3' union all
select 2, '赵4' union all
select 2, '王5' union all
select 2, '钱6' union all
select 3, '李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
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
where a.rn=1/*
COM G_LINK TIME
---------- ---------- ----------------
公司A 李2 2013/10/12
公司B 赵4 2013/11/11
公司C 李7 2013/11/11(3 row(s) affected)
*/
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)) insert into lm
select 1 as no,'张1' as M_LINK
union all select 1,'李2'
union all select 1,'何3'
union all select 2,'赵4'
union all select 2,'王5'
union all select 2,'钱6'
union all select 3,'李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 *
from
(
--选出日期最晚的,但会有重复的数据
select *,dense_rank() over(partition by no order by time desc) as rownum
from LOG
)a
where a.rownum = 1
)select com,
g_link,
time
from
(
select r.no,
c.com,
r.g_link,
r.time,
l.no as lm_no,
--针对重复数据,或者是同一时间但人名不同的
--按照LM表中数据插入的前后顺序排序,取尽早的数据,比如这里取的是"赵4",而不是"王5"
ROW_NUMBER() over(partition by l.no order by l.no) as rownum
from r
inner join LM l
on r.no = l.no
and r.g_link = l.m_link
inner join CUST c
on l.no = c.no
)a
where a.rownum = 1 /*
com g_link time
公司A 李2 2013/10/12
公司B 赵4 2013/11/11
公司C 李7 2013/11/11*/
我的lM表还有部门DEPT和职务POSTION:
1 公司A 张1 部门1 职务1
1 公司A 李2 部门2 职务2
1 公司A 李2 部门3 职务3
2 公司B 赵五 部门4 职务4
2 公司B 王六 部门5 职务5
2 公司B 钱1 部门6 职务6
3 公司C 李1 部门7 职务7查询结果也要一起查询出来部门和职务,要怎么改?
1 公司A 张1 部门1 职务1
1 公司A 李2 部门2 职务2
1 公司A 李2 部门3 职务3
2 公司B 赵五 部门4 职务4
2 公司B 王六 部门5 职务5
2 公司B 钱1 部门6 职务6
3 公司C 李1 部门7 职务7查询结果也要一起查询出来部门和职务这两个字段,要怎么改?
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 *
from
(
--选出日期最晚的,但会有重复的数据
select *,dense_rank() over(partition by no order by time desc) as rownum
from LOG
)a
where a.rownum = 1
)select com,
g_link,
dept,
position,
time
from
(
select r.no,
c.com,
r.g_link,
r.time,
l.no as lm_no,
l.dept,
l.position,
--针对重复数据,或者是同一时间但人名不同的
--按照LM表中数据插入的前后顺序排序,取尽早的数据,比如这里取的是"赵4",而不是"王5"
ROW_NUMBER() over(partition by l.no order by l.no) as rownum
from r
inner join LM l
on r.no = l.no
and r.g_link = l.m_link
inner join CUST c
on l.no = c.no
)a
where a.rownum = 1 /*
com g_link dept position time
公司A 李2 部门2 职务2 2013/10/12
公司B 赵4 部门4 职务4 2013/11/11
公司C 李7 部门7 职务7 2013/11/11
*/
假如从没联系过,即使为空没记录都一样显示出来要怎样查询?先删除LOG表中的“3 李7 2013/11/11”,查询结果为:
COM G_LINK TIME DEPT POSTION
---------- ---------- ---------------- ---------- ----------
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 2013/11/11 部门4 职务4
公司C 李7 部门7 职务7
COM G_LINK TIME DEPT POSTION
---------- ---------- ---------------- ---------- ----------
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 2013/11/11 部门4 职务4
公司C 李7 部门7 职务7
;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 lg.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_link time dept position
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 2013/11/11 部门4 职务4
公司C 李7 NULL 部门7 职务7
*/
;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 lg.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_link time dept position
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 2013/11/11 部门4 职务4
公司C 李7 NULL 部门7 职务7
*/
--查询
;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 lg.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_link time dept position
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 2013/11/11 部门4 职务4
公司C 李7 NULL 部门7 职务7
*/
对于下面这条sql语句怎么修改成上面的效果,即使为空也显示NULL出来??
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
对于下面这条sql语句怎么修改成上面的效果,即使为空也显示NULL出来??
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这个语句这么写以a为主,内连接其他2个表,是没办法实现你要的结果的,
必须以LM和cust为主表,左联LOG表,修改后基本上和我上面写的差不多
LOG表只留下:
1 张1 2013/09/12
1 李2 2013/10/12
1 李2 2013/10/12
1 张1 2013/09/11
查出来的结果是如下,这个要怎么解决?
com m_link time dept position
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 NULL 部门4 职务4
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_link time dept position
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 NULL 部门4 职务4
公司C 李7 NULL 部门7 职务7
*/
有没有其他办法实现呢,不用rank,dense_rank,row_number。
有没有其他办法实现呢,不用rank,dense_rank,row_number。哦 ,那你们的软件平台,如果这个语句,不是纯SQL,比如:select * into 临时表
from ...select *
from 临时表这样能运行不?
有没有其他办法实现呢,不用rank,dense_rank,row_number。哦 ,那你们的软件平台,如果这个语句,不是纯SQL,比如:select * into 临时表
from ...select *
from 临时表这样能运行不?
这个可以。
;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_link time dept position
公司A 李2 2013/10/12 部门2 职务2
公司B 赵4 NULL 部门4 职务4
公司C 李7 NULL 部门7 职务7
*/