select
证件编号ZJBH = a.ZJBH,
人员姓名RYXM = a.RYXM,
档案编号DABH = b.DABH,
档案期号DAQH = c.DAQH,
记录次数JLCS = c.JLCS,
最大记录次数JLCS = b.JLCS
from
table2 a,
table3 b,
table1 c
where
a.ZJBH=c.ZJBH
and
b.DABH=c.DABH
and
not exists(select 1 from table1 where DAQH>c.DAQH)
证件编号ZJBH = a.ZJBH,
人员姓名RYXM = a.RYXM,
档案编号DABH = b.DABH,
档案期号DAQH = c.DAQH,
记录次数JLCS = c.JLCS,
最大记录次数JLCS = b.JLCS
from
table2 a,
table3 b,
table1 c
where
a.ZJBH=c.ZJBH
and
b.DABH=c.DABH
and
not exists(select 1 from table1 where DAQH>c.DAQH)
证件编号ZJBH = a.ZJBH,
人员姓名RYXM = a.RYXM,
档案编号DABH = b.DABH,
档案期号DAQH = c.DAQH,
记录次数JLCS = c.JLCS,
最大记录次数JLCS = b.JLCS
from
table2 a,
table3 b,
table1 c
where
a.ZJBH=c.ZJBH
and
b.DABH=c.DABH
and
not exists(select 1 from table1 where ZJBH=c.ZJBH DAQH>c.DAQH)
create table table1(ZJBH varchar(20),DABH int,DAQH int,JLCS int)
insert into table1 select '0001',1,1,3
insert into table1 select '0001',1,2,2
insert into table1 select '0002',1,1,3
insert into table1 select '0002',1,2,3
insert into table1 select '0002',1,3,1
create table table2(ZJBH varchar(20),RYXM varchar(20))
insert into table2 select '0001','张一'
insert into table2 select '0002','张二'
create table table3(DABH int,JLCS int)
insert into table3 select 1,3--执行查询
select
证件编号ZJBH = a.ZJBH,
人员姓名RYXM = a.RYXM,
档案编号DABH = b.DABH,
档案期号DAQH = c.DAQH,
记录次数JLCS = c.JLCS,
最大记录次数JLCS = b.JLCS
from
table2 a,
table3 b,
table1 c
where
a.ZJBH=c.ZJBH
and
b.DABH=c.DABH
and
not exists(select 1 from table1 where ZJBH=c.ZJBH and DAQH>c.DAQH)--输出结果
/*
证件编号ZJBH,人员姓名RYXM ,档案编号DABH,档案期号DAQH,记录次数JLCS,最大记录次数JLCS
0001 张一 1 2 2 3
0002 张二 1 3 1 3
*/--删除测试数据
drop table table1,table2,table3
go
group by ZJBH
不过我测试了一下,结果不对。可能我把数据简化了再发给大家的原因吧!
我对SQL语句不熟悉,还请解释一下这条SQL语句,多谢!
给分了!
将table2中每条记录在table1中对应DAQH最大的那条记录选出来,
并且与table3关联,取出table1中记录档案编号(DABH)对应的档案最大记录次数(JLCS)。
现在我的SQL语句已验证成功,是:
SELECT a.DWDM, a.ZJBH, a.RYXM, b.DABH, c.DAQH, c.JLCS, b.JLCS AS Expr1
FROM T_DA_RYXX a INNER JOIN
T_DA_RYDA c ON a.ZJBH = c.ZJBH AND a.ZJBH = c.ZJBH INNER JOIN
T_DA_DAXX b ON c.DABM = b.DABH
WHERE (NOT EXISTS
(SELECT 1
FROM t_da_ryda
WHERE DAQH > c.DAQH AND zjbh = a.zjbh AND dabm = b.dabh))
因为每个人还有多种档案,所以在最好还加了一个"...AND dabm = b.dabh)) "
drop table table1if exists(select * from sysobjects where name='table2' and type='u')
drop table table2if exists(select * from sysobjects where name='table3' and type='u')
drop table table3
gocreate table table1(ZJBH varchar(20) not null,DABH int not null,DAQH int not null, JLCS int not null)
create table table2(ZJBH varchar(20) not null,RYXM varchar(20) not null)
create table table3(DABH int not null, JLCS int not null)
go
insert table1
select '0001', 1, 1,3 union all
select '0001', 1, 2,2 union all
select '0002', 1, 1,3 union all
select '0002', 1, 2,3 union all
select '0002', 1, 3,1 insert table2
select '0001','張一' union all
select '0002','張二' insert table3
select 1,3
goselect * from table1
/*
ZJBH DABH DAQH JLCS
0001 1 1 3
0001 1 2 2
0002 1 1 3
0002 1 2 3
0002 1 3 1
*/
select * from table2
/*
ZJBH RYXM
0001 張一
0002 張二
*/
select * from table3
/*
DABH JLCS
1 3
*/
--方法一:
select a.ZJBH 証件編號ZJBH,c.RYXM 人員姓名RYXM,a.DABH 檔案編號DABH,a.DAQH 檔案期號DAQH,a.JLCS 記錄次數JLCS,d.JLCS 最大記錄次數JLCS from table1 a join (select ZJBH,DABH,max(DAQH) DAQH from table1 group by ZJBH,DABH)b on a.ZJBH=b.ZJBH and a.DABH=b.DABH and a.DAQH=b.DAQH
join table2 c on a.ZJBH=c.ZJBH join table3 d on a.DABH=d.DABH
/*
証件編號(ZJBH), 檔案編號(DABH),檔案期號(DAQH),記錄次數(JLCS)
0001 1 1 3
0001 1 2 2
0002 1 1 3
0002 1 2 3
0002 1 3 1
*/--方法二:
select a.ZJBH 証件編號ZJBH,c.RYXM 人員姓名RYXM,a.DABH 檔案編號DABH,a.DAQH 檔案期號DAQH,a.JLCS 記錄次數JLCS,d.JLCS 最大記錄次數JLCS from table1 a
join table2 c on a.ZJBH=c.ZJBH join table3 d on a.DABH=d.DABH where exists (select 1 from table1 where a.ZJBH=ZJBH and a.DABH=DABH having a.DAQH=max(DAQH))
/*
証件編號(ZJBH), 檔案編號(DABH),檔案期號(DAQH),記錄次數(JLCS)
0001 1 1 3
0001 1 2 2
0002 1 1 3
0002 1 2 3
0002 1 3 1
*/
--方法三:
select a.ZJBH 証件編號ZJBH,c.RYXM 人員姓名RYXM,a.DABH 檔案編號DABH,a.DAQH 檔案期號DAQH,a.JLCS 記錄次數JLCS,d.JLCS 最大記錄次數JLCS from table1 a
join table2 c on a.ZJBH=c.ZJBH join table3 d on a.DABH=d.DABH where not exists (select 1 from table1 where a.ZJBH=ZJBH and a.DABH=DABH and a.DAQH<DAQH)
/*
証件編號(ZJBH), 檔案編號(DABH),檔案期號(DAQH),記錄次數(JLCS)
0001 1 1 3
0001 1 2 2
0002 1 1 3
0002 1 2 3
0002 1 3 1
*/