Select query select based on a priority

问题:

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

I have a table like

Table (id, value, value_type, data)

ID is NOT unique. There is no unique key.

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

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

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

If no record for that id with value_Type B exists I want all records with that id and value_type 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?

Thanks so much!

Additional details:

SQL Server 2005

回答1:

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

Non CTE version:

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

I get:

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

回答2:

SELECT * 
  FROM table 
 WHERE value_type = B
UNION ALL
SELECT * 
  FROM table 
 WHERE ID not in (SELECT distinct id 
                    FROM table 
                   WHERE value_type = B)

回答3:

The shortest query to do the job I can think of:

SELECT TOP 1 WITH TIES *
FROM #test
ORDER BY Rank() OVER (PARTITION BY id ORDER BY value_type DESC)

This is about 50% worse on CPU as OMG Ponies' and Christoperous 5000's solutions, but the same number of reads. It's the extra sort that is making it take more CPU.

The best-performing original query I've come up with so far is:

SELECT * 
FROM #test 
WHERE value_type = 'B'
UNION ALL
SELECT * 
FROM #test T1
WHERE NOT EXISTS (
   SELECT *
   FROM #test T2
   WHERE
      T1.id = T2.id
      AND T2.value_type = 'B'
)

This consistently beats all the others presented on CPU by about 1/3rd (the others are about 50% more) but has 3x the number of reads. The duration on this query is often 2/3rds the time of all the others. I consider it a good contender.

Indexes and data types could change everything.

回答4:

declare @test as table(
 id int , value [nvarchar](255),value_type [nvarchar](255),data  int)

 INSERT INTO @test
 SELECT 1, 'X', 'A',1 UNION
 SELECT 1, 'X', 'A',2 UNION
 SELECT 1, 'X', 'A',3 UNION
 SELECT 1, 'X', 'A',4 UNION
 SELECT 2, 'X', 'A',5 UNION
 SELECT 2, 'X', 'B',6 UNION
 SELECT 2, 'X', 'B',7 UNION
 SELECT 2, 'X', 'A',8 UNION
 SELECT 2, 'X', 'A',9 


 SELECT * FROM @test x
 INNER JOIN 
 (SELECT id, MAX(value_type) as value_type FROM 
 @test GROUP BY id) as y
 ON x.id = y.id AND x.value_type = y.value_type

回答5:

Try this (MSSQL).

Select id, value_typeB, null
from myTable
where value_typeB is not null
Union All
Select id, null, value_typeA
from myTable
where value_typeB is null and value_typeA is not null

回答6:

Perhaps something like this:

select * from mytable
where id in (select distinct id where value_type = "B")
union
select * from mytable
where id in (select distinct id where value_type = "A" 
and id not in (select distinct id where value_type = "B"))

回答7:

This uses a union, combining all records of value B with all records that have only A values:

SELECT *
FROM mainTable
WHERE value_type = B
GROUP BY value_type UNION SELECT *
                          FROM mainTable
                          WHERE value_type = A
                               AND id NOT IN(SELECT *
                                             FROM mainTable
                                             WHERE value_type = B);