已知一个供应情况表 SPJ
有属性 供应商号码 SNO
零件号码 PNO
工程项目号码 JNO
零件数量 QTY
求满足下面要求的供应商号码,该供应商供应给某个工程零件P1 的数量大于这个工程被供应的零件P1 的平均数量。这个问题的SQL查询语句怎么写啊?
有属性 供应商号码 SNO
零件号码 PNO
工程项目号码 JNO
零件数量 QTY
求满足下面要求的供应商号码,该供应商供应给某个工程零件P1 的数量大于这个工程被供应的零件P1 的平均数量。这个问题的SQL查询语句怎么写啊?
调试欢乐多
SELECT SNO FROM SPJ WHERE QTY>(
SELECT AVG(QTY) FROM SPJ)
CREATE TABLE SPJ(SNO NVARCHAR(8),PNO NVARCHAR(8),JNO NVARCHAR(8),QTY NVARCHAR(8)
)
INSERT SPJ
SELECT N'S1',N'P1',N'J1',N'200' UNION ALL
SELECT N'S1',N'P1',N'J3',N'100' UNION ALL
SELECT N'S1',N'P1',N'J4',N'700' UNION ALL
SELECT N'S1',N'P2',N'J2',N'100' UNION ALL
SELECT N'S2',N'P1',N'J1',N'400' UNION ALL
SELECT N'S2',N'P3',N'J2',N'200' UNION ALL
SELECT N'S2',N'P3',N'J4',N'500'
GO
SELECT SNO
FROM SPJ
WHERE QTY >(
SELECT AVG(CAST(QTY AS INT))
FROM SPJ
)
/*
SNO
--------
S1
S2
S2(所影响的行数为 3 行)
*/
SQL codeSELECT SNOFROM SPJ WHERE QTY>
(SELECT AVG(QTY)FROM SPJ WHERE JNO='P1' AND PNO='P1' )
SELECT SNOFROM SPJ WHERE QTY>
(SELECT AVG(QTY)FROM SPJ WHERE JNO='P1' AND PNO='P1' )
SELECT SNO FROM SPJ WHERE QTY>
(SELECT AVG(QTY)FROM SPJ WHERE JNO='P1' AND PNO='P1')哎~~~~~~~~~~~~不好意思啊
CREATE TABLE SPJ(SNO NVARCHAR(8),PNO NVARCHAR(8),JNO NVARCHAR(8),QTY int
)
INSERT SPJ
SELECT N'S1',N'P1',N'J1',200 UNION ALL
SELECT N'S1',N'P1',N'J3',100 UNION ALL
SELECT N'S1',N'P1',N'J4',700 UNION ALL
SELECT N'S1',N'P2',N'J2',100 UNION ALL
SELECT N'S2',N'P1',N'J1',400 UNION ALL
SELECT N'S2',N'P3',N'J2',200 UNION ALL
SELECT N'S2',N'P3',N'J4',500
GO select * from spj a
where exists
(select 1 from spj b where a.pno = pno and a.qty > (select avg(qty) from spj where b.pno = pno))
------------------------------------------------------
SNO PNO JNO QTY
-------- -------- -------- -----------
S1 P1 J4 700
S2 P1 J1 400
S2 P3 J4 500(3 行受影响)
格式应该是同ztgis一样的
你的查找不到呀
JNO = 'P1',是错了吧,JNO 是工程项目号,P1是零件号
FROM SPJ as SP
WHERE PNO = 'P1'
and QTY>(
SELECT AVG(QTY)
FROM SPJ as JP
WHERE PNO='P1'
and SP.JNO = JP.JNO
group by JNO
)
having qty > (select avg(qty) from Spj where pno = a.pno group by jno)