mysql - SQL 是有效的，但是有什么方法可以使它更小(包括 sqlfiddle)

Question

我有这个查询，它正在工作，我得到了我需要的东西

SQL 简述(下面提供的 sqlfiddle 中提供了更多示例)

# Case 1: Return those who do have active subscription (subscriptions and access_rights is for debugging only)
SELECT a.*,
  (GROUP_CONCAT(DISTINCT CONCAT (s.subscription_id,' => ',s.is_active) SEPARATOR ', ')) AS `subscriptions`,
  (GROUP_CONCAT(DISTINCT CONCAT (ar.access_right_id,' => ',ar.is_active) SEPARATOR ', ')) AS `access_rights`
FROM account a
  LEFT JOIN access_right ar USING (account_id)
  LEFT JOIN subscription s USING (account_id)
  LEFT JOIN access_right active_ar ON active_ar.account_id = a.account_id AND active_ar.is_active = 1
  LEFT JOIN access_right inactive_ar ON inactive_ar.account_id = a.account_id AND inactive_ar.is_active = 0
  LEFT JOIN subscription active_su ON active_su.account_id = a.account_id AND active_su.is_active = 1
  LEFT JOIN subscription inactive_su ON inactive_su.account_id = a.account_id AND inactive_su.is_active = 0
  WHERE (active_su.account_id IS NOT NULL)
  GROUP BY account_id;

# Case 2: Return those who do have non-active subscription (active needs to be excluded, nulls must be shown)
SELECT a.*,
  (GROUP_CONCAT(DISTINCT CONCAT (s.subscription_id,' => ',s.is_active) SEPARATOR ', ')) AS `subscriptions`,
  (GROUP_CONCAT(DISTINCT CONCAT (ar.access_right_id,' => ',ar.is_active) SEPARATOR ', ')) AS `access_rights`
FROM account a
  LEFT JOIN access_right ar USING (account_id)
  LEFT JOIN subscription s USING (account_id)
  LEFT JOIN access_right active_ar ON active_ar.account_id = a.account_id AND active_ar.is_active = 1
  LEFT JOIN access_right inactive_ar ON inactive_ar.account_id = a.account_id AND inactive_ar.is_active = 0
  LEFT JOIN subscription active_su ON active_su.account_id = a.account_id AND active_su.is_active = 1
  LEFT JOIN subscription inactive_su ON inactive_su.account_id = a.account_id AND inactive_su.is_active = 0
  WHERE (active_su.account_id IS NULL)
  GROUP BY account_id;

此外，类似的条件将应用于 access_right 表，并且还会有两种条件的组合

有什么办法可以缩短它吗？

http://sqlfiddle.com/#!9/14b347/15

Answer 1

如果您只需要他们是否有事件标志，则无需多次加入

# Case 1:
select a.*
from account a
LEFT JOIN subscription s on a.account_id = s.account_id
where s.is_active = 1;

# Case 2:
select a.*
from account a
where a.account_id not in 
    (select s.account_id from subscription s where s.is_active = 1);