SQL查询中的问题区别和顺序（简单但可能吗？）(Distinct and order by issue in SQL query (Easy but is it possible?))

SQL查询中的问题区别和顺序（简单但可能吗？）(Distinct and order by issue in SQL query (Easy but is it possible?))

我有一个包含这些列的表（MS SQL-Server 2008）;

城市日期

和这样的行;

波尔图| 1988年11月20日 波尔图| 1988年11月19日 里斯本| 1988年11月21日

我想要的是订购日期列（desc）并获取城市的不同值。 所以应该是;

里斯本 波尔图

我试过了;

select distinct(city) from TableCity order by cityDate desc

但输出是;

如果指定了SELECT DISTINCT，则消息145，级别15，状态1，行1 ORDER BY项目必须出现在选择列表中。

I have a table with these columns (MS SQL-Server 2008);

city cityDate

and rows like this;

Porto | 20.11.1988 Porto | 19.11.1988 Lisbon | 21.11.1988

What I want is ordering the date column (desc) and getting the distinct values of city. So the resut should be;

Lisbon Porto

I tried;

select distinct(city) from TableCity order by cityDate desc

but the output is;

Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

最满意答案

你只需要使用group by而不是distinct：

假设T是你的表：

WITH T as ( SELECT 'Porto' City,'20.11.1988' CityDate UNION ALL SELECT 'Porto' City,'19.11.1988' CityDate UNION ALL SELECT 'Lisbon' City,'21.11.1988' CityDate )

- 测试1：

select City,CityDate from T GROUP BY City,CityDate Order by CityDate DESC

- 结果：这仍然显示三行，因为波尔图的城市日期不一样，但如果波尔图城市日期相同，它将只显示两行。

City CityDate Lisbon 21.11.1988 Porto 20.11.1988 Porto 19.11.1988

- 测试2：

select T2.City FROM (select City from T GROUP BY City,CityDate) as T2 GROUP BY T2.City

要么

你可以使用CTE：

With T as ( select City from YourTable GROUP BY City,CityDate ) select City FROM T group by City

- 结果：

City Lisbon Porto

问候

You just need to use group by instead of distinct:

Suppose T is your Table:

WITH T as ( SELECT 'Porto' City,'20.11.1988' CityDate UNION ALL SELECT 'Porto' City,'19.11.1988' CityDate UNION ALL SELECT 'Lisbon' City,'21.11.1988' CityDate )

--TEST 1:

select City,CityDate from T GROUP BY City,CityDate Order by CityDate DESC

--Result: This still displays the three rows because City Date of Porto is not the same,but if Porto City Date is the same it will display only two rows.

City CityDate Lisbon 21.11.1988 Porto 20.11.1988 Porto 19.11.1988

--TEST 2:

select T2.City FROM (select City from T GROUP BY City,CityDate) as T2 GROUP BY T2.City

OR

you can use CTE:

With T as ( select City from YourTable GROUP BY City,CityDate ) select City FROM T group by City

--Result:

City Lisbon Porto

Regards