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_profile。如何在选择所有列的同时使用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

此代码不起作用。

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;