SQL 选择行,其中 id=max(id)
我有一个表,其中包含具有相同标题的产品的多个实例的产品信息,通过不同的颜色及其 ID 进行标识。我需要输出 id = 按标题分组的最大 id 的整行,但我似乎无法做到这一点。这是一个非常简化的表格和一些示例数据:
id title colour description
1 rico red blah1
2 rico blue blah2
3 rico yellow blah3
4 katia black blah4
5 katia white blah5
在这个示例中,我的代码中,当我想要 3 个 rico 黄 blah3 时,我得到 1 个 rico 红 blah1。
这是我正在使用的代码:
SELECT pd_id, pd_title, pd_description, pd_colour,
pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
WHERE
cat_sub_id_1 = '".$cat_sub_id."'
AND cat_parent_id='".$cat_parent_id."'
GROUP BY pd_title
HAVING MAX(pd_id)
ORDER BY pd_id DESC
更新:谢谢大家,
我使用 alinoz 的答案得出了以下有效的代码:)
SELECT
pd_id,pd_title,pd_description,pd_colour,pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
HERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
AND pd_id IN (
SELECT max(pd_id)
FROM product
WHERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
GROUP BY pd_title
)
GROUP BY pd_title
ORDER BY pd_id DESC
I have a table which holds product information with multiple instances of a product with the same title, identified by different colours and their ids. I need to output the entire row where the id = the maximum id grouped by the title, but I can't seem to get it to do this. Here is a very simplified table and some example data:
id title colour description
1 rico red blah1
2 rico blue blah2
3 rico yellow blah3
4 katia black blah4
5 katia white blah5
In this example with my code, I get 1 rico red blah1 when I want 3 rico yellow blah3.
Here is the code I am using:
SELECT pd_id, pd_title, pd_description, pd_colour,
pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
WHERE
cat_sub_id_1 = '".$cat_sub_id."'
AND cat_parent_id='".$cat_parent_id."'
GROUP BY pd_title
HAVING MAX(pd_id)
ORDER BY pd_id DESC
UPDATE: Thanks guys,
I used alinoz's answer to come up with the following code which works :)
SELECT
pd_id,pd_title,pd_description,pd_colour,pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
HERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
AND pd_id IN (
SELECT max(pd_id)
FROM product
WHERE cat_sub_id_1 = '".$cat_sub_id."' AND cat_parent_id='".$cat_parent_id."'
GROUP BY pd_title
)
GROUP BY pd_title
ORDER BY pd_id DESC
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(7)
尝试在 where 中添加一个子句 pd_id = (select max(pd_id) from ... )
SELECT pd_id, pd_title, pd_description, pd_colour,
pd_price,pd_large_image,pd_date,cat_sub_id_3,pd_new
FROM product
WHERE
cat_sub_id_1 = '".$cat_sub_id."'
AND cat_parent_id='".$cat_parent_id."'
AND pd_id = (select max(pd_id) from product)
GROUP BY pd_title
此查询不是最佳的,但它可以完成这项工作。
除了 Adrian 和 Salman 的方法之外,还有一种方法,当存在平局、两行或更多行具有相同(最大)id 时,会给出不同的结果。它只会显示每个标题一行,而另一个查询将显示所有行(在您的情况下,这可能与您按 id 排序时无关,我认为这是主键):
SELECT
t.*
FROM
TableX AS t
JOIN
( SELECT DISTINCT
title --- what you want to Group By
FROM TableX
) AS dt
ON t.PK = --- the Primary Key of the table
( SELECT tt.PK
FROM TableX AS tt
WHERE tt.title = dt.title
ORDER BY id ASC --- (or DESC) what you want to Order By
LIMIT 1
)
我认为,没有必要使用子选择:
select
SUBSTRING_INDEX(group_concat(testtab.ActionID order by testtab.ActionID DESC SEPARATOR '|'), '|', 1) as ActionIDs,
SUBSTRING_INDEX(group_concat(testtab.DiscountedPrice order by testtab.ActionID DESC SEPARATOR '|'), '|', 1) as DiscountedPrices
testtab.*
from testtab
where
testtab.StartDate <= 20160120
and testtab.EndDate > 20160120
and testtab.VariantID = 302304364
and testtab.DeleteStatus = 0
group by testtab.VariantID
order by testtab.VariantID, testtab.ActionID;
它只是我测试中的一个快速示例。只需在 substring_indexes 中以相同的方式进行排序并使用良好的分隔符即可。
玩得开心
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
啊啊啊,古老的 greates-n-per-group...
当然,您应该相应地根据您的需要进行调整。
另外,我认为这是“必读”:SQL Select only rows with Max Value on a Column
Aaahhh, the good old greatest-n-per-group...
Of course, you should adapt this to your needs accordingly.
Also, I think this is a "must read": SQL Select only rows with Max Value on a Column