在MS Sql中从表中选择不同的值集

I have table in my db which is in MSSQL having following structure

我的db中有一个表，它在MSSQL中，具有以下结构

DayID      Sequence           Cities       Title     Hotels   
----------------------------------------------------------------
1                 1             Manali       Day1      Hotel A      
2                 2             Manali       Day2      Hotel B     
3                 2             Delhi        Day3      Hotel C    
4                 3             Delhi        Day4      Hotel D     
5                 3             Manali       Day5      Hotel E    
6                 4             Manali       Day6      Hotel F

Now I need The result as following

现在我需要如下结果

Cities
------   
Manali    
Delhi   
Manali.

I have used group by Cities but it is giving only two cities

我曾按城市分组，但它只提供两个城市

Manali
Delhi

But I need following output.

但我需要跟踪输出。

Manali    
Delhi   
Manali

Please Suggest.

请建议。

Thanks in Advance.

提前谢谢。

3 个解决方案

#1

Try this:

试试这个:

SELECT Cities
FROM (
   SELECT Cities, DayID,
          ROW_NUMBER() OVER (ORDER BY DayID) -
          ROW_NUMBER() OVER (PARTITION BY Cities ORDER BY DayID) AS grp
   FROM mytable) AS t
GROUP BY Cities, grp
ORDER BY MIN(DayID)

Calculated field grp identifies islands of consecutive records having the same Cities value. Using this field in a GROUP BY clause, we can extract the required sequence of Cities values.

计算的场grp识别具有相同城市值的连续记录的岛屿。使用GROUP BY子句中的这个字段，我们可以提取所需的城市值序列。

Note: The above query works in SQL Server.

注意:上面的查询在SQL Server中工作。

#2

depending on requirement you can group by eg: select Cities from table group by Cities

根据需求，您可以按eg:从表组中选择城市。

#3

First make sure the table is sorted so we can use the 'by' option in the data step:

首先，确保表已排序，以便在数据步骤中使用“by”选项:

 proc sort data=tablename;
 by sequence cities;
 run;

In the data step use the 'by' clause to select the first row of each group and output. Only 'keep' cities as requested by user

在数据步骤中，使用“by”子句选择每个组的第一行和输出。只按用户要求保存城市。

 data desired;
 keep cities;
 set tablename;
 by sequence cities;
 if first.cities then output;
 run;

#1