带有concat的MySQL空集

Question

当我使用这个查询时，它会返回用户的ID和分隔的地址。但如果address或email_verified_at为NULL或为空，我希望返回空集。我该怎么做。

SELECT u.id, 
GROUP_CONCAT(w.address separator ';')  as address 
FROM users AS u 
LEFT JOIN user_address AS w 
ON u.id = w.user_id 
WHERE u.name = 'name' AND 
u.email_verified_at IS NOT NULL AND 
address IS NOT NULL;  

但如果address或email_verified_at为NULL或为空，我希望返回空集。我该怎么做。

| name | id | email_verified_at |
| ---- | - | ------------------- |
| test | 1 | 2022-09-14 00:53:08 |

| user_id | address |
| - | -------------- |
| 1 | www.google.com |
| 1 | www.test.com |
| 1 | www.test2.com | 

Answer 1

使用具有空SELECT值的UNIONLIMIT 1:

SELECT u.id, 
GROUP_CONCAT(w.address separator ';') AS address 
FROM users u 
LEFT JOIN user_address w 
ON u.id = w.user_id 
WHERE u.name = 'name' 
AND u.email_verified_at IS NOT NULL 
AND w.address IS NOT NULL
GROUP BY id
UNION 
SELECT 
'' AS id,
'' AS address
LIMIT 1;

在这里打闹。

结果：

id	地址