SQL：如何检索一对多关系中的最新关联

huangapple
go评论93阅读模式
英文:

SQL: How to Retrieve the Most Recent Association in a One-To-Many Relationship

问题

以下是存储一对多关系的表格，其中一个用户可能与一个或多个配置文件相关联：

  1. +----+---------+------------+------------------------+
  2. | ID | USER_ID | PROFILE_ID | LAST_PROFILE_DATE_TIME |
  3. +----+---------+------------+------------------------+
  4. |  1 |     100 |        101 | 04.06.23 08:35:19.5393 |
  5. |  2 |     100 |        102 | 05.06.23 08:35:19.5393 |
  6. +----+---------+------------+------------------------+

如何获取用户 100 的最新配置文件？以下是预期结果：

  1. +------------+
  2. | PROFILE_ID |
  3. +------------+
  4. |        102 |
  5. +------------+

我需要一个单一的值，用于更新另一个表中的字段。

这是我的尝试...但问题是我得到了两行，因为GROUP BY子句需要所有字段，而不是MAX：

  1. SELECT up.PROFILE_ID
  2. FROM
  3.   (SELECT USER_ID, PROFILE_ID, MAX(LAST_PROFILE_DATE_TIME)
  4.      FROM USER_PROFILE
  5.     GROUP BY USER_ID, PROFILE_ID) up
  6. WHERE up.USER_ID = 100;

我需要在Oracle和PostgreSQL上运行此查询。非常感谢您的帮助。

英文:

Here below is a table that stores a one-to-many relationship where one user might be associated with one or more profiles:

  1. +----+---------+------------+------------------------+
  2. | ID | USER_ID | PROFILE_ID | LAST_PROFILE_DATE_TIME |
  3. +----+---------+------------+------------------------+
  4. |  1 |     100 |        101 | 04.06.23 08:35:19.5393 |
  5. |  2 |     100 |        102 | 05.06.23 08:35:19.5393 |
  6. +----+---------+------------+------------------------+

How do I get the latest profile for user 100? Here's the expected result:

  1. +------------+
  2. | PROFILE_ID |
  3. +------------+
  4. |        102 |
  5. +------------+

I need a single value to be used to update a field in another table.

This is my attempt... but the problem is that I get two rows because the GROUP BY clause requires all the fields but MAX:

  1. SELECT up.PROFILE_ID
  2. FROM
  3.   (SELECT USER_ID, PROFILE_ID, MAX(LAST_PROFILE_DATE_TIME)
  4.      FROM USER_PROFILE
  5.     GROUP BY USER_ID, PROFILE_ID) up
  6. WHERE up.USER_ID = 100;

I need to run the query on both Oracle and PostgreSQL. Any help would be really appreciated.

答案1

得分: 2

你可以按照以下方式执行：

首先获取每个USER_ID的最新LAST_PROFILE_DATE_TIME：

  1. select USER_ID, max(LAST_PROFILE_DATE_TIME) as MAX_LAST_PROFILE_DATE_TIME
  2. from USER_PROFILE
  3. where USER_ID = 100
  4. group by USER_ID

然后将这个数据集与你的表连接以获取PROFILE_ID：

  1. select t.PROFILE_ID
  2. from USER_PROFILE t
  3. inner join (
  4.   select USER_ID, max(LAST_PROFILE_DATE_TIME) as MAX_LAST_PROFILE_DATE_TIME
  5.   from USER_PROFILE
  6.   where USER_ID = 100
  7.   group by USER_ID
  8. ) as s on s.USER_ID = t.USER_ID and s.MAX_LAST_PROFILE_DATE_TIME = t.LAST_PROFILE_DATE_TIME;
英文:

You can do it as follows :

First get the latest LAST_PROFILE_DATE_TIME per USER_ID :

  1.   select USER_ID, max(LAST_PROFILE_DATE_TIME) as MAX_LAST_PROFILE_DATE_TIME
  2.   from USER_PROFILE
  3.   where USER_ID = 100
  4.   group by USER_ID

Then join this dataset to your table to obtain PROFILE_ID:

  1. select t.PROFILE_ID
  2. from USER_PROFILE t
  3. inner join (
  4.   select USER_ID, max(LAST_PROFILE_DATE_TIME) as MAX_LAST_PROFILE_DATE_TIME
  5.   from USER_PROFILE
  6.   where USER_ID = 100
  7.   group by USER_ID
  8. ) as s on s.USER_ID = t.USER_ID and s.MAX_LAST_PROFILE_DATE_TIME = t.LAST_PROFILE_DATE_TIME;

答案2

得分: 2

以下是翻译好的部分：

你可以使用 ROW_NUMBER 分析函数：

  1. SELECT profile_id,
  2.        last_profile_date_time
  3. FROM   (
  4.   SELECT profile_id,
  5.          last_profile_date_time,
  6.          ROW_NUMBER()
  7.            OVER (ORDER BY last_profile_date_time DESC) AS rn
  8.   FROM   table_name
  9.   WHERE  user_id = 100
  10.   ORDER BY last_profile_date_time DESC
  11. ) q
  12. WHERE  RN = 1;

或者两种数据库都支持 FETCH FIRST ROW ONLY 语法：

  1. SELECT profile_id,
  2.        last_profile_date_time
  3. FROM   table_name
  4. WHERE  user_id = 100
  5. ORDER BY last_profile_date_time DESC
  6. FETCH FIRST ROW ONLY;

对于示例数据：

  1. CREATE TABLE table_name (
  2.   ID         INT,
  3.   USER_ID    INT,
  4.   PROFILE_ID INT,
  5.   LAST_PROFILE_DATE_TIME TIMESTAMP
  6. );
  8. INSERT INTO table_name (id, user_id, profile_id, last_profile_date_time)
  9.   VALUES (1, 100, 101, TIMESTAMP '2023-06-04 08:35:19.5393' );
  10. INSERT INTO table_name (id, user_id, profile_id, last_profile_date_time)
  11.   VALUES (2, 100, 102, TIMESTAMP '2023-06-05 08:35:19.5393' );

两者的输出结果均为：

profile_id last_profile_date_time
102 2023-06-05 08:35:19.5393

PostgreSQL fiddle Oracle fiddle

英文:

You can use the ROW_NUMBER analytic function:

  1. SELECT profile_id,
  2.        last_profile_date_time
  3. FROM   (
  4.   SELECT profile_id,
  5.          last_profile_date_time,
  6.          ROW_NUMBER()
  7.            OVER (ORDER BY last_profile_date_time DESC) AS rn
  8.   FROM   table_name
  9.   WHERE  user_id = 100
  10.   ORDER BY last_profile_date_time DESC
  11. ) q
  12. WHERE  RN = 1;

or both databases support the FETCH FIRST ROW ONLY syntax:

  1. SELECT profile_id,
  2.        last_profile_date_time
  3. FROM   table_name
  4. WHERE  user_id = 100
  5. ORDER BY last_profile_date_time DESC
  6. FETCH FIRST ROW ONLY;

Which, for the sample data:

  1. CREATE TABLE table_name (
  2.   ID         INT,
  3.   USER_ID    INT,
  4.   PROFILE_ID INT,
  5.   LAST_PROFILE_DATE_TIME TIMESTAMP
  6. );
  8. INSERT INTO table_name (id, user_id, profile_id, last_profile_date_time)
  9.   VALUES (1, 100, 101, TIMESTAMP '2023-06-04 08:35:19.5393' );
  10. INSERT INTO table_name (id, user_id, profile_id, last_profile_date_time)
  11.   VALUES (2, 100, 102, TIMESTAMP '2023-06-05 08:35:19.5393' );

Both output:

profile_id last_profile_date_time
102 2023-06-05 08:35:19.5393

PostgreSQL fiddle Oracle fiddle

huangapple
  • 本文由 发表于 2023年6月5日 17:46:10
  • 转载请务必保留本文链接：https://go.coder-hub.com/76405198.html
  • oracle
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定