这是数据表
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
尝试二搜索出来的结果不正确。
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
尝试二搜索出来的结果不正确。
解决方案 »
- mysql语句执行时间的问题
- 存储过程 小问题
- MySQL是哪个公司开发的?有什么特点?只能用命令行操作吗?
- 挑逗你的智慧:mysql中的返回数据怎么转换成xml文档形式??
- Mysql里如何使用连接表达式???
- 求教如何用JAVA在MYSQL中存取图片啊?
- 对于POSTGRES数据库表进行select操作时,如何将两个field合并成一个field
- mysql自动关闭?
- 为什么会出错:mysql="select * from Bbs_User where User_Name="& request.form("Name")谢谢!
- mysql写入图片不成功
- mysqldump -T ./ test
- mssql2008的数据导入mysql中,工具mysqlworkbench ,关于字符集的问题
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”
这样写的SQL搜索出来的不正常啊
条件二:`AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000
BuyID=1 、3 均满足这2个条件
取最小BuyID?
WHERE `AttributeID`=1 AND `Value`='单间出租'
and exists (select 1 from jm_user_buy_attribute where `AttributeID`=4 AND `Value`>=1000 AND `Value`<=2000)建议学习一下SQL语言的语法基础。
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)
(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、3select * from jm_user_buy_attribute where AttributeID=4 AND Value>=1000 AND Value<=2000
结果:1、3、4select * 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不知道楼主是否测试过
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