Mysql的Having子句後如何使用GROUP BY

這是獲取特定使用者帳戶的查詢，產品與帳戶相關，one-to-many因此每個產品有四個權限

所有不是的帳戶，Disable也顯示他們的權限我根據場景獲得了所有帳戶，但問題是如果一個帳戶有多個產品，那麼它顯然不止一次顯示帳戶 ID

**我在檢查權限的子句後尋找什麼GROUP BY*但a.id沒有HAVING*運氣得到語法錯誤

Error Code: 1064 right syntax to use near 'GROUP BY a.`id` LIMIT 0, 1000' at line 14

這是我的查詢

SELECT  a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
c.`clients_last_name`, a.`accounts_account_name`
,(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) 
AND `Disable` !='1'   ORDER BY a.`id`  GROUP BY a.`id`

任何幫助，將不勝感激

您可以嘗試使用外部選擇

SELECT q.*
 FROM
(
 SELECT a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
        c.`clients_last_name`, a.`accounts_account_name`,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
   FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
  INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
  INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
  INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
  WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
 HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) AND `Disable` !='1'   
) q
GROUP BY aid

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