想要的效果是:根据表dbo.D_Order中的BuyMemberID和State字段,查询表dbo.D_Order且不在表 dbo.M_OrderEvaluation存在的记录数SELECT
COUNT(*)
FROM dbo.D_Order
WHERE
ID not in (
SELECT OrderID
FROM dbo.M_OrderEvaluation
Where
EvaluationType=1
And OrderID in (
SELECT ID FROM dbo.D_Order WHERE BuyMemberID='54768CA2447B40539E2086ECFDE9957B' AND State=256
)
)
求简化
COUNT(*)
FROM dbo.D_Order
WHERE
ID not in (
SELECT OrderID
FROM dbo.M_OrderEvaluation
Where
EvaluationType=1
And OrderID in (
SELECT ID FROM dbo.D_Order WHERE BuyMemberID='54768CA2447B40539E2086ECFDE9957B' AND State=256
)
)
求简化
SELECT COUNT(*) FROM dbo.D_Order
WHERE ID not in
(SELECT OrderID FROM dbo.M_OrderEvaluation Where EvaluationType=1)
and BuyMemberID='54768CA2447B40539E2086ECFDE9957B' AND State=256
SELECT COUNT(*)
FROM dbo.D_Order a
WHERE NOT EXISTS
(
SELECT OrderID b
FROM dbo.M_OrderEvaluation
WHERE a.ID = b.SELECT
AND EvaluationType=1
AND a.BuyMemberID='54768CA2447B40539E2086ECFDE9957B'
AND a.State=256
)
FROM dbo.D_Order a
WHERE NOT EXISTS
(
SELECT OrderID b
FROM dbo.M_OrderEvaluation
WHERE a.ID = b.OrderID--改了一下
AND EvaluationType=1
AND a.BuyMemberID='54768CA2447B40539E2086ECFDE9957B'
AND a.State=256
)
FROM dbo.D_Order a
WHERE NOT EXISTS
(
SELECT OrderID
FROM dbo.M_OrderEvaluation b----改了一下
WHERE a.ID = b.OrderID--改了一下
AND EvaluationType=1
AND a.BuyMemberID='54768CA2447B40539E2086ECFDE9957B'
AND a.State=256
)
select count(*) from D_Order
where OrderID not in
(
select OrderID from D_Order
where BuyMemberID='54768CA2447B40539E2086ECFDE9957B' AND State=256
)
where OrderID not in
(
select OrderID from D_Order
where BuyMemberID='54768CA2447B40539E2086ECFDE9957B' AND State=256
)
简化sql