Question

求sql语句,子查询,多个值

UPDATE INVMB
SET MB100 = 'Y', MB046 IN
(SELECT AVG(TD010)
FROM PURTD
GROUP BY TD004)
FROM INVMB JOIN
PURTD ON INVMB.MB001 = PURTD.TD004
WHERE MB001 > '83-31'

这段应该怎么写,谢谢!

Answer 1

--参照下例
create table X(
a int,
b money,
)
create table Y(
c int,
d money,
)

insert X values(1,null)
insert X values(2,null)

insert Y values(1,1)
insert Y values(1,2)
insert Y values(1,7)
insert Y values(2,3)
insert Y values(2,1)
insert Y values(2,5)
insert Y values(2,2)
--创建视图
create view v1 as select * from X inner join (select c,avg(d) d from Y group by c) as Y
on X.a=Y.c
--更新
update v1 set b=d
select * from v1

Answer 2

SELECT AVG(TD010)
FROM PURTD
GROUP BY TD004
查询的结果多于一个值
INVMB.MB001 = PURTD.TD004
WHERE MB001 > '83-31'
上面用了=号,查出来肯定是一个值,不能对查询出的多值进行赋值

Answer 3

???
看不明白你要做什么?
是不是

UPDATE INVMB
SET MB100 = 'Y' from FROM INVMB where MB046 IN
(SELECT AVG(TD010)
FROM PURTD
GROUP BY TD004)
and MB001 i > '83-31'

Answer 4

你要达到的目的呢？