如何选择一个新的列包含选定的表？

cgfeq70w 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(236)

如何选择新列包含选定表？我试图这样做，但它返回的错误是（#1242-subquery返回超过1行）

SELECT 
(SELECT SUM(A+AA+AB+AC+AD+AE+AF+AG+AH+AI+AJ+AK+AL+AM+AN+AO+
        B+C+CL+D+E+F+G+H+I+J+K+L+LK+M+N+O+P+Q+R+S+T+U+V+W+X+Y+Z) 
        AS SELESAI
        FROM statistik WHERE stastus = '1' GROUP BY kategori ),

SUM(A+AA+AB+AC+AD+AE+AF+AG+AH+AI+AJ+AK+AL+AM+AN+AO+
    B+C+CL+D+E+F+G+H+I+J+K+L+LK+M+N+O+P+Q+R+S+T+U+V+W+X+Y+Z) 
    AS Jumlah,kategori, 
SUM(A) AS A, 
SUM(AA) AS AA, 
SUM(AB) AS AB, 
SUM(AC) AS AC, 
SUM(AD) AS AD, 
SUM(AE) AS AE, 
SUM(AF) AS AF, 
SUM(AG) AS AG, 
SUM(AH) AS AH, 
SUM(AI) AS AI, 
SUM(AJ) AS AJ, 
SUM(AK) AS AK, 
SUM(AL) AS AL, 
SUM(AM) AS AM, 
SUM(AN) AS AN, 
SUM(AO) AS AO, 
SUM(B) AS B, 
SUM(C) AS C, 
SUM(CL) AS CL, 
SUM(D) AS D, 
SUM(E) AS E, 
SUM(F) AS F, 
SUM(G) AS G, 
SUM(H) AS H, 
SUM(I) AS I, 
SUM(J) AS J, 
SUM(K) AS K, 
SUM(L) AS L, 
SUM(LK) AS LK,
SUM(M) AS M, 
SUM(N) AS N, 
SUM(O) AS O, 
SUM(P) AS P, 
SUM(Q) AS Q, 
SUM(R) AS R, 
SUM(S) AS S, 
SUM(T) AS T, 
SUM(U) AS U, 
SUM(V) AS V, 
SUM(W) AS W, 
SUM(X) AS X, 
SUM(Y) AS Y, 
SUM(Z) AS Z 
FROM statistik WHERE stastus = '4' GROUP BY kategori ;

这是我的数据库：
--表的表结构 statistik ```
CREATE TABLE statistik (
id int(100) NOT NULL,
kategori varchar(100) DEFAULT NULL,
stastus int(100) DEFAULT NULL,
tarikhLaporan date DEFAULT NULL,
A int(2) NOT NULL,
AA int(2) NOT NULL,
AB int(2) NOT NULL,
AC int(2) NOT NULL,
AD int(2) NOT NULL,
AE int(2) NOT NULL,
AF int(2) NOT NULL,
AG int(2) NOT NULL,
AH int(2) NOT NULL,
AI int(2) NOT NULL,
AJ int(2) NOT NULL,
AK int(2) NOT NULL,
AL int(2) NOT NULL,
AM int(2) NOT NULL,
AN int(2) NOT NULL,
AO int(2) NOT NULL,
B int(2) NOT NULL,
C int(2) NOT NULL,
CL int(2) NOT NULL,
D int(2) NOT NULL,
E int(2) NOT NULL,
F int(2) NOT NULL,
G int(2) NOT NULL,
H int(2) NOT NULL,
I int(2) NOT NULL,
J int(2) NOT NULL,
K int(2) NOT NULL,
L int(2) NOT NULL,
LK int(2) NOT NULL,
M int(2) NOT NULL,
N int(2) NOT NULL,
O int(2) NOT NULL,
P int(2) NOT NULL,
Q int(2) NOT NULL,
R int(2) NOT NULL,
S int(2) NOT NULL,
T int(2) NOT NULL,
U int(2) NOT NULL,
V int(2) NOT NULL,
W int(2) NOT NULL,
X int(2) NOT NULL,
Y int(2) NOT NULL,
Z int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--正在转储表的数据 `statistik` ```
INSERT INTO `statistik` (`id`, `kategori`, `stastus`, `tarikhLaporan`, `A`, `AA`, `AB`, `AC`, `AD`, `AE`, `AF`, `AG`, `AH`, `AI`, `AJ`, `AK`, `AL`, `AM`, `AN`, `AO`, `B`, `C`, `CL`, `D`, `E`, `F`, `G`, `H`, `I`, `J`, `K`, `L`, `LK`, `M`, `N`, `O`, `P`, `Q`, `R`, `S`, `T`, `U`, `V`, `W`, `X`, `Y`, `Z`) VALUES
(8, 'a', 4, '2018-08-17', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(9, 'b', 4, '2018-08-17', 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(10, 'b', 1, '2018-08-17', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(11, 'b', 1, '2018-08-17', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(12, 'b', 1, '2018-08-17', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(13, 'b', 1, '2018-08-17', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(14, 'b', 1, '2018-08-17', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(15, 'b', 1, '2018-08-17', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(16, 'bc', 4, '2018-08-17', 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(17, 'c', 1, '2018-08-23', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(18, 'c', 1, '2018-08-23', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(19, 'c', 1, '2018-08-23', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(20, 'z', 4, '2018-08-11', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(21, 'z', 1, '2018-08-11', 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(22, '', 1, '0000-00-00', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
(23, '', 1, '0000-00-00', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

--转储表的索引
--表的索引 statistik ```
ALTER TABLE statistik
ADD PRIMARY KEY (id),
ADD KEY stastus (stastus);

--转储表的自动增量
--表的自动增量 `statistik` ```
ALTER TABLE `statistik`
  MODIFY `id` int(100) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

sql mysql innodb

来源：https://stackoverflow.com/questions/51814425/how-to-select-a-new-column-contains-selected-table

2条答案

按热度按时间

s4n0splo1#

顶部嵌套的select只能返回一个值。它根本不应该是嵌套查询。最好给你的每一笔钱加上一个案例陈述

(SUM(case when stastus = '1' then A+AA+AB+AC+AD+AE+AF+AG+AH+AI+AJ+AK+AL+AM+AN+AO+
    B+C+CL+D+E+F+G+H+I+J+K+L+LK+M+N+O+P+Q+R+S+T+U+V+W+X+Y+Z else 0 end) 
    AS SELESAI,

让其他人都用这个格式。

sum(case when stastus='4' then A else 0 end) as A

否则，您可以使用您连接或联合在一起的多个查询来重构。

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

jv2fixgn2#

尽管我更喜欢@tomc的方法，但是您可以使用相关子查询而不是其他子查询来做您想做的事情 group by :

SELECT (SELECT SUM(A+AA+AB+AC+AD+AE+AF+AG+AH+AI+AJ+AK+AL+AM+AN+AO+
        B+C+CL+D+E+F+G+H+I+J+K+L+LK+M+N+O+P+Q+R+S+T+U+V+W+X+Y+Z) 
                  ) AS SELESAI
        FROM statistik s2
        WHERE s2.status = 1 
              s2.kategori = s.kategori
       ),
       . . .
FROM statistik s
. . .

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

我来回答

如何选择一个新的列包含选定的表？

2条答案

相关问题

热门标签

最新问答