如何获取一行中的最大值以及列名 [英] How to get the highest value in a row together with the column name

问题描述

我有一个包含 10 列的表格，其中八列是带有日期的列.我想要的结果是获得每一行的最大日期(我已经完成了)，但我也想获得列的名称，这个最大日期来自.

I have a table with 10 columns, eight of them are columns with dates. My desired outcome is to get max date for each row (what I have already accomplished) but I would also like to get the name of the columns, which this max dates come from.

    SELECT s.sp_id
,s.sp_numer
,(
    SELECT MAX(Data_przedawnienia_update)
    FROM (
        VALUES (Data_przedawnienia_Postanowienie_o_umorzeniu_egzekucji)
            ,(Data_przedawnienia_Egzekucja_w_toku)
            ,(Data_przedawnienia_Nakaz_zaplaty_z_klauzula)
            ,(Data_przedawnienia_Nakaz_zaplaty)
            ,(Data_przedawnienia_Sprawa_sadowa_w_toku)
            ,(Data_przedawnienia_Wplaty_na_etapie_polubownym)
            ,(Data_przedawnienia_Umowa_ugody)
            ,(Data_przedawnienia_Wypowiedzenie_umowy)
        ) AS Data_przedawnienia(Data_przedawnienia_update)
    ) AS Data_przedawnienia_update
FROM dm_data_bps.dbo.sprawa AS s
INNER JOIN dm_data_bps.dbo.cache_sprawa_info AS csi ON s.sp_id = csi.sp_id
AND sprawa_zamknieta = 0
LEFT JOIN ##Postanowienie_o_umorzeniu_egzekucji AS umorzenie ON s.sp_id =       
umorzenie.sp_id
LEFT JOIN ##Egzekucja_w_toku AS egzekucja ON s.sp_id = egzekucja.sp_id
LEFT JOIN ##Nakaz_zaplaty_z_klauzula AS nakaz_kl ON s.sp_id = nakaz_kl.sp_id
LEFT JOIN ##Nakaz_zaplaty AS nakaz ON s.sp_id = nakaz.sp_id
LEFT JOIN ##Sprawa_sadowa_w_toku AS sprawa_sad ON s.sp_id = sprawa_sad.sp_id
LEFT JOIN ##Wplaty_na_etapie_polubownym AS wplaty_polubowny ON s.sp_id =   
wplaty_polubowny.sp_id
LEFT JOIN ##Umowa_ugody AS ugoda ON s.sp_id = ugoda.sp_id
LEFT JOIN ##Wypowiedzenie_umowy AS wypowiedzenie ON s.sp_id = 
wypowiedzenie.sp_id

推荐答案

XML 具有处理通用查询的强大能力:

XML has great abilities to deal with generic queries:

DECLARE @tbl TABLE(ID INT IDENTITY,d1 DATE, d2 DATE, d3 DATE);
INSERT INTO @tbl VALUES
 ('20180101','20180102','20180103')  --one max value
,('20170101','20190102','20190102'); --two max values

SELECT TOP 1 WITH TIES
       t.ID 
      ,y.value('text()[1]','date') d
      ,y.value('local-name(.)','varchar(100)') c
FROM @tbl t
CROSS APPLY(SELECT d1,d2,d3 FOR XML PATH('d'),TYPE) A(x)
CROSS APPLY x.nodes('/d/*') B(y) 
ORDER BY DENSE_RANK() OVER(PARTITION BY ID ORDER BY y.value('text()[1]','date') DESC);

更新:一些解释

第一个 CROSS APPLY 将创建一个如下所示的 XML:

UPDATE: Some explanation

The first CROSS APPLY will create an XML which looks like this:

<d>
  <d1>2018-01-01</d1>
  <d2>2018-01-02</d2>
  <d3>2018-01-03</d3>
</d>

第二个CROSS APPLY 使用.nodes() 返回 内的所有节点.使用 .value() 我们可以获得元素的名称(local-name())及其内容.

The second CROSS APPLY uses .nodes() to return all nodes within <d>. With .value() we can get the element's name (local-name()) and its content.

DENSE_RANK 和 TOP 1 WITH TIES 的技巧将返回所有获得 1(每个 ID 最高)的行.

The trick with DENSE_RANK and TOP 1 WITH TIES will return all rows which get a 1 (which are the highest per ID).

这篇关于如何获取一行中的最大值以及列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！