如何在嵌套选择的查询列中以最大值获取整个行

Question

我在SQL中的查询有问题。我的查询是这样的：

SELECT t.id, t.attribute, t.name, t.date
FROM (
SELECT *
FROM table1
WHERE ...
UNION
SELECT *
FROM table2
WHERE
UNION
...) t
WHERE ...
GROUP BY t.attribute

我想要的是获取每个属性的最新日期的所有行（日期可以为null，然后是最新的）。我知道我可以创建一个临时表，但是如果可能的话，我想避免它。我也想再次嵌套在哪里也不是在连接中的同一查询。有什么办法？

谢谢！

Answer 1

这是使用CTE（公共表格）进行操作的一种方法：

;WITH Maxtable1
    AS (SELECT attribute
            , name
            , MAX(t.date) AS MaxDate
        FROM   table1
        GROUP BY attribute
             , name),
    Maxtable2
    AS (SELECT attribute
            , name
            , MAX(t.date) AS MaxDate
        FROM   table2
        GROUP BY attribute
             , name),
             .
             .
             .
    Maxtable<n>
    AS (SELECT attribute
            , name
            , MAX(t.date) AS MaxDate
        FROM   table<n>
        GROUP BY attribute
             , name),
    DetailTable1
    AS (SELECT id
            , attribute
            , name
            , date
        FROM   table1
             INNER JOIN Maxtable1 ON table1.attribute = Maxtable1.attribute
                                AND table1.name = Maxtable1.name
                                AND table1.date = Maxtable1.date),
    DetailTable2
    AS (SELECT id
            , attribute
            , name
            , date
        FROM   table2
             INNER JOIN Maxtable2 ON table2.attribute = Maxtable2.attribute
                                AND table2.name = Maxtable2.name
                                AND table2.date = Maxtable2.date),
             .
             .
             .
    DetailTable<n>
    AS (SELECT id
            , attribute
            , name
            , date
        FROM   table<n>
             INNER JOIN Maxtable<n> ON table<n>.attribute = Maxtable<n>.attribute
                                AND table<n>.name = Maxtable<n>.name
                                AND table<n>.date = Maxtable<n>.date)
    SELECT *
    FROM   DetailTable1
    UNION
    SELECT *
    FROM   DetailTable2
    UNION
    .
    .
    .
    SELECT *
    FROM   DetailTable<n>;

Answer 2

使用现代SQL您可以使用窗口函数：

SELECT t2.id, t2.attribute, t2.name, t2.date
FROM (
    SELECT t1.id, t1.attribute, t1.name, t1.date, 
           row_number() over (partition by t1.attribute order by t1.date desc) as rn
    FROM (
      SELECT *
      FROM table1
      WHERE ...
      UNION
      SELECT *
      FROM table2
      WHERE ...
      UNION
      ...
   ) t1
) t2
where rn = 1;

或者使用常见的表表达式避免衍生表的嵌套：

with data as (
  SELECT *
  FROM table1
  WHERE ...
  UNION
  SELECT *
  FROM table2
  WHERE ...
  UNION
), numbered as (
  SELECT id, attribute, name, date, 
         row_number() over (partition by t1.attribute order by t1.date desc) as rn
  FROM data
)
SELECT id, attribute, name, date
from numbered 
where rn = 1;