代码之家 › 专栏 › 技术社区 › Milan Pk

SQL查询未返回最大日期

greatest-n-per-group aggregate-functions sql-server

Milan Pk · 技术社区 · 6 年前

有人可以查看我的查询并告诉我为什么没有达到最大值(最晚日期)?

我希望cst\U在最新的dts\U srv\U ts(日期时间)之前。

这是我的代码:

SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
--max(dbo.TRX202_ITEM_CHARGE.cst_at),
dbo.TRX202_ITEM_CHARGE.cst_at,
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  

GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,
dbo.TRX202_ITEM_CHARGE.cst_at
order by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id

这是我得到的结果:

chg_cod_ext_id     cst_at     dts_srv_ts
440001              1.6500   2014-06-24 15:26:52.000
440001              8.8440   2015-01-26 12:22:24.000
440001              13.6070  2015-12-23 02:12:10.000
440001              16.7080  2017-04-30 07:56:04.000
440001              16.7310  2016-09-04 21:25:12.000
440001              16.9080  2017-08-26 05:14:27.000
440001              17.0090  2016-06-03 07:52:29.000
440001              17.0580  2018-03-26 11:15:29.000
440001              17.5460  2017-12-19 11:11:13.000

这就是我想要的:

chg_cod_ext_id     cst_at     dts_srv_ts
440001             17.0580    2018-03-26 11:15:29.000

我们将非常感谢您的帮助!

3 回复 | 直到 6 年前

S3S 6 年前

这里的问题是您在 cst_at 。如果在另一列上使用max,则必须对此进行分组,但它会自然更改分组,并且不会返回所需的结果。快速修复方法是 row_number() 。您还可以通过内部联接或相关子查询来实现这一点。祝Paragon好运!

;with cte as(
SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
dbo.TRX202_ITEM_CHARGE.cst_at,
dbo.TRX111_ITEM_DISPENSE.dts_srv_ts,
RN = row_number() over (partition by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id order by dbo.TRX111_ITEM_DISPENSE.dts_srv_ts desc)

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001')

select * from cte where RN = 1

dfundako 6 年前

如果您正在按ID查找最新日期,您可以尝试直接使用max()?

SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  
GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id
order by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id

Zorkolot 6 年前

当包含其他两列时,返回与这些列组合相关的最大日期时间。

一种方法是 MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) 不包括其他两列。这将返回最大日期时间。可以使用以下方法对此聚合值筛选完整查询 HAVING 。以下是一种使用变量存储最大日期时间的方法:

--Save the maxdatetime to a variable
DECLARE @maxdatetime datetime = 
(
SELECT 
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  

GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,
dbo.TRX202_ITEM_CHARGE.cst_at
)

--Query, filtering the aggregate with HAVING
SELECT 
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id, 
dbo.TRX202_ITEM_CHARGE.cst_at,
MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) AS dts_srv_ts

FROM 
dbo.TRX101_THERAPY_ITEM
INNER JOIN dbo.TRX202_ITEM_CHARGE on dbo.TRX101_THERAPY_ITEM.prx_itm_int_id = dbo.TRX202_ITEM_CHARGE.prx_itm_int_id 
INNER JOIN dbo.TPB900_CHG_CODE_MST ON dbo.TRX202_ITEM_CHARGE.bas_chg_cod_int_id = dbo.TPB900_CHG_CODE_MST.chg_cod_int_id 
INNER JOIN dbo.TRX111_ITEM_DISPENSE ON dbo.TRX202_ITEM_CHARGE.itm_dpn_int_id = dbo.TRX111_ITEM_DISPENSE.itm_dpn_int_id

WHERE   
dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id = '440001'  

GROUP BY dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,
dbo.TRX202_ITEM_CHARGE.cst_at

HAVING MAX(dbo.TRX111_ITEM_DISPENSE.dts_srv_ts) = @maxdatetime

order by dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id