mysql - sql 字符串无法正确

Question

我正在尝试从我的数据库获取一些数据。

看起来有点像这样

群组

groups_id、groups_name、groups_description、groups_active、groups_hash、groups_entry_date、user_id、groups_email、groups_sms

CUSTOMERS_GROUPS

customers_hash、groups_hash

客户

customers_id、customers_first_name、customers_surname、customers_telephone、customers_email、customers_telephone_active、customers_email_active、client_type、customers_hash、customers_entry_date

我想要 customer.groups_hash 和 groups.groups_name 采用串联形式。这是我的尝试...

SELECT * , GROUP_CONCAT( DISTINCT customers_groups.groups_hash
SEPARATOR '/' ) , GROUP_CONCAT( groups.groups_name
SEPARATOR '/' )
FROM customers
INNER JOIN customers_groups ON ( customers.customers_hash = customers_groups.customers_hash )
LEFT JOIN groups ON ( customers_groups.customers_hash = groups.groups_hash )
WHERE groups.groups_active ='1' GROUP BY customers.customers_entry_date

但它给了我一个零集......

Answer 1

问题出在你的where子句上。它必须是 on 子句的一部分:

SELECT * , GROUP_CONCAT( DISTINCT customers_groups.groups_hash
SEPARATOR '/' ) , GROUP_CONCAT( groups.groups_name
SEPARATOR '/' )
FROM customers
INNER JOIN customers_groups ON ( customers.customers_hash = customers_groups.customers_hash )
LEFT JOIN groups ON ( customers_groups.customers_hash = groups.groups_hash ) AND groups.groups_active ='1' 
GROUP BY customers.customers_entry_date