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

ie3xauqp  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(464)

试图让我的查询组,但它不喜欢我的组,所以我必须拿出一些其他东西。如何为每个唯一的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

谢谢

esyap4oy

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;

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

相关问题