sql中delete嵌套的问题

delete from posts_99 where authorid in (select authorid from posts_99 group by authorid having count(message)<3)

中select authorid from posts_99 group by authorid having count(message)<3

查询条件中没有count(message)，所以having条件不成立。

select authorid，count(message) from posts_99 group by authorid having count(message)<3 这样的查询才成立，但在delete语句中不适用。

用两步来解决

1。将查询结果放到一个临时表中

select authorid，count(message) SL into #a from posts_99 group by authorid having count(message)<3

2.关联临时表做delete

delete from posts_99 where authorid in (select authorid from #a)

两表有关联的情况下删除不是这么用的。给你个例子你理解一下吧：

CREATE CURSOR MyProducts (ProdID I , OurPrice Y NULL)

INSERT INTO MyProducts VALUES (1,1.10) &&matches but is not marked for deletion

INSERT INTO MyProducts VALUES (2,2.20) &&matches and is marked

INSERT INTO MyProducts VALUES (3,3.30) &&matches and is marked

CREATE CURSOR MSRPList (ProdID I , ProdCategory I Null, MSRP Y, Discontinued L)

INSERT INTO MSRPList VALUES (1, 9, 1.00, .f.)

INSERT INTO MSRPList VALUES (2, 8, 2.00, .t.)

INSERT INTO MSRPList VALUES (3, 7, 3.00, .t.)

DELETE MyProducts FROM MSRPList WHERE MSRPList.ProdID = MyProducts.ProdID AND MSRPList.discontinued = .t.

SELECT * from MyProducts

所以你的这个用法应该是

delete a from b where a.id=b.id

---