这是数据表
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数据库中触发器的状态为off,怎么启动啊?
- 求sql语句(mysql)
- MySQL分组数据怎么用php分页
- mysql5.0与5.1的比较
- 关于mysqlbinlog 生成 sql 文件中有乱码。数据存入时是用utf-8 编码。
- 如何查询数据里的开始字段ID号和结束字段ID号?
- 求一条语句,如何将一列中的数据按字母顺序排列?
- 一直认为数据库就是表的集合,看来好像不是哦
- 如何查看所有被赋值的变量
- 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