现在有个表Table test :
结构 id (int)
field_id(int)
value(int)
就是这样 id 是一个号码 , 但不是主键
field_id是它的栏位号码,value是栏位的值现在有这些数据
1 1 101
1 2 1
2 1 101
2 2 2
3 1 101
3 2 1
4 1 102
4 2 3 现在要做交查询 就是 field_id = 1 , value = 101 和 field_id = 2 , value = 1
如果用Intersect的话很简单
(Select id from test where field_id = 1 And value = 101) intersect (Select id from test where field_id = 2 And value = 1)
但是Mysql不能用intersect , 而一般说的在mysql中用inner union的话是涉及两张表的交集 , 这个该怎么处理 谢谢
结构 id (int)
field_id(int)
value(int)
就是这样 id 是一个号码 , 但不是主键
field_id是它的栏位号码,value是栏位的值现在有这些数据
1 1 101
1 2 1
2 1 101
2 2 2
3 1 101
3 2 1
4 1 102
4 2 3 现在要做交查询 就是 field_id = 1 , value = 101 和 field_id = 2 , value = 1
如果用Intersect的话很简单
(Select id from test where field_id = 1 And value = 101) intersect (Select id from test where field_id = 2 And value = 1)
但是Mysql不能用intersect , 而一般说的在mysql中用inner union的话是涉及两张表的交集 , 这个该怎么处理 谢谢
from test a inner join
(select field_id,value
from test
group by field_id,value
having count(*)>1 ) b
on a.field_id=b.field_id and a.value=b.value
from test a
where 1<(select count(*) from test where field_id=a.field_id and value=a.value);
where 1 < (select count(*) from test where field_id=a. ..............
from test as a inner join
(select field_id,value
from test
group by field_id,value
having count(*)>1 )as b
on a.field_id=b.field_id and a.value=b.value;
from test as a
where 1<(select count(*) from test where field_id=a.field_id and value=a.value);