我有两个表:
第一:table_a
id | orderid
1 123
2 456
...
第二:table_b
orderid | status | date
123 2 1380566559
123 4 1380566561
123 6 1380566563
我想从 table_a 返回 orderid 行,只有当 orderid 的状态为 2 并且在我的表中没有状态 4 和 6 时......
我尝试过但没有成功:(
SELECT DISTINCT(a.orderid)
FROM table_a AS a
INNER JOIN table_b AS b
INNER JOIN table_b AS c
INNER JOIN table_b AS d
ON a.orderid = b.orderid
WHERE b.status = 2 AND
c.status != 4 AND
d.status != 6;
SELECT a.orderid
FROM table_a AS a
INNER JOIN table_b AS b on a.orderid = b.orderid
group by a.orderid
having sum(b.status = 2) >= 1
and sum(b.status in (4,6)) = 0
SQLFiddle demo
请尝试以下查询
SELECT DISTINCT(a.orderid)
FROM table_a a, table_b b
WHERE a.orderid = b.orderid
AND b.status = 2
AND b.status != 4
AND b.status !=6
您可以使用以下链接进行验证http://sqlfiddle.com/#!2/a9b89/5/0
你能运行下面的查询来获取 oupput 吗?
select
order_id
from table_a a
inner join table_b b
on a.order_id = b.order_id
where b.status=2;