SQL - 限制组的结果
我有下表:
Car | color | year
---------+----------+------
mercedes | blue | 1991
mercedes | yellow | 1993
mercedes | blue | 1996
mercedes | red | 1998
renaud | blue | 1991
renaud | yellow | 1993
renaud | blue | 1996
renaud | red | 1998
我正在寻找一个查询,该查询允许检索每种汽车类型的最旧年份行。 在这种情况下,它应该给我:
Car | color | year
----------+-------+------
mercedes | blue | 1991
renaud | blue | 1991
我尝试过 ORDER BY + LIMIT、HAVING、GROUP BY...但它总是添加我不需要的额外行(我必须迭代结果集以创建一个子结果)。
你知道我该怎么做吗?
非常感谢您的有用提示!
凯劳德
I have the following table :
Car | color | year
---------+----------+------
mercedes | blue | 1991
mercedes | yellow | 1993
mercedes | blue | 1996
mercedes | red | 1998
renaud | blue | 1991
renaud | yellow | 1993
renaud | blue | 1996
renaud | red | 1998
I am looking for a query which permits to retrieve the oldest year row of each car type.
In this case, it should give me :
Car | color | year
----------+-------+------
mercedes | blue | 1991
renaud | blue | 1991
I have tried with ORDER BY + LIMIT, HAVING, GROUP BY.... But it always add extra rows which I don't need (I have to iterate over the result set to create a sub result).
Do you have ideas of how I could do that ?
Thanks a lot for your helpfull hints !
Kheraud
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
嵌套选择 - 像这样:
这为每辆车提供了正确的年份。然后你想添加一种颜色(没有指定如果那一年有不止一种该车该怎么办......)
nested select - something like this:
this gives the proper year for each car. then you want to add a color (not specified what to do if there are more than one in that year for that car...)
从 table_name 中选择汽车、颜色、最大(年份)
按车分组,颜色
select car, color, Max(Year) from table_name
group by car,color