mysql - MySQL - 选择时使用临时列计算行总数

Question

我有一张桌子（4列），比如

CREATE TABLE IF NOT EXISTS `myTable` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `refer_id` int(12) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `groupType` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

我创建了一个简单的数据

INSERT INTO `myTable` (`name`, `refer_id`, `groupType`) VALUES
('a', 1, 1),
('b', 2, 1),
('c', 3, 1),

('d', 4, 2),
('e', 4, 2),
('f', 4, 2),

('g', 7, 2),
('h', 7, 2),

('i', 5, 3),
('k', 5, 3),
('l', 6, 3);

我有 3 个groupType。而groupType1 和 3 是相同的（不能分组）和groupType2（是相同的组refer_id）。看起来像

id  groupType   group_field total
1           1          1    1
2           1          2    1
3           1          3    1
4           2       group_4 3
7           2       group_7 2
9           3          9    1
10          3         10    1
11          3         11    1

我正在使用下面的查询来做到这一点（我认为它看起来不错）

select id,
groupType,
IF(groupType != 2, @gf:=id, @gf:=CONCAT('group_',refer_id)) as group_field,
count(*) as total

from myTable
group by group_field
order by id asc

我想通过以下方式获得上述查询（）的总行数8 rows，但这是不正确的。怎么做？谢谢（我不想全部联合）

select id,
groupType,
IF(groupType != 2, @gf:=id, @gf:=CONCAT('group_',refer_id)) as group_field,
count(*) as total,

CONCAT_WS('', 1) AS defaultValue,
SUM(defaultValue) as totalCount

from myTable
group by group_field
order by id asc

也许是这样的？

id  groupType   group_field total defaultValue totalCount
1           1          1    1     1            8
2           1          2    1     1            8
3           1          3    1     1            8
4           2       group_4 3     1            8
7           2       group_7 2     1            8
9           3          9    1     1            8
10          3         10    1     1            8
11          3         11    1     1            8

标签： mysql

Answer 1

尝试这个

CREATE TABLE IF NOT EXISTS `myTable` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `refer_id` int(12) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `groupType` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

✓

INSERT INTO `myTable` (`name`, `refer_id`, `groupType`) VALUES
('a', 1, 1),
('b', 2, 1),
('c', 3, 1),

('d', 4, 2),
('e', 4, 2),
('f', 4, 2),

('g', 7, 2),
('h', 7, 2),

('i', 5, 3),
('k', 5, 3),
('l', 6, 3);

✓

select min(id) id,
groupType,
IF(groupType != 2, @gf:=id, @gf:=CONCAT('group_',refer_id)) as group_field,
count(*)  as total,
count(*) over () as cnt
from myTable
group by groupType, group_field 
order by id asc

编号 | 组类型 | 组字段 | 总计| cnt
-: | --------: | :------------ | ----: | --:
 1 | 1 | 1 | 1 | 8
 2 | 1 | 2 | 1 | 8
 3 | 1 | 3 | 1 | 8
 4 | 2 | group_4 | 3 | 8
 7 | 2 | group_7 | 2 | 8
 9 | 3 | 9 | 1 | 8
10 | 3 | 10 | 1 | 8
11 | 3 | 11 | 1 | 8

db<>在这里摆弄

对于 MySQl 5.5+

select min(id) id,
groupType,
IF(groupType != 2, @gf:=id, @gf:=CONCAT('group_',refer_id)) as group_field,
count(*)  as total,
(select count(distinct IF(groupType != 2, @gf:=id, @gf:=CONCAT('group_',refer_id))) from myTable) cnt
from myTable
group by groupType, group_field 
order by id asc

编号 | 组类型 | 组字段 | 总计| cnt
-: | --------: | :------------ | ----: | --:
 1 | 1 | 1 | 1 | 8
 2 | 1 | 2 | 1 | 8
 3 | 1 | 3 | 1 | 8
 4 | 2 | group_4 | 3 | 8
 7 | 2 | group_7 | 2 | 8
 9 | 3 | 9 | 1 | 8
10 | 3 | 10 | 1 | 8
11 | 3 | 11 | 1 | 8

db<>在这里摆弄