php - SELECT 语句中的 GROUP_CONCAT

Question

我对带有 GROUP_CONCAT 的 SQL 语句有一个小问题。

我有这个声明:

SELECT ni.*, GROUP_CONCAT(newsletter_item_receivers.value) AS receivers, nf.*, GROUP_CONCAT(nm.mailgroup_name) AS mailgroups
FROM newsletter_items ni
INNER JOIN newsletter_fields nf ON (nf.field_letter_uid = ni.letter_id)                                                          
LEFT JOIN newsletter_item_receivers ON (newsletter_item_receivers.letter_id = ni.letter_id)
INNER JOIN newsletter_mailgroups nm ON (FIND_IN_SET(nm.mailgroup_id, newsletter_item_receivers.value))
WHERE nf.field_name = 'letter_headline' AND ni.template = '". $template ."' 
GROUP BY ni.letter_id

我做错了什么，因为我的输出一团糟。

我有 4 个表:

具有这些栏的 newsletter_items:

letter_id (int)
letter_date (int)
template (varchar)
status (int)

和 newsletter_item_receivers具有这些列:

rid (int)
letter_id (int)
value (int)

newsletter_mailgroups

mailgroup_id (int)
mailgroup_name (varchar)

newsletter_fields

field_uid (int)
field_name (varchar)
field_content (text)
field_letter_uid (int)

我几乎得到了我想要的输出，但我的邮件组显示错误。我希望它显示为

A组，C组不过是显示的样子

A组，C组，A组，C组，C组当我的 newsletter_item_receivers 表包含这个时:

rid | letter_id | value
1   | 1         | 2
2   | 1         | 3
3   | 2         | 1

newsletter_item_receivers.letter_id 是指 newsletter_items.letter_id 和newsletter_item_receivers.value 指的是newsletter_mailgroups.mailgroup_id

有没有人可以看到我做错了什么？

Answer 1

试试这个:-

group_concat( DISTINCT newsletter_item_receivers.value 
              ORDER BY newsletter_item_receivers.value)

和

group_concat( DISTINCT nm.mailgroup_name 
              ORDER BY nm.mailgroup_name)