mysql - 如何条件 SQL WHERE

Question

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

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

我想实现的是在category下只显示category标签，在conditions下只显示condition标签

Answer 1

使用大小写。我认为您还返回了太多列:

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 列一致。不要重载无关的列。
• GROUP BY 应匹配 SELECT 中未聚合的键/表达式。
• 使用 IN 而不是一堆 OR - 更易于维护、编写并且不易出错。
• 对字符串和日期常量使用单引号。不要将它们用作列别名。