单表多条件筛选交集

[mkcat]

这是数据表
BuyID AttributeID Value
1 1 单间出租
1 2 0
1 3 1
1 4 1000
2 1 整套出租
2 2 地址
2 3 1
2 4 1000
3 1 单间出租
3 2 9
3 3 10
3 4 1500

条件一：`AttributeID`=1 AND `Value`='单间出租'
条件二：`AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000

我改如何搜索出条件一和条件二的交集呢？
即搜索出的结果只有一条，`BuyID`=1

尝试一：SELECT * FROM jm_user_buy_attribute WHERE (`AttributeID`=1 AND `Value`='单间出租') OR (`AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000) GROUP BY BuyID
尝试一搜索出来的结果只是满足条件一或者条件二的结果，但不能筛选出交集。

尝试二：SELECT * FROM jm_user_buy_attribute WHERE (`AttributeID`=1 AND `Value`='单间出租') OR (`AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000) GROUP BY BuyID
尝试二搜索出来的结果只是去除了相同的BuyID

尝试三：SELECT * FROM jm_user_buy_attribute as b1 left join jm_user_buy_attribute as b2 on b1.BuyID=b2.BuyID where b1.AttributeID=1 AND b1.Value='单间出租' OR b2.AttributeID=4 AND b2.Value>=1000 AND b2.Value<=2000
尝试二搜索出来的结果不正确。

[ACMAIN_CHM]

SELECT BuyID FROM WHERE `AttributeID`=1 AND `Value`='单间出租' and exists (select 1 from jm_user_buy_attribute where `AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000)

[mkcat]

使用环境： php+MYSQL5.5

[mkcat]

引用 2 楼 ACMAIN_CHM 的回复:

SELECT BuyID FROM WHERE `AttributeID`=1 AND `Value`='单间出租' and exists (select 1 from jm_user_buy_attribute where `AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000)

SQL 语句错误 “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 'WHERE `AttributeID`=1 AND `Value`='单间出租'and exists (select 1 from jm_use' at line 1”

[mkcat]

SELECT * FROM jm_user_buy_attribute WHERE `AttributeID`=1 AND `Value`='单间出租'and exists (select 1 from jm_user_buy_attribute where `AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000) 这样写的SQL搜索出来的不正常啊

[mkcat]

尝试一那里多写了一个GROUP BY BuyID

[wwwwb]

SELECT BuyID FROM tty a WHERE AttributeID=1 AND Value='单间出租' and exists (select 1 from tty where AttributeID=4 AND 0+Value>=1000 AND 0+Value<=2000 and a.BuyID<BuyID)

[xuzuning]

select * from (select * from jm_user_buy_attribute where AttributeID=1 AND Value='单间出租') t1, (select * from jm_user_buy_attribute where AttributeID=4 AND Value>=1000 AND Value<=2000) t2 where t1.BuyID=t2.BuyID

[wwwwb]

条件一：`AttributeID`=1 AND `Value`='单间出租' 条件二：`AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000 BuyID=1 、3 均满足这2个条件 取最小BuyID？

[ACMAIN_CHM]

楼主啊，缺少表名，你自己添加一下不就行了？ SELECT BuyID FROM jm_user_buy_attribute WHERE `AttributeID`=1 AND `Value`='单间出租' and exists (select 1 from jm_user_buy_attribute where `AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000) 建议学习一下SQL语言的语法基础。

[wwwwb]

select * from jm_user_buy_attribute where AttributeID=1 AND Value='单间出租' 结果：1、3 select * from jm_user_buy_attribute where AttributeID=4 AND Value>=1000 AND Value<=2000 结果：1、3、4 select * from (select * from jm_user_buy_attribute where AttributeID=1 AND Value='单间出租') t1, (select * from jm_user_buy_attribute where AttributeID=4 AND Value>=1000 AND Value<=2000) t2 where t1.BuyID=t2.BuyID 结果：1、3 不知道楼主是否测试过

[mkcat]

引用 10 楼 wwwwb 的回复:

select * from jm_user_buy_attribute where AttributeID=1 AND Value='单间出租' 结果：1、3 select * from jm_user_buy_attribute where AttributeID=4 AND Value>=1000 AND Value<=2000 结果：1、3、4 se……

这样是可以的,下面收我的总SQL语句 SELECT * FROM jm_user_buy t, (SELECT * FROM jm_user_buy_attribute WHERE `AttributeID`=1 AND `Value`='整套出租') t1, (SELECT * FROM jm_user_buy_attribute WHERE `AttributeID`=12 AND `Value`>=500 AND `Value`<=1000) t12 WHERE (t.`IndustryID`=289) AND t.`BuyID`=t1.`BuyID` AND t.`BuyID`=t12.`BuyID` AND (t.`ShopState` = 0) ORDER BY t.`UpdateTime` ASC