在MySQL中,我想有一个额外的列来显示特定列的值之和.但是,我想求和的数字来自子查询,并且没有存储在单独的表中,如下所示:
(SELECT a.ID, MAX(a.COUNT_ID) AS MAX_COUNT FROM
(SELECT ID, COUNT(*) AS COUNT_ID
FROM my_table
GROUP BY COL1, COL2) a
GROUP BY COL1, COL2) b
这将输出类似:
ID MAX_COUNT
ABC 1
DEF 2
GHI 3
现在,我想要一个额外的列来显示MAX_COUNT的总和,如下所示(在所有行中都重复):
ID MAX_COUNT SUM_MAX_COUNT
ABC 1 6
DEF 2 6
GHI 3 6
实际目标实际上是显示MAX_COUNT占MAX_COUNT总数的百分比,即1 / 6、2 / 6和3/6.
我该怎么做呢?我已经尝试过进行CROSS JOIN,但是它不起作用:
SELECT * FROM
((SELECT a.ID, MAX(a.COUNT_ID) AS MAX_COUNT FROM
(SELECT ID, COUNT(*) AS COUNT_ID
FROM my_table
GROUP BY COL1, COL2) a
GROUP BY COL1, COL2) b
CROSS JOIN (SELECT SUM(b.MAX_COUNT)) AS c
错误:未知表’b’
例
表格示例:
CREATE TABLE TABLE1 (
COL1 varchar(255),
COL2 varchar(255),
DAY int,
HOUR int);
INSERT INTO TABLE1 VALUES
('X','Y',1,12),
('X','Y',1,13),
('X','Y',1,13),
('A','B',2,19),
('X','B',3,13),
('X','B',3,13);
现在,对于COL1和COL2的每种组合,我想要每个小时中此表中的行数:
SELECT COL1, COL2, HOUR, COUNT(*) AS COUNT_LINES
FROM TABLE1
GROUP BY DAY, HOUR, COL1, COL2;
输出以下内容:
COL1 COL2 HOUR COUNT_LINES
X Y 12 1
X Y 13 2
A B 19 1
X B 13 2
现在我想要,对于COL1和COL2的每个组合,最大为COUNT_LINES,所以我在子查询中使用上面的查询:
SELECT a.COL1, a.COL2, MAX(a.COUNT_LINES)
FROM
(SELECT COL1, COL2, HOUR, COUNT(*) AS COUNT_LINES
FROM TABLE1
GROUP BY DAY, HOUR, COL1, COL2) a
GROUP BY COL1, COL2;
输出以下内容:
COL1 COL2 MAX(COUNT_LINES)
A B 1
X B 2
X Y 2
现在,在最后一步中,我希望将MAX(COUNT_LINES)列的SUM放在单独的列中,如下所示:
COL1 COL2 MAX(COUNT_LINES) SUM(MAX(COUNT_LINES))
A B 1 5
X B 2 5
X Y 2 5
但这是我不知道该怎么做的部分.