请教 psql 多个表 Join+ count 的问题

我 google 了快 1 个小时了，实在找不出答案。谢谢了。

SELECT t.*,
tt.tag_name,
CASE WHEN COUNT(c.tutorial_id) > 0 THEN TRUE ELSE FALSE END AS collected,
CASE WHEN COUNT(l.tutorial_id) > 0 THEN TRUE ELSE FALSE END AS liked,
COUNT(lc.tutorial_id) as likecount
FROM
tutorials t
LEFT JOIN
collections c ON t.tutorial_id = c.tutorial_id AND c.user_id = 'ec02a580-f9cd-4941-94c4-13a0c86246e3'
LEFT JOIN
likes l ON t.tutorial_id = l.tutorial_id AND l.user_id = 'ec02a580-f9cd-4941-94c4-13a0c86246e3'
LEFT JOIN
likes lc ON t.tutorial_id = lc.tutorial_id
LEFT JOIN
tutorials_tags tt on t.tutorial_id = tt.tutorial_id
WHERE
t.tutorial_id = '78e76b5b-9699-47a4-9c41-2c2f1cfb4059'
GROUP BY 1

ERROR: column "tt.tag_name" must appear in the GROUP BY clause or be used in an aggregate function

query 从表 like 里数这个 tutorial_id 有多少 like 和用户是否喜欢，从 collections 查用户是否已经收藏，从 tag 里查 tag_name. 我卡在 tag_name 不能 group by.
麻烦了！

tutorial_id

join

tag_name

left

3 replies • 2022-05-18 23:29:09 +08:00

lichao

May 13, 2022

GROUP BY 1 ？？你这么写，用意是什么？

gogogo1203

May 13, 2022

@lichao 不然怎么用 count? 这里的 by 1 或者 by t.tutorial_id 都是一样的。你是有更好的方法？

gogogo1203

May 18, 2022

找到解决方案了.
const q = `
SELECT
a.*, u.image
FROM (
SELECT
t.tutorial_id, t.username, t.version, t.date_created, t.date_updated, t.tag_name, t.slug, t.title,t.description, t.content, t.published, t.deleted, t.date_deleted,
CASE WHEN COUNT(c.tutorial_id) > 0 THEN TRUE ELSE FALSE END AS collected,
CASE WHEN COUNT(l.tutorial_id) > 0 THEN TRUE ELSE FALSE END AS liked,
c.user_id,
COUNT(l.tutorial_id) as likecount
FROM
collections c
JOIN
tutorials t ON t.tutorial_id = c.tutorial_id
LEFT JOIN
likes l on c.tutorial_id = l.tutorial_id AND l.user_id = c.user_id
LEFT JOIN
likes lc ON c.tutorial_id = lc.tutorial_id
WHERE
c.user_id = :user_id
GROUP BY c.user_id, t.tutorial_id
) a
JOIN
users u USING(user_id)
WHERE
a.user_id = :user_id
AND
a.deleted = FALSE
`