复杂的选择陈述，只能获取更高的级别以忽略较低的水平

Question

我如何选择既有州级和国家级别的候选人。

+------+--+--------+-----------+
| Name |  |  Level             |
+------+--+--------+-----------+
| A    |  |   state            |
| B    |  |   State            |
| C    |  |    state           |
| A    |  |  National          |
+------+--+--------+-----------+

该查询应仅获取我的A纪录一次，以便忽略国家一级。预先感谢您。

Answer 1

您可以使用ROW_NUMBER将等级提高到最高级别，然后选择最高的等级。

架构：

 CREATE TABLE #TAB( Name VARCHAR(50), LevelS VARCHAR(50) )
 INSERT INTO #TAB 
 SELECT 'A','state' 
 UNION ALL 
 SELECT 'B','State' 
 UNION ALL 
 SELECT 'C','state' 
 UNION ALL 
 SELECT 'A','National' 

现在选择如下

 SELECT * FROM (
 SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY 
  CASE WHEN LevelS='state' THEN 2
       WHEN LevelS='National' THEN 1 END ) SNO
 ,NAME
 ,LevelS  FROM #TAB
 )A
 WHERE SNO=1

结果：

+-----+------+----------+
| SNO | NAME |  LevelS  |
+-----+------+----------+
|   1 | A    | National |
|   1 | B    | State    |
|   1 | C    | state    |
+-----+------+----------+