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

如何在何处设置SQL条件

group-concat where if-statement sql mysql

PWT · 技术社区 · 8 年前

我想确定何时对我的结果进行分组。我不知道如何区分“类别”和“条件”结果。

SELECT d.company_name AS 'organisation name',
       GROUP_CONCAT(DISTINCT tag.tag) AS 'category',
       GROUP_CONCAT(DISTINCT tag.tag) AS 'conditions', d.contact_describtion AS 'description', d.tel, d.tel2, d.mobile, d.email, d.email2, d.web, d.web2, d.address, d.town, d.county, d.post_code AS 'post code', IF(d.state = 1, "published", "unpublished") AS 'status',d.contact_page_notes AS 'contact history', d.last_contacted_date AS 'last contacted', d.last_updated_date AS 'last updated'
FROM jds4a_directory d INNER JOIN
     jds4a_tags_resources res
     on d.id = res.resource_id INNER JOIN
     jds4a_tags tag
     on tag.id = res.tag_id
WHERE tag.category = 'condition' OR tag.category = 'category'
GROUP BY res.resource_id

Here is my result

jds4a_tags table

我想要实现的是只在类别下显示类别标记,并且只在条件下显示条件标记

2 回复 | 直到 8 年前

Gordon Linoff 8 年前

使用 case 。您返回的列也太多了,我认为:

SELECT d.company_name AS organisation_name,
       GROUP_CONCAT(DISTINCT (CASE WHEN tag.category = 'condition' THEN tag.tag END)) AS categories,
       GROUP_CONCAT(DISTINCT (CASE WHEN tag.category = 'category' THEN tag.tag END)) AS conditions
FROM jds4a_directory d INNER JOIN
     jds4a_tags_resources res
     on d.id = res.resource_id INNER JOIN
     jds4a_tags tag
     on tag.id = res.tag_id
WHERE tag.category IN ('condition', 'category')
GROUP BY d.company_name;

备注:

这个 SELECT 列应与 GROUP BY 列。不要用无关列重载。
分组依据 应与中的未聚合键/表达式匹配 选择 。
使用 IN 而不是一堆 OR s—更易于维护、编写,并且不易出错。
字符串和日期常量使用单引号。不要将它们用作列别名。

Pradip Borde 8 年前

您可以尝试以下查询:

SELECT d.company_name AS 'organisation name',
GROUP_CONCAT(DISTINCT tag.tag) AS 'category',
GROUP_CONCAT(DISTINCT tag1.tag) AS 'conditions', d.contact_describtion AS 'description', d.tel, d.tel2, d.mobile, d.email, d.email2, d.web, d.web2, d.address, d.town, d.county, d.post_code AS 'post code', IF(d.state = 1, "published", "unpublished") AS 'status',d.contact_page_notes AS 'contact history', d.last_contacted_date AS 'last contacted', d.last_updated_date AS 'last updated'
FROM jds4a_directory d 
INNER JOIN  jds4a_tags_resources res
on d.id = res.resource_id 
LEFT JOIN jds4a_tags tag
on tag.id = res.tag_id AND tag.category = 'category'
LEFT JOIN jds4a_tags tag1
on tag1.id = res.tag_id AND tag1.category = 'condition' 
GROUP BY res.resource_id

尚未尝试此查询。但它应该会起作用。基本上,您需要使用条件标记名再次加入jds4a\U标记表