如何在 SQL 中分组和选择最小值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9885879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 15:01:49 来源:igfitidea点击:
How to GROUP and choose lowest value in SQL
提问by feronovak
I have table consisting of these fields:
我有由这些字段组成的表：
id | date_from | date_to | price
--------------------------------------------
CK1 22-12-2012 29-12-2012 800
CK1 22-12-2012 29-12-2012 1200
CK2 22-12-2012 29-12-2012 1400
CK2 22-12-2012 29-12-2012 1800
CK2 22-12-2012 29-12-2012 2200
How do I create SQL select that groups results by ID, DATE_FROM, DATE_TO and picks lowest value from price.
如何创建按 ID、DATE_FROM、DATE_TO 对结果进行分组并从价格中选择最低值的 SQL 选择。
So result would be
所以结果是
CK1 22-12-2012 29-12-2012 800
CK2 22-12-2012 29-12-2012 1400
回答by Phil
select id, date_from, date_to, min(price)
from table
group by id, date_from, date_to
回答by Arion
If your dbms support cte then you can do like this;
如果您的 dbms 支持 cte，那么您可以这样做；
Test data
测试数据
DECLARE @tbl TABLE
(
id VARCHAR(100),
date_from VARCHAR(100),
date_to VARCHAR(100),
price INT
)
INSERT INTO @tbl
VALUES
('CK1','22-12-2012','29-12-2012',800),
('CK1','22-12-2012','29-12-2012',1200),
('CK2','22-12-2012','29-12-2012',1400),
('CK2','22-12-2012','29-12-2012',1800),
('CK2','22-12-2012','29-12-2012',2200)
Query
询问
;WITH CTE
AS
(
SELECT
RANK() OVER(PARTITION BY id ORDER BY price ASC) AS RowNbr,
tbl.*
FROM
@tbl AS tbl
)
SELECT
*
FROM
CTE
WHERE
CTE.RowNbr=1
回答by Mahmoud Gamal
Like this:
像这样：
SELECT id, date_from, date_to, MIN(price)
FROM TableName
GROUP BY id, date_from, date_to
回答by Alex_L
SELECT id, date_from, date_to, min(price)
FROM my_table
GROUP BY id, date_from, date_to
