根据优先级选择查询选择 [英] Select query select based on a priority

问题描述

有人请更改我的标题以更好地反映我想问的问题.

Someone please change my title to better reflect what I am trying to ask.

我有一张像

Table (id, value, value_type, data)

ID 不是唯一的.没有唯一键.

ID is NOT unique. There is no unique key.

value_type 有两个可能的值，比如 A 和 B.

value_type has two possible values, let's say A and B.

B 型比 A 型好，但通常不可用.

Type B is better than A, but often not available.

对于每个 id，如果存在 value_type B 的记录，我想要所有具有该 id 和 value_type B 的记录.

For each id if any records with value_type B exists, I want all the records with that id and value_type B.

如果该 id 的 value_Type B 不存在记录，我想要所有具有该 id 和 value_type A 的记录.

If no record for that id with value_Type B exists I want all records with that id and value_type A.

请注意，如果该 ID 存在 B，我不想要类型为 A 的记录.

Notice that if B exists for that id I don't want records with type A.

我目前使用一系列临时表来执行此操作.是否有一个选择语句(子查询正常)可以完成这项工作?

I currently do this with a series of temp tables. Is there a single select statement (sub queries OK) that can do the job?

非常感谢！

其他详细信息:

SQL Server 2005

SQL Server 2005

推荐答案

RANK，而不是 ROW_NUMBER，因为您希望关系(具有相同 B 值的那些)具有相同的排名值:

RANK, rather than ROW_NUMBER, because you want ties (those with the same B value) to have the same rank value:

WITH summary AS (
  SELECT t.*,
         RANK() OVER (PARTITION BY t.id 
                          ORDER BY t.value_type DESC) AS rank
    FROM TABLE t
   WHERE t.value_type IN ('A', 'B'))
SELECT s.id,
       s.value,
       s.value_type,
       s.data
  FROM summary s
 WHERE s.rank = 1

非 CTE 版本:

SELECT s.id,
       s.value,
       s.value_type,
       s.data
  FROM (SELECT t.*,
               RANK() OVER (PARTITION BY t.id 
                                ORDER BY t.value_type DESC) AS rank
          FROM TABLE t
         WHERE t.value_type IN ('A', 'B')) s
 WHERE s.rank = 1

<小时>

WITH test AS (
   SELECT 1 AS id, 'B' AS value_type
   UNION ALL
   SELECT 1, 'B'
   UNION ALL
   SELECT 1, 'A'
   UNION ALL
   SELECT 2, 'A'
   UNION ALL
   SELECT 2, 'A'),
     summary AS (
   SELECT t.*,
          RANK() OVER (PARTITION BY t.id 
                           ORDER BY t.value_type DESC) AS rank
     FROM test t)
SELECT *
  FROM summary
 WHERE rank = 1

我明白了:

id   value_type  rank
----------------------
1    B           1
1    B           1
2    A           1
2    A           1

这篇关于根据优先级选择查询选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！