17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT DISTINCT SBBH,CDBH MAX(IMGURL),MAX(SBMC) FROM TABLE GROUP BY SBBH,CDBH
select * from
(SELECT distinct JGCL.SBBH, JGCL.CDBH, SBXX.SBMC,
JGCL.JLBH,
JGCL.HPHM,
JGCL.IMGURL,
JGCL.HPZL,
JGCL.JGSJ,
JGCL.CLSD,
JGCL.CWKC,
JGCL.HPYS,
JGCL.CLLX,
JGCL.JLLX,
JGCL.BZWZDM,
JGCL.FXBH,
JGCL.TPSL,
JGCL.SJLY,
JGCL.CCLX,
JGCL.CSYS,
JGCL.JLLB,
JGCL.CSBJ,
JGCL.YZYS
row_number()over(partition by JGCL.SBBH, JGCL.CDBH, SBXX.SBMC order by JGCL.JLBH desc,JGCL.HPHM desc,JGCL.IMGURL desc,JGCL.HPZL desc) rn
FROM b_bk_jgcl JGCL
left join B_SSSB_SBXX sbxx
on JGCL.SBBH= SBXX.SBBH
WHERE 1 = 1
AND jgsj >= to_date('2016-10-26 16:15:00', 'yyyy-mm-dd hh24:mi:ss')
AND jgsj < to_date('2016-10-26 16:25:00', 'yyyy-mm-dd hh24:mi:ss')
) where rn=1
;
大概这个样子,可能你的语句还是不全。
row_number()over(partition by JGCL.SBBH, JGCL.CDBH, SBXX.SBMC order by JGCL.JLBH desc,JGCL.HPHM desc,JGCL.IMGURL desc,JGCL.HPZL desc) 后面的排序我也没排完,你自己加吧(不加也可以执行)SELECT distinct JGCL.SBBH, JGCL.CDBH, SBXX.SBMC,
MAX(JGCL.JLBH) as "jlbh",
MAX(JGCL.HPHM) as "hphm",
MAX(JGCL.IMGURL) as "IMGURL",
MAX(JGCL.HPZL) as "hpzl",
MAX(JGCL.JGSJ) as "jgsj",
MAX(JGCL.CLSD) as "clsd",
MAX(JGCL.CWKC) as "cwkc",
MAX(JGCL.HPYS) as "hpys",
MAX(JGCL.CLLX) as "cllx",
MAX(JGCL.JLLX) as "jllx",
MAX(JGCL.BZWZDM) as "bzwzdm",
MAX(JGCL.FXBH) as "fxbh",
MAX(JGCL.TPSL) as "tpsl",
MAX(JGCL.SJLY) as "sjly",
MAX(JGCL.CCLX) as "cclx",
MAX(JGCL.CSYS) as "csys",
MAX(JGCL.JLLB) as "jllb",
MAX(JGCL.CSBJ) as "csbj",
MAX(JGCL.YZYS) as "yzys",
FROM b_bk_jgcl JGCL
left join B_SSSB_SBXX sbxx
on JGCL.SBBH= SBXX.SBBH
WHERE 1 = 1
AND jgsj >= to_date('2016-10-26 16:15:00', 'yyyy-mm-dd hh24:mi:ss')
AND jgsj < to_date('2016-10-26 16:25:00', 'yyyy-mm-dd hh24:mi:ss')
Group By JGCL.SBBH, JGCL.CDBH, SBXX.SBMC
select SBBH,CDBH,IMGURL,SBMC from
(SELECT SBBH,CDBH,IMGURL,SBMC,row_number()over(partition by SBBH,CDBH order by IMGURL desc,SBMC desc) rn FROM TABLE )
where rn=1;