问题描述:

For each two rows I have same "id"/num, and I need to get all num that their state is 0 in 2 row.

For example like in my table the result will be:

num : 2, 3

because for num 2 the state for type a is 0 and the state for type b is 0 also. But num 1 have 1 in type b so it is not good.

How I do this with SQL query or with JSON data or array in PHP? My table is:

id | num | type |state

1 | 1 | a | 0

2 | 1 | b | 1

3 | 2 | a | 0

4 | 2 | b | 0

5 | 3 | a | 0

6 | 3 | b | 0

网友答案:

You can give this a try:

SELECT
  num
FROM myTable
GROUP BY num
HAVING sum(state) = 0

Here is the sqlfiddle.

网友答案:

A self join without a correlated subquery:

select t1.num
from tablexxx t1
join tablexxx t2
on t2.num=t1.num
and t2.state=0 and t1.state=0

Add an index on num (perhaps).

相关阅读:
Top