首页 新闻 搜索 专区 学院

同一条数据中按时间来做判断取最新一条数据是怎么取呢

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字段   准备用它做判断
借口/*-的主页 借口/*- | 初学一级 | 园豆:10
提问于:2021-12-22 09:26
< >
分享
所有回答(3)
0

order by 时间 desc?倒序取第一条?

气氛组(红尘熊) | 园豆:460 (菜鸟二级) | 2021-12-22 15:11

这样不行 我这是同个时间有两个一样得数据我要取最新得 CREATIONDATE这个字段就是要获取最新数据 而且我这个是多条

支持(0) 反对(0) 借口/*- | 园豆:10 (初学一级) | 2021-12-22 15:15

@借口/*-: 那你添加一个排序字段,然后去取最大排序的那个数据?

支持(0) 反对(0) 气氛组(红尘熊) | 园豆:460 (菜鸟二级) | 2021-12-23 10:14

@但乱红尘: 不行得哦 你排完序它那个旧得还存在

支持(0) 反对(0) 借口/*- | 园豆:10 (初学一级) | 2021-12-23 14:18

@借口/*-: 啥叫还存在,max(sort)取排序最大不就好了嘛

支持(0) 反对(0) 气氛组(红尘熊) | 园豆:460 (菜鸟二级) | 2021-12-23 14:20

@但乱红尘: 他这个数据有存在得 必须要两个表关联才能取最大得,要不然最小得不是还存在了嘛

支持(0) 反对(0) 借口/*- | 园豆:10 (初学一级) | 2021-12-23 14:25
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

借口/*- | 园豆:10 (初学一级) | 2021-12-23 14:19

用union呢

支持(0) 反对(0) 气氛组(红尘熊) | 园豆:460 (菜鸟二级) | 2021-12-23 14:30

@但乱红尘: union 语句必须拥有相同数量的列 我这个它不需要
直接关联之后对比 但是数据量查询就是double 了

支持(0) 反对(0) 借口/*- | 园豆:10 (初学一级) | 2021-12-23 14:33
0

今天刚学习的 开窗函数 dense_rank() 函数

小刺猬001 | 园豆:660 (小虾三级) | 2021-12-27 17:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册