同一条数据中按时间来做判断取最新一条数据是怎么取呢
2
悬赏园豆：5
[待解决问题]
select c.name as gys,d.name as kh,b.SXSTARDATE as sxksjh,sum(b.RCQTY) as sxsl,sum(b.SXQTY) as sxjs,b.HZPLANDATE as hzjhrq
,b.HZSTARDATE as hzksrq,sum(b.BZQTY)as bzsl,b.JYDATE as jyrq,sum(b.QTYIN) as rksl,b.INTIME as rksj,b.JYRESULT as jyjg,
b.HTHQ ,b.HQBG,DELAYDAY as ywts,b.REMARK as bzsm,b.ORDERQTY as ddsl,b.billdate as htqdrq,b.bljhqty as bljh,b.bldkqty as bldk,
b.FLJHQTY as fljh,b.FLDKQTY as fldk,b.BLWG ,b.BLJC,b.CQYY,b.CJPLANDATE as cjjh,b.CJSTARDATE as cjksrq,b.CJQTY as cwsl,b.SXPLANDATE as sxjhrq
from RP_PRODUCT_SCHE a
left join RP_PRODUCT_SCHEITEM b on b.RP_PRODUCT_SCHE_ID=a.id
left join C_SUPPLIER c on b.Y_SUPPLIER_ID=c.id
left join M_PRODUCT d on b.M_PRODUCT_ID=d.id
left join C_SUPPLIERTYPE f on c.C_SUPPLIERTYPE_ID=f.id
where f.id not in (13,14,15,1,2,11,12,18)
and a.billdate>=to_char(to_date('2021-12-1','YYYY-MM-DD'),'YYYYMMDD')
and a.billdate<=to_char(to_date('2021-12-21','YYYY-MM-DD'),'YYYYMMDD')
and a.STATUS=2 and a.ISACTIVE='Y'
group by b.SXSTARDATE ,b.HZSTARDATE,b.JYDATE ,b.INTIME,
b.HTHQ ,b.HQBG,DELAYDAY ,b.REMARK,b.HZPLANDATE,b.JYRESULT,d.name,c.name,
b.billdate ,b.bljhqty,b.bldkqty ,
b.FLJHQTY ,b.FLDKQTY ,b.BLWG ,b.BLJC,b.CQYY,b.CJPLANDATE ,b.CJSTARDATE ,b.CJQTY ,b.SXPLANDATE,b.ORDERQTY 还未添加CREATIONDATE字段 准备用它做判断
所有回答(3)
0
order by 时间 desc？倒序取第一条？
这样不行 我这是同个时间有两个一样得数据我要取最新得 CREATIONDATE这个字段就是要获取最新数据 而且我这个是多条
@借口/*-: 那你添加一个排序字段，然后去取最大排序的那个数据？
@但乱红尘: 不行得哦 你排完序它那个旧得还存在
@借口/*-: 啥叫还存在，max（sort）取排序最大不就好了嘛
@但乱红尘: 他这个数据有存在得 必须要两个表关联才能取最大得，要不然最小得不是还存在了嘛
0
最笨得方法就是：
select a.* from
(
select c.name as gys,d.name as kh,b.SXSTARDATE as sxksjh,sum(b.RCQTY) as sxsl,sum(b.SXQTY) as sxjs,b.HZPLANDATE as hzjhrq,b.CREATIONDATE
,b.HZSTARDATE as hzksrq,sum(b.BZQTY)as bzsl,b.JYDATE as jyrq,sum(b.QTYIN) as rksl,b.INTIME as rksj,b.JYRESULT as jyjg,
b.HTHQ ,b.HQBG,DELAYDAY as ywts,b.REMARK as bzsm,b.ORDERQTY as ddsl,b.billdate as htqdrq,b.bljhqty as bljh,b.bldkqty as bldk,
b.FLJHQTY as fljh,b.FLDKQTY as fldk,b.BLWG ,b.BLJC,b.CQYY,b.CJPLANDATE as cjjh,b.CJSTARDATE as cjksrq,b.CJQTY as cwsl,b.SXPLANDATE as sxjhrq
from RP_PRODUCT_SCHE a
left join RP_PRODUCT_SCHEITEM b on b.RP_PRODUCT_SCHE_ID=a.id
left join C_SUPPLIER c on b.Y_SUPPLIER_ID=c.id
left join M_PRODUCT d on b.M_PRODUCT_ID=d.id
left join C_SUPPLIERTYPE f on c.C_SUPPLIERTYPE_ID=f.id
where f.id not in (13,14,15,1,2,11,12,18)
and a.billdate>=to_char(to_date('2021-12-1','YYYY-MM-DD'),'YYYYMMDD')
and a.billdate<=to_char(to_date('2021-12-21','YYYY-MM-DD'),'YYYYMMDD')
and a.STATUS=2 and a.ISACTIVE='Y'
group by b.SXSTARDATE ,b.HZSTARDATE,b.JYDATE ,b.INTIME,
b.HTHQ ,b.HQBG,DELAYDAY ,b.REMARK,b.HZPLANDATE,b.JYRESULT,d.name,c.name,
b.billdate ,b.bljhqty,b.bldkqty ,b.CREATIONDATE,
b.FLJHQTY ,b.FLDKQTY ,b.BLWG ,b.BLJC,b.CQYY,b.CJPLANDATE ,b.CJSTARDATE ,b.CJQTY ,b.SXPLANDATE,b.ORDERQTY
) a ,
(
select gys ,kh , max(CREATIONDATE) as CREATIONDATE from
(
select c.name as gys,d.name as kh,b.SXSTARDATE as sxksjh,sum(b.RCQTY) as sxsl,sum(b.SXQTY) as sxjs,b.HZPLANDATE as hzjhrq,b.CREATIONDATE
,b.HZSTARDATE as hzksrq,sum(b.BZQTY)as bzsl,b.JYDATE as jyrq,sum(b.QTYIN) as rksl,b.INTIME as rksj,b.JYRESULT as jyjg,
b.HTHQ ,b.HQBG,DELAYDAY as ywts,b.REMARK as bzsm,b.ORDERQTY as ddsl,b.billdate as htqdrq,b.bljhqty as bljh,b.bldkqty as bldk,
b.FLJHQTY as fljh,b.FLDKQTY as fldk,b.BLWG ,b.BLJC,b.CQYY,b.CJPLANDATE as cjjh,b.CJSTARDATE as cjksrq,b.CJQTY as cwsl,b.SXPLANDATE as sxjhrq
from RP_PRODUCT_SCHE a
left join RP_PRODUCT_SCHEITEM b on b.RP_PRODUCT_SCHE_ID=a.id
left join C_SUPPLIER c on b.Y_SUPPLIER_ID=c.id
left join M_PRODUCT d on b.M_PRODUCT_ID=d.id
left join C_SUPPLIERTYPE f on c.C_SUPPLIERTYPE_ID=f.id
where f.id not in (13,14,15,1,2,11,12,18)
and a.billdate>=to_char(to_date('2021-12-1','YYYY-MM-DD'),'YYYYMMDD')
and a.billdate<=to_char(to_date('2021-12-21','YYYY-MM-DD'),'YYYYMMDD')
and a.STATUS=2 and a.ISACTIVE='Y'
group by b.SXSTARDATE ,b.HZSTARDATE,b.JYDATE ,b.INTIME,
b.HTHQ ,b.HQBG,DELAYDAY ,b.REMARK,b.HZPLANDATE,b.JYRESULT,d.name,c.name,
b.billdate ,b.bljhqty,b.bldkqty ,b.CREATIONDATE,
b.FLJHQTY ,b.FLDKQTY ,b.BLWG ,b.BLJC,b.CQYY,b.CJPLANDATE ,b.CJSTARDATE ,b.CJQTY ,b.SXPLANDATE,b.ORDERQTY
)
group by gys ,kh
) b where a.gys =b.gys and a.kh=b.kh and b.CREATIONDATE =a.CREATIONDATE
用union呢
@但乱红尘: union 语句必须拥有相同数量的列 我这个它不需要
直接关联之后对比 但是数据量查询就是double 了
0
今天刚学习的 开窗函数 dense_rank() 函数