Count(*) 作為角色計數，按角色計數 = 1 過濾

我如何過濾掉只有 rolecount = 1 的行？WHERE rolecount = 1 給出錯誤。實在想不通怎麼做。

SELECT 
u.uid AS uid,
u.name AS name, 
u.picture AS picture,
n.field_full_name_value AS field_full_name_value,
f.field_function_value AS field_function_value,
pi.field_phone_intern__value AS field_phone_intern__value,
ur.rid AS rid,
COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u 
LEFT OUTER JOIN `dev_drupal_project`.users_roles ur ON u.uid = ur.uid 
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_full_name n ON u.uid = n.entity_id 
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_function f ON u.uid = f.entity_id 
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_phone_intern_ pi ON u.uid = pi.entity_id 
GROUP BY u.uid;

GROUP BY執行後的過濾器可以與HAVING子句一起應用，它相當於一個WHERE條件，但適用於執行的最新階段。因此，假設查詢的其餘部分是正確的：

SELECT 
 u.uid AS uid,
 u.name AS name, 
 u.picture AS picture,
 n.field_full_name_value AS field_full_name_value,
 f.field_function_value AS field_function_value,
 pi.field_phone_intern__value AS field_phone_intern__value,
 ur.rid AS rid,
 COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u 
LEFT OUTER JOIN `dev_drupal_project`.users_roles ur 
 ON u.uid = ur.uid 
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_full_name n 
 ON u.uid = n.entity_id 
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_function f 
 ON u.uid = f.entity_id 
LEFT OUTER JOIN `dev_drupal_project`.field_data_field_phone_intern_ pi 
 ON u.uid = pi.entity_id 
GROUP BY u.uid
HAVING rolecount = 1;

您可以使用別名（我推薦它）或count(*)表達式進行過濾，但不能在WHERE子句中。

引用自：https://dba.stackexchange.com/questions/74410