MAX和MIN添加查询

SELECT a.price, b.price, a.delivery_hour, a.resource_id, b.delivery_date
             FROM mms_realtime_dispatch_prices_report AS a
             INNER JOIN mms_realtime_expost_prices_report AS b 
             ON a.resource_id = b.resource_id
             AND a.delivery_hour = b.delivery_hour
             AND a.delivery_date = b.delivery_date
             WHERE a.delivery_date = '2013-09-21'
             ORDER BY a.resource_id ASC 

This is my query. Is it possible to add a new column that determine the max and the min in price?

这是我的疑问。是否可以添加一个新列来确定价格的最大值和最小值?

2 个解决方案

#1

  SELECT MIN(a.price) as MIN_PRICE, MAX(a.price) as MAX_PRICE, b.price, a.delivery_hour, a.resource_id, b.delivery_date
         FROM mms_realtime_dispatch_prices_report AS a
         INNER JOIN mms_realtime_expost_prices_report AS b 
         ON a.resource_id = b.resource_id
         AND a.delivery_hour = b.delivery_hour
         AND a.delivery_date = b.delivery_date
         WHERE a.delivery_date = '2013-09-21'
         GROUP BY b.price, a.delivery_hour, a.resource_id, b.delivery_date
         ORDER BY a.resource_id ASC 

#2

Try something like this:

尝试这样的事情:

SELECT max(a.price), min(b.price), a.delivery_hour, a.resource_id, b.delivery_date
         FROM mms_realtime_dispatch_prices_report AS a
         INNER JOIN mms_realtime_expost_prices_report AS b 
         ON a.resource_id = b.resource_id
         AND a.delivery_hour = b.delivery_hour
         AND a.delivery_date = b.delivery_date
         WHERE a.delivery_date = '2013-09-21'
         GROUP BY a.delivery_hour, a.resource_id, b.delivery_date
         ORDER BY a.resource_id ASC