从分组的集合中选择值

ojsjcaue 于 2021-06-20 发布在 Mysql

关注(0)|答案(6)|浏览(344)

如何使这个sql fiddle从groupby返回的元素子集中获取结果？
table

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 |
+-----------+------------+-------+

sql mysql

来源：https://stackoverflow.com/questions/50484699/select-values-from-a-grouped-by-set

6条答案

按热度按时间

cl25kdpy1#

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

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;

这里是sql的小提琴。

赞(0）回复(0）举报 2021-06-20

deikduxw2#

这里有一个没有自连接的解决方案

SELECT 
  `master_id`, 
  max(case when `master_id` = `child_id` then `name` end) n,
  count(case when `master_id` != `child_id` then 1 end) as "Total Accounts"
FROM `accounts` a1
GROUP BY master_id ASC;

演示

赞(0）回复(0）举报 2021-06-20

drkbr07n3#

SELECT 
b.master_id, 
a.name,
count(*) as "Total Accounts"
FROM accounts b, accounts a
where b.master_id<>b.child_id
and a.child_id = b.master_id
GROUP BY b.master_id ASC;

或者正如哈立德·朱奈德先生所说：

SELECT 
  b.master_id,
  a.name,
  COUNT(*) AS "Total Accounts" 
FROM
  accounts b 
  JOIN accounts a 
    ON b.master_id <> b.child_id 
    AND a.child_id = b.master_id 
GROUP BY b.master_id ASC

结果：

master_id   name        Total Accounts
111         master_111  3
222         master_222  2

赞(0）回复(0）举报 2021-06-20

1hdlvixo4#

select master_id, name, 
       sum(master_id != child_id) as Total
from `accounts`
group by master_id 
order by master_id ASC;

sqlfiddle演示

赞(0）回复(0）举报 2021-06-20

mznpcxlj5#

使用 case 要进行条件聚合的表达式：

select b.master_id, a.name, 
       sum(case when b.master_id <> b.child_id then 1 else 0 end) as "Total Accounts"
from `accounts` a
inner join `accounts` b on a.child_id = b.master_id
group by b.master_id 
order by b.master_id ASC;

然而， WHERE 条款也应该做这项工作。

select master_id, name, 
       count(*) as "Total Accounts"
from `accounts`
where master_id != child_id
group by master_id 
order by master_id ASC;

赞(0）回复(0）举报 2021-06-20

brvekthn6#

试试这个。。。

SELECT master_id, name, count(*) as "Total Accounts" 
FROM accounts where master_id != child_id
GROUP BY master_id ASC;

赞(0）回复(0）举报 2021-06-20

我来回答

从分组的集合中选择值

6条答案

相关问题

热门标签

最新问答