mysql - 需要帮助与MySQL重复行问题

我的查询是这样

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中，我对多个主题有一个想法。

我希望如果想法属于特定主题，则订单字段应为1，如果不是，则订单字段应为0

问题是我得到重复的行，例如

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属于该主题的那一行。但是如果想法不属于那个主题，那么我想得到orderfield = 0的任何其他行

最佳答案

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

使用OUTER JOIN连接到主题表，然后将theme_id = 3条件添加到连接表达式。因此，如果找到了theme_id = 3的主题，则orderField将为1。如果未找到theme_id = 3的主题，则由于外部联接，它将为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