用select * from tab where number = '333333' 和
select max(loop) from tab where number = '333333'
都可以查出数据
但用下面联合在一起就报语法错误。菜鸟请教为什么?select * from tab where number = '333333'
and loop in( select max(loop) from tab where number = '333333')
解决方案 »
- 请教关于mysql的slow query log的问题
- 如何按日期分段查询?
- 零件编号问题
- 如何使用mysql_ping命令?
- MySQL远程连接的问题
- mysql连接的问题
- jsp+MySQL,小项目外包!有意向者这边来谈谈(QQ:393418945)
- 为什么会出错:mysql="select * from Bbs_User where User_Name="& request.form("Name")谢谢!
- 多值问题
- linux下的mysql无法根据UNIQUE约束创建外键?
- SQL批量修改
- Cannot proceed because the tables used by events were found damaged at serve
from `tab`
where `number`='333333'
and `loop` in ( select max(`loop`) from tab where `number`='333333');
是不是太多关键字了。 摄氏信息是什么?
and `loop` in( select max(loop) from `tab` where `number` = '333333')
and productid in( select max(productid) from product where category = 'computer')
create table tab(number char(8), loop int(11));
ERROR 1064 (42000): 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 'loop int(11))' at line 1
loop是关键字吧!!
mysql> create table tab(number char(8), lop int(11));
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> insert into tab values('333333', 1),('333333',2)
-> ,('333333',3);
Query OK, 3 rows affected (0.00 sec)
mysql> select * from tab where number = '333333' and lop in( select max(lop) from tab where number = '333333');
+--------+------+
| number | lop |
+--------+------+
| 333333 | 3 |
+--------+------+
1 row in set (0.01 sec)
+----+--------+------+
| id | number | loop |
+----+--------+------+
| 1 | 333333 | 10 |
| 2 | 333333 | 60 |
| 3 | 333333 | 40 |
| 4 | 333333 | 20 |
+----+--------+------+
4 rows in set (0.00 sec)mysql>
mysql> select *
-> from `tab`
-> where `number`='333333'
-> and `loop` in ( select max(`loop`) from tab where `number`='333333');
+----+--------+------+
| id | number | loop |
+----+--------+------+
| 2 | 333333 | 60 |
+----+--------+------+
1 row in set (0.00 sec)mysql> select * from tab where number = '333333'
-> and loop in( select max(loop) from tab where number = '333333')
-> ;
ERROR 1064 (42000): 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 'loop
in( select max(loop) from tab where number = '333333')' at line 2
mysql>
select * from product where category = 'computer'
and productid in( select max(productid) from product where category = 'computer')
我分别在MYSQL和MSSQL中都查询了没有问题啊
mysql> select * from `tab` where `number` = '333333'
-> and `loop` in( select max(`loop`) from `tab` where
`number` = '333333') ;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'select max(
`loop`) from `tab` where `number`
mysql>
特性
MySQ系列
Foreign keys
3.23(针对InnoDB存储引擎)
Unions
4.0
Subqueries
4.1
from `tab` a
where `number`='333333'
and not exists (select 1 from tab where `number`='333333' and `loop`>a.`loop`);
select * from `tab` where `number` = '333333' and `loop` in ( select max(`loop`) from `tab` where
`number` = '333333') ;这样:
select * from `tab` where `number` = '333333' and `loop`=( select max(`loop`) from `tab` where `number` = '333333') ;OR
select * from `tab` A
INNER JOIN
(select max(`loop`) AS MA from `tab` where `number` = '333333') B
ON A.`LOOP`=B.MA
where A.`number` = '333333'