对于with语句中的三列中的两列，sql为distinct

ie3xauqp 于 2021-07-24 发布在 Java

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

试图让我的查询组，但它不喜欢我的组，所以我必须拿出一些其他东西。如何为每个唯一的person\u id和ah\u person\u id组合获取一行，与哪一行无关。

WITH w_accnt AS
             (SELECT DISTINCT account.id AS id,
                              account.group_code AS account_number,
                              account.attribute_1 AS group_number,
                              account.source_id AS ah_person_id,
                              weekly.person_id AS person_id                              
                FROM MyAccountTable account
               INNER JOIN MyWeeklyTable weekly
                  ON weekly.account_id = account.id

                 ),

示例表

Person   AH_PERSON   ACCOUNT_num
A         1           22
B         2           23
B         2           24 
C         3           25
C         4           26

我想要的是预期的结果

Person   AH_PERSON   ACCOUNT_num
A         1           22
B         2           23
C         3           25
C         4           26

谢谢

sql plsql

来源：https://stackoverflow.com/questions/62742660/distinct-for-two-out-of-three-columns-in-with-statement

1条答案

按热度按时间

esyap4oy1#

使用聚合：

SELECT MIN(a.id) AS id, MIN(a.group_code) AS account_number,
       MIN(a.attribute_1) AS group_number,
       a.source_id AS ah_person_id,
       w.person_id AS person_id                              
FROM MyAccountTable account a JOIN
     MyWeeklyTable w
     ON w.account_id = a.id
GROUP BY a.source_id, w.person_id

或者窗口功能：

SELECT aw.*
FROM (SELECT a.id, a.group_code AS account_number, a.attribute_1 AS group_number,
             a.source_id AS ah_person_id, w.person_id AS person_id,
             ROW_NUMBER() OVER (PARTITION BY a.source_id, w.person_id ORDER BY a.id DESC) as seqnum                              
      FROM MyAccountTable account a JOIN
           MyWeeklyTable w
           ON w.account_id = a.id
      GROUP BY a.source_id
     ) aw
WHERE seqnum = 1;

这样可以更好地控制返回的值，并确保它们都来自同一行。

赞(0）回复(0）举报 2021-07-24

我来回答

对于with语句中的三列中的两列，sql为distinct

1条答案

相关问题

热门标签

最新问答