mysql - 需要帮助解决 mysql 重复行问题

Question

我的查询是这样的

SELECT C.id, C.title, C.short_description,C.rating,C.image,C.duration,C.difficulty ,  
0 AS active , if(G.theme_id = 3,1,0) as orderField
FROM tblIdeas C
INNER JOIN tblIdeas_themes G on (G.Idea_id = C.id )
AND C.category_id = 2  and C.published = 1 and C.DELETED=0 and C.id != 4 
ORDER BY active DESC , orderField DESC ,title ASC

---

 tblideas ------ id,description , etc
    tblideas_themes    -------- idea_id , theme_id
    tblthemes -------------id , theme_name

在 tblideas_themes 中，我有多个主题的想法。

我希望如果 idea 属于特定主题，则 order 字段应为 1，否则 orderfield 应为 0

问题是我得到了重复的行 e,g

Idea 1 ---------- with orderField 1 --as it was in that theme
Idea 1 -----------with order field 0 as ---it was also in the other theme as well

我希望列表中应该只有一个想法。

如果这个想法属于多个主题，那么我想得到属于该主题的那一行，其中 orderfield =1 。但是如果 idea 不属于那个主题，那么我想得到 orderfield = 0 的任何其他行

Answer 1

您可以使用 GROUP BY 将每个想法的行减少到一行。

使用 OUTER JOIN 连接到 themes 表，并在连接表达式中添加 theme_id=3 条件。因此，如果找到 theme_id=3 的主题，则 orderField 将为 1。如果没有找到 theme_id=3 的主题，则由于外连接，orderField 将为 null，并且 orderField 将默认为 0。

SELECT C.id, C.title, C.short_description, C.rating, C.image, C.duration, 
    C.difficulty, 0 AS active, IF(G.theme_id = 3,1,0) as orderField
FROM tblIdeas C
LEFT OUTER JOIN tblIdeas_themes G ON (G.Idea_id = C.id AND G.theme_id = 3)
WHERE C.category_id = 2  and C.published = 1 and C.DELETED=0 and C.id != 4 
GROUP BY C.id
ORDER BY active DESC , orderField DESC ,title ASC