求一sql语句(mysql)
求一sql语句(mysql)
记录集如下:
id data_id s_number
27 6saw9is5 200
28 6saw9is5 100
29 6saw9is5 300
30 5haw9is5 400
31 5haw9is5 200
32 5haw9is5 450
求出s_number最小的记录.
也就是说,返回的值应该是
28 6saw9is5 100
31 5haw9is5 200
select *
from table_name
where s_number in
(
select min(s_number)
from table_name
)
还是楼上的厉害啊！
我的为什么不行？在mysql 5种测试通过。
select id, min(s_number) as number ,data_id from (select * from tablename order by s_number ) as t group by data_id
这样得到的是
31 5haw9is5 200
28 6saw9is5 100
select * from (select id, min(s_number) as number ,data_id from (select * from tablename order by s_number ) as t group by data_id) as tttt order by id
这样排序就和你的一样了。
mysql> select id,min(s_number) as number,data_id from mytable where s_number in
(select min(s_number) from mytable) group by data_id;
+——+——–+———-+
| id | number | data_id |
+——+——–+———-+
| 28 | 100 | 6saw9is5 |
+——+——–+———-+
1 row in set (0.00 sec)
===================================
如果是这样的结果呢??
select a.* from mytable as a,(select data_id,min(s_number) from mytable group by data_id) as b where a.data_id=b.data_id and a.s_number=b.s_bumber
select a.* from mytable as a,(select data_id,min(s_number) as s_number from mytable group by data_id) as b where a.data_id=b.data_id and a.s_number=b.s_number
显示行 0 – 1 (2 总计, 查询花费 0.0008 秒)
SQL 查询:
SELECT a . *
FROM mytable AS a, (
SELECT data_id, min( s_number ) AS s_number
FROM mytable
GROUP BY data_id
) AS b
WHERE a.data_id = b.data_id
AND a.s_number = b.s_number
LIMIT 0 , 30
[编辑] [解释 SQL] [创建 PHP 代码] [刷新]
行，开始行数：
以 水平 水平(旋转标题) 垂直 模式显示，并且在 个单元格后重复标题
id data_id s_number
? 28 6saw9is5 100
? 31 5haw9is5 200
mysql 5.2环境
select id,data_id,min(s_number) as s_number from TABLE_NAME group by data_id;
绝对100%好使，如果同意呢，就给点分啦。
min(s_number) 为求s_number列里面的最小值
group by data_id 为以data_id列来分组显示
