目录

数据库报错：column 'x' must appear in the GROUP BY clause or be used in an aggregate function

KKKPSJKEY 收录于 Back

2021-08-23 约 1420 字预计阅读 3 分钟次阅读

/sql-group-by-error/featured-image.jpg

目录

分析

用了聚集函数前面要select的列必须出现在聚集函数或者groupby里，不然它不知道怎么分配查询大于平均WEIGHT的零件，列出他们的供应商代码（SNO），零件代码（PNO），工程代码（JNO），供应数量（QTY）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


insert into P(Pno,Pname,color,weight)
values
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);

insert into SPJ(Sno,Pno,Jno,QTY)
values
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);

1）查询大于平均WEIGHT的零件，列出他们的供应商代码（SNO），零件代码（PNO），工程代码（JNO），供应数量（QTY）

1
2


select sno,spj.pno,jno,qty from spj,p where spj.pno=p.pno and weight>(select avg(weight) from p)
select sno,pno,jno,qty from spj where pno in(select pno from p  where weight>(select avg(weight) from p))

错误

1

select sno,spj.pno,jno,qty from spj,p where spj.pno=p.pno  having weight>(select avg(weight) from p)

错 having要和group by 一起用