mysql - 从一组中选择值

如何使SQL Fiddle从GROUP by返回的元素子集中获得结果？
桌子

CREATE TABLE IF NOT EXISTS `accounts` (
  `master_id` int(3) unsigned NOT NULL,
  `child_id` int(3) unsigned NOT NULL,
  `name` varchar(200) NOT NULL
);

数据

INSERT INTO `accounts` (`master_id`, `child_id`, `name`) VALUES
  ('222', '555', 'child_555'),
  ('111', '111', 'master_111'),
  ('111', '999', 'child_999'),
  ('111', '888', 'child_888'),
  ('222', '222', 'master_222'),
  ('222', '777', 'child_777'),
  ('111', '666', 'child_666');

当前查询

SELECT
master_id,
name,
count(*) as "Total Accounts"
FROM `accounts`
GROUP BY master_id ASC;

结果

+-----------+------------+-------+
| master_id |    name    | Total |
+-----------+------------+-------+
|       111 | master_111 |     4 |
|       222 | child_555  |     3 |
+-----------+------------+-------+

预期

 - Don't count child if child_id == master_id
 - Get correct name

+-----------+------------+-------+
| master_id |    name    | Total |
+-----------+------------+-------+
|       111 | master_111 |     3 |
|       222 | master_222 |     2 |
+-----------+------------+-------+

最佳答案

我认为使用一些条件逻辑进行简单的聚合是最好的方法：

SELECT master_id,
       MAX(CASE WHEN child_id = master_id THEN name END) as name,
       SUM(child_id <> master_id) as "Total Accounts"
FROM `accounts`
GROUP BY master_id ASC;

Here是这个的SQL小提琴。

关于mysql - 从一组中选择值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50484699/