以下是为特定用户获取产品与一对多关系中的帐户相关的帐户的查询,因此对于每个产品都有四个权限
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions
所有帐户不是禁用,并且显示他们的权限我根据情景获取了所有帐户但问题是如果一个帐户有多个产品,那么它显然显示帐户ID超过一次
All the accounts which is not Disable and also shows their permissions I got all accounts according to scenario but the problem is if one account has more than one products then it obviously shows account id more than one time
* 我在寻找 GROUP BY 在 HAVING 子句之后检查权限的 a.id * 但没有运气得到语法错误
*What i am looking for to GROUP BY the a.id after the HAVING clause which checks the permissions * but no luck getting syntax error
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`任何帮助将不胜感激
Any help would be appreciated
推荐答案您可以尝试使用外部选择
You can try to use an outer select
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更多推荐
如何在having子句Mysql之后使用GROUP BY
发布评论