mySQL 中 Intersect的使用

[Yujia0701]

现在有个表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的话是涉及两张表的交集 , 这个该怎么处理 谢谢

[liuyann]

没看懂，你期望的结果是什么？

== 思想重于技巧 ==

[Yujia0701]

1和3
因为1 1 101 3 1 101
1 2 1 3 2 1
就是两个栏位Id 对应的值 相同 就是这个意思 谢谢

[liuyann]

select a.* 
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

== 思想重于技巧 ==

[Yujia0701]

在inner join附近报错...

[liuyann]

select *
from test a
where 1<(select count(*) from test where field_id=a.field_id and value=a.value);

== 思想重于技巧 ==

[Yujia0701]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test a
where 1 < (select count(*) from test where field_id=a. ..............

[liuyann]

select 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;

== 思想重于技巧 ==

[liuyann]

select *
from test as a
where 1<(select count(*) from test where field_id=a.field_id and value=a.value);

== 思想重于技巧 ==

[Yujia0701]

谢谢 应该 返回记录1 和 3
但是现在1 2 3 都返回了
2是不应该出现的

是不是它比较了2的第一个栏位值对了就对了?

还有点小问题