mariadb 使用MAX()对临时列进行内部联接

lfapxunr 于 2022-11-08 发布在其他

关注(0)|答案(1)|浏览(131)

我在MariaDB（latest）中有一个表t，其中包括person_ID、date_1、date_2等列。它们分别包含人员ID和字符串日期。对于每个ID，只有一个date_1，但有多个date_2。行要么有date_1，要么有date_2，这就是我加入ID的原因。下面是表t的一个示例：
| 人员标识|日期_1|日期_2|
| - -|- -|- -|
| A级|- -|三个|
| A级|- -|五个|
| A级|一个|- -|
| 乙|- -|10个|
| 乙|- -|十四|
| 乙|五个|- -|
| C类|- -|十一|
| C类|- -|九个|
| C类|七个|- -|
创建并填写表格t：

CREATE TABLE t(
id SERIAL,
person_ID TEXT,
date_1 TEXT,
date_2 TEXT,
PRIMARY KEY (id)
);

INSERT INTO t (person_ID, date_2) VALUES ('A', 3);
INSERT INTO t (person_ID, date_2) VALUES ('A', 5);
INSERT INTO t (person_ID, date_1) VALUES ('A', 1);
INSERT INTO t (person_ID, date_2) VALUES ('B', 10);
INSERT INTO t (person_ID, date_2) VALUES ('B', 14);
INSERT INTO t (person_ID, date_1) VALUES ('B', 5);
INSERT INTO t (person_ID, date_2) VALUES ('C', 11);
INSERT INTO t (person_ID, date_2) VALUES ('C', 9);
INSERT INTO t (person_ID, date_1) VALUES ('C', 7);

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

以下是两个子查询A和B的内部联接。查询A给出了一个不同的person_ID列表，其中包含date_1和date_1本身。另一方面，查询B应该给出一个不同的person_ID列表，其中包含date_2和MAX（date_2）。

SELECT A.person_ID, A.date_A,  B.date_B, B.date_B - A.date_A AS diff FROM
    (SELECT person_ID, date_1 AS date_A FROM t
    WHERE date_1 >= 0) A
INNER JOIN
    (SELECT person_ID, MAX(date_2) AS date_B FROM t
    WHERE date_2 >= 0
    GROUP BY person_ID) B
ON A.person_ID = B.person_ID
AND B.date_B > A.date_A
AND (B.date_B - A.date_A) <= 7
GROUP BY A.person_ID;

输出如下：
| 人员ID|日期_A|日期_B|差异|
| - -|- -|- -|- -|
| A级|一个|五个|四个|
| C类|七个|九个|2个|
但这将是期望的结果（忽略ID = B，因为diff = 9）：
| 人员ID|日期_A|日期_B|差异|
| - -|- -|- -|- -|
| A级|一个|五个|四个|
| C类|七个|十一|四个|
我假设MAX（date_2）为person_ID = C给出9而不是11，因为该值是最后为date_2插入的。
您可以使用此链接亲自试用。

mariadb

来源：https://stackoverflow.com/questions/73924868/inner-join-on-temporary-columns-using-max

1条答案

按热度按时间

yc0p9oo01#

稀疏表（带有NULL的行）使这个问题变得更加困难。
从一个子查询开始清理稀疏表。它生成一个结果集，其中包含空值的行被删除，生成如下所示的结果。

person_ID     date_1    date_2   diff
        A        1        3        2
        A        1        5        4
        B        5       10        5
        B        5       14        9
        C        7       11        4
        C        7        9        2

这会将每个人的单一date_1值放入具有date_2值的列中。query的执行方式为：

SELECT t.person_ID, b.date_1, t.date_2, t.date_2 - b.date_1 diff
   FROM t 
   JOIN t b      ON t.person_ID = b.person_ID
                AND b.date_1 IS NOT NULL
                AND t.date_2 IS NOT NULL

让我们将该子查询的输出命名为别名detail。
您的业务逻辑需要非常常见的greatest-n-per-group查询模式。它需要检索每个person_ID中具有最大diff的行（与diff <= 7一样长）。使用该detail子查询，我们可以更容易地编写逻辑。在结果集中，您希望每个person_ID的行显示date_1、date_2并且diff用于最大diff，但是不考虑diff〉7的任何行。
首先，写入another subquery，以找出每个person_ID的最大合格diff值。

SELECT person_ID, MAX(diff) diff
   FROM detail
  GROUP BY person_ID
 HAVING MAX(diff) <= 7

然后将该子查询连接到详细信息，以获得所需结果集。

SELECT detail.*
   FROM detail
   JOIN ( SELECT person_ID, MAX(diff) diff
            FROM detail
           GROUP BY person_ID
           HAVING MAX(diff) <= 7
        ) md ON detail.person_ID = md.person_ID 
             AND detail.diff = md.diff

现在，我使用common table expression来编写此查询：来定义detail。该语法在MariaDB 10.2+（和MySQL 8+）中可用。将其组合在一起，下面是查询。

WITH detail AS
 (SELECT t.person_ID, b.date_1, t.date_2, t.date_2 - b.date_1 diff
        FROM t 
        JOIN t b ON t.person_ID = b.person_ID
                AND b.date_1 IS NOT NULL
                AND t.date_2 IS NOT NULL
 )
 SELECT detail.*
   FROM detail
   JOIN ( SELECT person_ID, MAX(diff) diff
            FROM detail
           GROUP BY person_ID
           HAVING MAX(diff) <= 7
        ) md ON detail.person_ID = md.person_ID 
             AND detail.diff = md.diff

总结：解决问题的步骤。

处理输入表中的稀疏数据问题......通过在具有date_2值的行中填充date_1值来去除具有NULL值的输入行。
找出每个person_ID的最大合格diff值。
将最大得diff列表重新连接到从表中，以提取从表中正确得行.
专业提示不要关闭ONLY_FULL_GROUP_BY。你不想依赖MySQL / MariaDB的strange nonstandard extension to GROUP BY，因为它有时会产生错误的值。当它产生错误的值时，这是令人困惑的。

赞(0）回复(0）举报 2022-11-08

我来回答

mariadb 使用MAX()对临时列进行内部联接

1条答案

相关问题

热门标签

最新问答