代码之家 › 专栏 › 技术社区 › Joh

需要mysql重复行问题的帮助吗

mysql

Joh · 技术社区 · 14 年前

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

在tblidas\u主题中,我有一个多主题的想法。

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

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

我希望清单上只有一个想法。

1 回复 | 直到 14 年前

Bill Karwin 14 年前

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

使用外部联接联接到themes表,并将theme_id=3条件添加到联接表达式中。因此,如果找到主题id为3的主题,orderField将为1。如果找不到主题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

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前