SQL Server How can I select if a row contains a specific type, not contains select existing row?

uurv41yg  于 2023-04-19  发布在  其他
关注(0)|答案(2)|浏览(118)

I have a table below. There are three types (A, B, and C) available. I want to create general an SQL query. If a row has type B or type C, the row with type B or C should be listed. If a row has just type A, the row with type A should be listed.

Table;
| Number | Type |
| ------------ | ------------ |
| 1 | A |
| 1 | B |
| 2 | A |
| 3 | A |
| 3 | C |
| 4 | A |
| 5 | A |
| 6 | A |
| 6 | B |
| 6 | C |

The expected result when the query run;

NumberType
1B
2A
3C
4A
5A
6B
6C

How can I create the query?

vwhgwdsa

vwhgwdsa1#

I would assign each type a precedence, and only return the types of the highest precedence.

Where two types can be the same precedence (because you want to return both), RANK()(rather than ROW_NUMBER() ) will ensure both are assigned the same value.

WITH
  precedence AS
(
  SELECT
    *,
    RANK()
      OVER (
        PARTITION BY Number
            ORDER BY CASE Type WHEN 'C' THEN 2
                               WHEN 'B' THEN 2
                               WHEN 'A' THEN 1
                                        ELSE 0
                     END
                       DESC
      )
        AS row_precedence
  FROM
    your_table
)
SELECT
  *
FROM
  precedence
WHERE
  row_precedence = 1

One option to make it a little neater could be to use APPLY(or a join on a lookup table) to derive the integers outside of the window function's code...

WITH
  precedence AS
(
  SELECT
    *,
    RANK()
      OVER (
        PARTITION BY Number
            ORDER BY type_precedence.value DESC
      )
        AS row_precedence
  FROM
    your_table
  CROSS APPLY
  (
    SELECT
      CASE Type WHEN 'C' THEN 2
                WHEN 'B' THEN 2
                WHEN 'A' THEN 1
                         ELSE 0
      END
        AS value
  )
    AS type_precedence
)
SELECT
  *
FROM
  precedence
WHERE
  row_precedence = 1

Demo: https://dbfiddle.uk/2Abwpa8p

xlpyo6sf

xlpyo6sf2#

Phrasing it in a different way helps finding the right command:

You want to have all rows with type 'B' and 'C', plus those with type 'A' if no other row with type 'B' or 'C' exists for that number.

So the SQL command you're looking for is:

SELECT t.Number, t.Type
FROM your_table t
WHERE t.Type IN ('B', 'C')
  OR (t.Type = 'A'
    AND NOT EXISTS (
      SELECT 1
      FROM your_table e
      WHERE e.Type IN ('B', 'C')
        AND e.Number = t.Number
    )
  )

相关问题