SQL Group By查询-获取聚合函数的相关字段

发布于 2024-11-16 22:00:07 字数 662 浏览 4 评论 0原文

简化了，但对于这样的表：

 id time distance price
 1  20   500      8 
 2  50   500      10 
 3  90   500      12 
 4  80   1000     17 
 5  170  1000     11 
 6  180  1000     13 
 7  19   800      12

我想获取距离 500 和 1000 的最快时间的行，即

 id time distance price
 1  20   500      8 
 4  80   1000     17

如果我这样做，那么

select min(time) from table

可以很好地查找价格，但我无法获取 id 和价格 - 只能获取所有 ID/价格的最大/最小/平均值/第一个值。

我可以通过多次查找来完成 - 例如

select * from table where distance = 500 and time = 20 
select * from table where distance = 1000 and time = 80

，但是有没有一种更好的方法不涉及 1 +（距离数）查询（或至少提供一个结果集，即使在内部它使用该数量的查询）

原文

Simplified, but for a table like:

 id time distance price
 1  20   500      8 
 2  50   500      10 
 3  90   500      12 
 4  80   1000     17 
 5  170  1000     11 
 6  180  1000     13 
 7  19   800      12

I want to get the rows with the quickest time for the distances 500 and 1000, i.e.

 id time distance price
 1  20   500      8 
 4  80   1000     17

If I do

select min(time) from table

that works fine for finding the price, but I can't get the id and price - only the max/min/average/first value of all ids/prices.

I can do it with multiple look ups - e.g.

select * from table where distance = 500 and time = 20 
select * from table where distance = 1000 and time = 80

but is there a better way that doesn't involve 1 + (number of distances) queries (or at least provides one resultset, even if internally it uses that number of queries)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

昇り龍 2024-11-23 22:00:07

您将需要使用内部选择：

SELECT t.id, t.time, t.distance, t.price
FROM table t
JOIN (SELECT MIN(time) as min_time, distance
        FROM table
        GROUP BY distance) as tmp
      ON (t.distance = tmp.distance AND t.time = tmp.min_time)
WHERE t.distance IN (500, 1000)

You will need to use an inner select:

SELECT t.id, t.time, t.distance, t.price
FROM table t
JOIN (SELECT MIN(time) as min_time, distance
        FROM table
        GROUP BY distance) as tmp
      ON (t.distance = tmp.distance AND t.time = tmp.min_time)
WHERE t.distance IN (500, 1000)

回复收藏 0 原文

街道布景 2024-11-23 22:00:07

您需要将 min 内容放入having子句中，因此您的查询将是select * from table group by distancehaving min(distance);
（未经测试）
或者您可以使用子查询来查找：
select * from table where distance = (select distance from table where min(time)) and time = select min(time) from table) （也未经测试:)）

回复收藏 0 原文

请远离我 2024-11-23 22:00:07

只需按顺序和限制即可。那么您可以在 1 次查询中获得 500 距离的最快速度。

select * from thetable where distance = 500 ORDER BY time ASC LIMIT 1

just order by and limit. then you have the fastest for the 500 distance in 1 query.

select * from thetable where distance = 500 ORDER BY time ASC LIMIT 1

回复收藏 0 原文

﹏雨一样淡蓝的深情 2024-11-23 22:00:07

试试这个——

SELECT t1.* FROM table1 t1
  JOIN (SELECT distance, MIN(time) min_time FROM table11 WHERE distance = 500 OR distance = 1000 GROUP BY distance) t2
    ON t1.distance = t2.distance AND t1.time = t2.min_time;

Try this one -

SELECT t1.* FROM table1 t1
  JOIN (SELECT distance, MIN(time) min_time FROM table11 WHERE distance = 500 OR distance = 1000 GROUP BY distance) t2
    ON t1.distance = t2.distance AND t1.time = t2.min_time;

回复收藏 0 原文

银河中√捞星星 2024-11-23 22:00:07

SELECT * FROM tblData INNER JOIN (SELECT MIN(TIME) AS minTime, distance FROM tblData WHERE distance IN (500,1000) GROUP BY distance) AS subQuery ON tblData.distance = subQuery.distance AND tblData.time = subQuery.minTime

SELECT * FROM tblData INNER JOIN (SELECT MIN(TIME) AS minTime, distance FROM tblData WHERE distance IN (500,1000) GROUP BY distance) AS subQuery ON tblData.distance = subQuery.distance AND tblData.time = subQuery.minTime

回复收藏 0 原文

凉城凉梦凉人心 2024-11-23 22:00:07

这个怎么样……正是您正在寻找的东西（已测试）

select *  from Table1 where time in (select min(time)  from table1 where distance = 500 or distance = 1000 group by distance) and (distance = 500 or distance = 1000)

What about this one ... gies exactly what you are looking for (TESTED)

select *  from Table1 where time in (select min(time)  from table1 where distance = 500 or distance = 1000 group by distance) and (distance = 500 or distance = 1000)

回复收藏 0 原文

~没有更多了~