在 MySQL 中添加 DISTINCT 运算符的 SQL MIN 和 MAX 结果 [英] SQL MIN and MAX results with added DISTINCT operator in MySQL

问题描述

我在获取我想要的结果集时遇到了一些困难(多年没有写过查询).我的表 itemdata 看起来像这样:

I'm having some difficulty (have not written queries in years) getting the result set I am after. My table itemdata looks like this:

year make model
1972 foo  bar
1973 foo  bar
1975 foo  bar
1971 bar  foo
1972 bar  foo
1973 bar  foo
1974 bar  foo
1975 bar  foo
1976 bar  foo

我的目标是返回一个结果集，该结果集由具有开始年份和结束年份的不同 make 模型对组成，如下所示:

My goal is to return a result set consisiting of the distinct make model pairs with a start year and an end year like so:

startyear endyear make model
1972      1975    foo  bar
1971      1976    bar  foo

出于某种原因(最有可能是缺乏 SQL 知识)我没有得到我想要的结果集.我尝试了许多查询，我认为我会成功:

For some reason (lack of SQL knowledge being the most likely) I'm not getting the result set I want. I've tried a number of queries and I thought I would succeed with:

SELECT MIN(year) as startyear , MAX(year) as endyear, make, model 
FROM itemdata 
WHERE model = (SELECT DISTINCT model FROM itemdata)

...但是我的子查询返回多行错误查询.

...however my subquery returning with multiple rows errored out the query.

如果您有任何帮助或建议，我将不胜感激！

I'd appreciate any help or advice!

推荐答案

改为在 make 和 model 上使用 GROUP BY，这应该可以满足您的需求.

Use a GROUP BY on make and model instead, and that should get you what you need.

SELECT MIN(year) AS startyear, MAX(year) as endyear, make, model
FROM itemdata GROUP BY make, model;

这篇关于在 MySQL 中添加 DISTINCT 运算符的 SQL MIN 和 MAX 结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！