我有一个包含这些列的表(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.1988What I want is ordering the date column (desc) and getting the distinct values of city. So the resut should be;
Lisbon PortoI tried;
select distinct(city) from TableCity order by cityDate descbut 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.CityOR
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 PortoRegards
更多推荐
发布评论