我有一个名为的下表
store_items
id item_no cat sub
1 1l500bk lady sport
2 1l1550bk lady sport
3 2m1600rd men shoe
4 2m1599rd men shoe
5 2m1589rd men shoe
6 3l900bk baby shoe
7 3l1000bk baby shoe
正如您所看到的,item_no包含数字和文本,当涉及到排序时,我知道这很痛苦。然而,我已经尝试过测试这个PHP代码:
//Table of results
$query = "SELECT * from store_items GROUP by cat,sub ORDER by cat,sub,item_no Desc";
$result = mysql_query($query);
if(!$result){
mysqli_error();
}
while($row=mysql_fetch_assoc($result)){
echo "<tr><td align=center>{$row['cat']}</td><td align=center>{$row['sub']}</td><td align=center>";
echo ucfirst($row['item_no']);
echo "</td></tr>";
}
我只想输出cat的列表,并将其分组在一起,其中每一个都将在其旁边列出最新/最高的item_no
。。。如下所示:
预期输出:
cat sub item_no
lady sport 1l1550bk
men shoe 2m1600rd
baby shoe 3l1000bk
这里的主要问题是,对于特定的cat
和sub
值,MAX(item_no)
与MAX(id)
不对应。另一个问题是item_no
本身是字母数字的,不能自然地确定哪一个是最大值。在这种情况下,您可以首先定义自己的函数(因为MySQL不提供这样的函数),该函数允许您将item_no
值转换为纯数字字符串(1l1550bk->11550):
自定义SQL函数:
DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE output VARCHAR(255) DEFAULT '';
DECLARE iterator INT DEFAULT 1;
DECLARE count INT DEFAULT 0;
WHILE iterator < (LENGTH(input) + 1) AND count < 10 DO
IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
SET COUNT=COUNT+1;
END IF;
SET iterator = iterator + 1;
END WHILE;
RETURN output;
END
$$
DELIMITER $$
然后,您可以使用此STRIP_NON_DIGIT()
函数为每个cat,sub
对确定适当的最大item_no
值:
SELECT
s.cat,
s.sub,
s.item_no
FROM
store_items s,
(
SELECT MAX(CAST(STRIP_NON_DIGIT(item_no) AS UNSIGNED)) as numeric_item, cat, sub
FROM store_items
GROUP BY cat, sub
) t
WHERE
CAST(STRIP_NON_DIGIT(s.item_no) AS UNSIGNED) = t.numeric_item
AND s.cat = t.cat
AND s.sub = t.sub;
这将给你想要的输出:
+------+-------+----------+
| cat | sub | item_no |
+------+-------+----------+
| baby | shoe | 3l1000bk |
| lady | sport | 1l1550bk |
| men | shoe | 2m1600rd |
+------+-------+----------+
3 rows in set
希望这对你有帮助。
MySQL MAX()
不适用于字母数字值。
如果表中没有太多行,那么可以获取所有数据,并使用完整的PHP完成这项工作。
如果你的桌子太大,你应该考虑增加另一个场地,就像加里·海耶斯说的那样。您可以使用自动递增ID。
如果您没有在GROUP BY中使用SELECT item_no或对其应用聚合函数,那么它实际上没有意义。您将随机获得reach行的item_no值。您可以改用SELECT cat, sub, GROUP_CONCAT(item_no) as item_nos
。
SELECT cat, sub, SUBSTRING_INDEX(GROUP_CONCAT(item_no ORDER BY id DESC),',',1) as newest_item_no
FROM store_items
GROUP BY cat, sub
ORDER BY cat,sub
编辑:我不确定你所说的最新/最高商品编号是什么意思,但我上面的查询假设最高ID是最新的商品。在GROUP_CONCT中,如果您想要排序,您可以将"id"更改为"item_no"。GROUP_CONCAT
将按该顺序为item_no提供逗号分隔的列表。SUBSTRING_INDEX函数只获取丢失的第一个item_no,并丢弃其余部分。