sql - SELECT * 和 SELECT COUNT(*) 在一个查询中

Question

我的 SQL 查询看起来像这样

SELECT * 
FROM categories AS c
LEFT JOIN LATERAL (SELECT i.* 
                   FROM influencer_profiles AS i 
                   WHERE c.id = i.category_id
                   ORDER BY i.updated_at
                   LIMIT 2) AS i ON 1 = 1
INNER JOIN users AS u ON i.user_id = u.id

但我也想计算每个influencer_profile类别以显示每个类别中有多少influencer_profiles。如何使用COUNT(*)选择所有列？

SELECT * 
FROM categories AS c
LEFT JOIN LATERAL (SELECT COUNT(*) 
                   FROM influencer_profiles AS i 
                   WHERE c.id = i.category_id
                   ORDER BY i.updated_at
                   LIMIT 2) AS i ON 1 = 1
INNER JOIN users AS u ON i.user_id = u.id

此代码不起作用。

标签： sqldatabasepostgresql

Answer 1

也许你只想要一个窗口函数。我注意到您left join在一个地方使用并且inner join正在撤消它。

所以，我在想：

SELECT c.*, i.*, u.*,
       COUNT(*) OVER (PARTITION BY c.id) as category_cnt
FROM categories c LEFT JOIN LATERAL
     (SELECT i.*
      FROM influencer_profiles AS i 
      WHERE c.id = i.category_id
      ORDER BY i.updated_at
      LIMIT 2
     ) i
     ON 1=1 LEFT JOIN
     users u 
     ON i.user_id = u.id;