sql - 在一个查询中分组两次

Question

我使用下面的代码，但没有返回我所期望的，

表关系，
每个 gallery 包含多个 media，每个媒体包含多个 media_user_action。我想计算每个 gallery 有多少 media_user_action 并按此计数排序

rows: [
 {
   "id": 1
 },
 {
   "id": 2
 }
]

并且此查询将返回类似

的重复图库行

rows: [
 {
   "id": 1
 },
 {
   "id": 1
 },
 {
   "id": 2
 }
 ...
]

我认为是因为在 LEFT JOIN 子查询中选择 media_user_action 行仅按 media_id 分组，还需要按 gallery_id 分组吗？

SELECT
 g.*
 FROM gallery g 
   LEFT JOIN gallery_media gm ON gm.gallery_id = g.id
   LEFT JOIN (
     SELECT 
       media_id, 
       COUNT(*) as mua_count
       FROM media_user_action
       WHERE type = 0
       GROUP BY media_id
   ) mua ON mua.media_id = gm.media_id
   ORDER BY g.id desc NULLS LAST OFFSET $1 LIMIT $2

表格

gallery
id |
1  |
2  |

gallery_media
id | gallery_id fk gallery.id | media_id fk media.id
1  | 1                        | 1
2  | 1                        | 2
3  | 2                        | 3
....

media_user_action
id | media_id fk media.id | user_id | type
1  | 1                    | 1       | 0
2  | 1                    | 2       | 0
3  | 3                    | 1       | 0
...

media
id | 
1  |
2  |
3  |

更新
我需要选择更多其他表，这是函数中的一部分 https://jsfiddle.net/g8wtqqqa/1/当用户输入选项然后构建查询时。

所以我更正了我的问题我需要找到一种方法，如果用户想通过它计算 media_user_action 顺序，我想知道如何将它们放在子查询中可能不会更改任何其他代码

基于下面@trincot 的回答，我更新了代码，只在顶部添加 media_count 稍微改变一下，然后将它们放在子查询中。是我想要的，
现在它们按 gallery.id 分组，但排序 media_count desc 和 asc 是相同的结果不起作用我找不到为什么？

SELECT
 g.*,
 row_to_json(gi.*) as gallery_information,
 row_to_json(gl.*) as gallery_limit,
 media_count
 FROM gallery g 
 LEFT JOIN gallery_information gi ON gi.gallery_id = g.id
 LEFT JOIN gallery_limit gl ON gl.gallery_id = g.id
 LEFT JOIN "user" u ON u.id = g.create_by_user_id
 LEFT JOIN category_gallery cg ON cg.gallery_id = g.id
 LEFT JOIN category c ON c.id = cg.category_id 
   LEFT JOIN (
     SELECT 
       gm.gallery_id,
       COUNT(DISTINCT mua.media_id) media_count
       FROM gallery_media gm
       INNER JOIN media_user_action mua
         ON mua.media_id = gm.media_id AND mua.type = 0
       GROUP BY gm.gallery_id
   ) gm ON gm.gallery_id = g.id
   ORDER BY gm.media_count asc NULLS LAST OFFSET $1 LIMIT $2

Answer 1

与 gallery_media 表的连接使您的结果成倍增加。计数和分组应该在您进行连接后进行。

你可以这样实现:

SELECT    g.id,
          COUNT(DISTINCT mua.media_id)
FROM      gallery g
LEFT JOIN gallery_media gm
       ON gm.gallery_id = g.id
LEFT JOIN media_user_action mua
       ON mua.media_id = gm.id AND type = 0
GROUP BY  g.id
ORDER BY  2 DESC

如果您还需要其他信息，您可以使用上面的(简化形式)作为子查询，您可以将其与您需要的任何其他信息连接起来，但不会增加行数:

SELECT    g.*
          row_to_json(gi.*) as gallery_information,
          row_to_json(gl.*) as gallery_limit,
          media_count
FROM      gallery g
LEFT JOIN (
            SELECT     gm.gallery_id,
                       COUNT(DISTINCT mua.media_id) media_count
            FROM       gallery_media gm
            INNER JOIN media_user_action mua
                    ON mua.media_id = gm.id AND type = 0
            GROUP BY   gm.gallery_id
          ) gm
       ON gm.gallery_id = g.id
LEFT JOIN gallery_information gi ON gi.gallery_id = g.id
LEFT JOIN gallery_limit gl ON gl.gallery_id = g.id
ORDER BY  media_count DESC NULLS LAST 
OFFSET    $1
LIMIT     $2

以上假定 gallery_id 在表 gallery_information 和 gallery_limit 中是唯一的。