表一 表二我初学SQL,遇到一个联合查询问题,如上两图,表一和表二.
先查询表一,要查询出同一CFPFHOLDING1中CFPFHOLDINGID最大的一行.比如说表一中最后一列CFPFJOLDING1为000001的有五行,那么就查询出CFPFHOLDINGID中最大的那行,也就是查询出CFPFHOLDINGID为1500的那行,以此类推.然后查询表二,同样的道理,查询出表二中同一PTCODE中ID最大的一行,例如PTCODE为71000003的就查询出ID为56290的一行,以此类推.最后将这两个表的数据联合起来输出,而这两个表之间的联系关键字就是PTCODE,就是说刚刚查询的表一的某一行数据的PTCODE跟表二的PTCODE是相同的话,就合成一行输出,假如我这里输出时候只显示PTCODE,CFPFHOLDING1,REPORTDATE 两个表分别叫tab1,tab2. 请问该如何写语句啊.
上面的情况挺绕的,不知道各位理解清楚没,感觉挺麻烦的
先查询表一,要查询出同一CFPFHOLDING1中CFPFHOLDINGID最大的一行.比如说表一中最后一列CFPFJOLDING1为000001的有五行,那么就查询出CFPFHOLDINGID中最大的那行,也就是查询出CFPFHOLDINGID为1500的那行,以此类推.然后查询表二,同样的道理,查询出表二中同一PTCODE中ID最大的一行,例如PTCODE为71000003的就查询出ID为56290的一行,以此类推.最后将这两个表的数据联合起来输出,而这两个表之间的联系关键字就是PTCODE,就是说刚刚查询的表一的某一行数据的PTCODE跟表二的PTCODE是相同的话,就合成一行输出,假如我这里输出时候只显示PTCODE,CFPFHOLDING1,REPORTDATE 两个表分别叫tab1,tab2. 请问该如何写语句啊.
上面的情况挺绕的,不知道各位理解清楚没,感觉挺麻烦的
select a.PTCODE,a.CFPFHOLDING1,a.REPORTDATE
from(
select *
from tab1 t
where not exists (select 1 from tab1 where CFPFHOLDING1 = t.CFPFHOLDING1 and CFPFHOLDINGID > t.CFPFHOLDINGID)
) a join
(
select *
from tab2 t
where not exists (select 1 from tab1 where PTCODE = t.PTCODE and ID > t.ID)
) b on a.PTCODE = b.PTCODE
--试试看!select a.PTCODE,a.CFPFHOLDING1,a.REPORTDATE,b.*
from(
select *
from tab1 t
where not exists (select 1 from tab1 where CFPFHOLDING1 = t.CFPFHOLDING1 and CFPFHOLDINGID > t.CFPFHOLDINGID)
) a join
(
select *
from tab2 t
where not exists (select 1 from tab1 where PTCODE = t.PTCODE and ID > t.ID)
) b on a.PTCODE = b.PTCODE
select t1.PTCODE,t1.CFPFHOLDING1,t1.REPORTDATE
from
(
select PTCODE,CFPFHOLDING1,CFPFHOLDINGID
from tab1 t where not exists(select 1 from tab1 where t.CFPFHOLDING1=CFPFHOLDING1 and CFPFHOLDINGID>t.CFPFHOLDINGID)
)t1,
(
select ID,PTCODE
from tab2 t where not exists(select 1 from tab2 where t.PTCODE=PTCODE and ID>t.ID)
)t2
where t1.PTCODE=t2.PTCODE
比如说表一中最后一列CFPFJOLDING1为000001的有五行,那么就查询出CFPFHOLDINGID中最大的那行,
也就是查询出CFPFHOLDINGID为1500的那行,以此类推.select * from tb a
where not exists
(select 1 from tb b where a.CFPFHOLDING1=b.CFPFHOLDING1 and b.CFPFHOLDINGID>a.CFPFHOLDINGID)
select * from tb a
where not exists
(select 1 from tb b where a.PTCODE=b.PTCODE and b.[ID]>a.[ID])
from
(
select * from tab1 a
where not exists
(select 1 from tab1 b where a.CFPFHOLDING1=b.CFPFHOLDING1 and b.CFPFHOLDINGID>a.CFPFHOLDINGID)
) ta,
(
select * from tab2 a
where not exists
(select 1 from tab2 b where a.PTCODE=b.PTCODE and b.[ID]>a.[ID])
) tb
where ta.PTCOD=tb.PTCOD
若是针对某个单个的,如例子那样,
select * from tab1 where CFPFJOLDING1='000001' and
CFPFHOLDINGID=(select MAX(CFPFHOLDINGID) from tab1 and CFPFJOLDING1='000001')
inner join
select * from tab2 where PTCODE='71000003' and
ID=(select MAX(ID) from tab2 and PTCODE='71000003' );