DB2:FETCH FIRST 1 ROWS ONLY

本文介绍了如何在DB2和Oracle数据库中实现高效的数据查询。具体地,文章展示了如何使用DB2的FETCH FIRST ROWS ONLY语法,并给出了Oracle中相应的WHERE ROWNUM=1的用法。通过实际例子对比了两种数据库环境下获取最新记录的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

DB2:FETCH FIRST 1 ROWS  ONLY,在Oracle中使用where rownum=1代替
DB2:
 SELECT INSTORAGENO  FROM
                               (
                               SELECT max(AUDITINGTIME) AUDITINGTIME ,INSTORAGENO  FROM T_IN_STOREROOM 
                               order by AUDITINGTIME desc
                               FETCH FIRST 1 ROWS  ONLY
                               ) a 
Oracle:
SELECT INSTORAGENO  FROM
                        (
                        SELECT max(AUDITINGTIME) AUDITINGTIME ,INSTORAGENO  FROM T_IN_STOREROOM 
                        order by AUDITINGTIME desc
                        ) a  where rownum=1

例如:

转载于:https://www.cnblogs.com/rwh871212/p/6962531.html

select left(lcp.managecom,4) 分公司代码,(select name from ldcom where comcode=lcp.managecom) 机构名称, (select labg.BranchAttr from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup)销售团队编码, ( select labg.Name BranchName from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup) 销售团队名称,lcp.appntname 投保人,lcp.insuredname 被保险人,lcp.contno 保单号,riskcode 险种代码, lcp.cvalidate 生效时间,lcp.prem 保费,(select codename from ldcode where codetype='salechnl' and code=lcp.salechnl) 销售渠道, (select groupagentcode from laagent where agentcode=lcp.agentcode) 业务员工号, (select name from laagent where agentcode=lcp.agentcode) 业务员姓名, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only ) 投保人所在销售渠道, (select la.groupagentcode from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only) 集团工号, (select case when agentstate>='06' then '离职' else '在职' end from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only ) 被保险人所在销售渠道, (select groupagentcode from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only) 集团工号, (select case when agentstate>='06' then '离职' else '在职' end from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职 from lcpol lcp where lcp.conttype='1' and lcp.appflag='1' and lcp.cvalidate>='2025-06-01' and lcp.cvalidate<='2025-06-10' and salechnl IN ('04','13','14','15') and exists (select 1 from lcappnt lc, laagent la WHERE lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.agentstate<'06' union select 1 from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.agentstate<'06') ---and la.agentstate<'06'在职 union select left(lcp.managecom,4) 分公司代码,(select name from ldcom where comcode=lcp.managecom) 机构名称, (select labg.BranchAttr from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup)销售团队编码, ( select labg.Name BranchName from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup) 销售团队名称,lcp.appntname 投保人,lcp.insuredname 被保险人,lcp.contno 保单号,riskcode 险种代码, lcp.cvalidate 生效时间,lcp.prem 保费,(select codename from ldcode where codetype='salechnl' and code=lcp.salechnl) 销售渠道, (select groupagentcode from laagent where agentcode=lcp.agentcode) 业务员工号, (select name from laagent where agentcode=lcp.agentcode) 业务员姓名, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only ) 投保人所在销售渠道, (select la.groupagentcode from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only) 集团工号, (select case when agentstate>='06' then '离职' else '在职' end from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职, (select case when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道' when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道' when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道' when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道' when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道' when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道' when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道' when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道' when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道' when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道' when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道' when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道' when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道' when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道' end from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only ) 被保险人所在销售渠道, (select groupagentcode from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only) 集团工号, (select case when agentstate>='06' then '离职' else '在职' end from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职 from lbpol lcp where lcp.conttype='1' and lcp.appflag in ('1','3') and lcp.cvalidate>='2025-06-01' and lcp.cvalidate<='2025-06-10' and salechnl IN ('04','13','14','15') and exists (select 1 from lbappnt lc, laagent la WHERE lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.agentstate<'06' union select 1 from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.agentstate<'06')--la.agentstate<'06'在职 and lcp.edorno not like 'xb%' ; 帮我优化一下db2的sql语句
最新发布
08-07
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值