＋设为首页 ＋添加到收藏夹
table:books
id number quantity
6 A23 17
3 A23 4
2 A23 33
1 A23 7
8 B11 2
7 B11 7
5 B11 4
9 C11 23
4 C11 5
现在要查询每个number中id最大的记录,如结果:
id number quantity
6 A23 17
8 B11 2
9 C11 23
应该如何查询?
晕.
刚才帖了一个..
#
# Table structure for table shop
#
DROP TABLE IF EXISTS `shop`;
CREATE TABLE `shop` (
`name` varchar(20) default NULL,
`price` int(10) unsigned default NULL,
`Comments` varchar(18) default NULL
) TYPE=MyISAM;
#
# Dumping data for table shop
#
INSERT INTO `shop` (`name`, `price`, `Comments`) VALUES("彩电", "10", "彩电1");
INSERT INTO `shop` (`name`, `price`, `Comments`) VALUES("电话", "20", "电话1");
INSERT INTO `shop` (`name`, `price`, `Comments`) VALUES("彩电", "11", "彩电2");
INSERT INTO `shop` (`name`, `price`, `Comments`) VALUES("电冰箱", "12", "电冰箱1");
INSERT INTO `shop` (`name`, `price`, `Comments`) VALUES("电冰箱", "13", "电冰箱2");
INSERT INTO `shop` (`name`, `price`, `Comments`) VALUES("洗衣机", "16", "洗衣机1");
#
# 解决思路, 将全部字段值组合成一个字符串,然后比较得出最小值,,
# 为了达到 字符串比较 与 数字类型比较 结果相同
# 就必须将整形左填充 0
# 剩余的事情就是再将结果切割了
#
SELECT
MIN(CONCAT_WS(,, LPAD(price, 20, 0), name, Comments) )
FROM shop
GROUP BY name
+-------------------------------------------------------------+
| MIN(CONCAT_WS(,, LPAD(price, 20, 0), name, Comments) ) |
+-------------------------------------------------------------+
| 00000000000000000010,彩电,彩电1 |
| 00000000000000000012,电冰箱,电冰箱1 |
| 00000000000000000020,电话,电话1 |
| 00000000000000000016,洗衣机,洗衣机1 |
+-------------------------------------------------------------+
#
#
#
SELECT
CAST(SUBSTRING_INDEX(MIN(CONCAT_WS(,, LPAD(price, 20, 0), name, Comments) ), ,, 1) AS UNSIGNED) as ResultPrice,
SUBSTRING_INDEX(SUBSTRING_INDEX(MIN(CONCAT_WS(,, LPAD(price, 20, 0), name, Comments) ), ,, 2), ,, -1) as ResultName,
SUBSTRING_INDEX(MIN(CONCAT_WS(,, LPAD(price, 20, 0), name, Comments) ), ,, -1) as ResultComments
FROM shop
GROUP BY name
+-------------+------------+----------------+
| ResultPrice | ResultName | ResultComments |
+-------------+------------+----------------+
| 10 | 彩电 | 彩电1 |
| 12 | 电冰箱 | 电冰箱1 |
| 20 | 电话 | 电话1 |
| 16 | 洗衣机 | 洗衣机1 |
+-------------+------------+----------------+
