How to select or skip to the next row if meets cer

问题:

I really am not even sure which direction to go with this... I'm trying to select a list of customers based on the following rules:

• Select all rows from Customer where Ranking = 1,

• OR if Ranking = 1 AND Type = Store then Rank 1 and return the row with Rank 2.

• OR if the customer only has 1 row, return it even if the type = Store.

The Ranking is not assigned with a Rank statement in the query. Rather it is an actual column in the Customer table (populated by a stored proc that does the ranking).

Using the example below I'd want rows 1, 4, 6, and 10 returned.

Customer Table

RowID   CustID  Type    Ranking

-----   ------  ----    -------

1       9       Web      1
2       9       Catalog  2
3       9       Store    3
4       10      Store    1
5       11      Store    1
6       11      Web      2
7       12      Store    1
8       12      Web      2
9       12      Catalog  3
10      13      Web      1

I feel like this task is more difficult BECAUSE the Ranking is already done when the table is created! Any suggestions are most welcome!

回答1:

You could try something like this (I haven't tested it!):

SELECT
   RowId,
   CustId,
   Type,
   Ranking
FROM Customer c
WHERE (c.Ranking = 1 AND c.Type != 'Store')
   OR (c.Type = 'Store' AND Ranking = 2)
   OR (c.Type = 'Store' AND Ranking = 1 AND NOT EXISTS (SELECT 1 FROM Customer WHERE CustId = c.CustId AND Ranking = 2)) 

If the customer table is large, you might find that the query is a bit slow and something like this would be faster:

SELECT
   RowId,
   CustId,
   Type,
   Ranking
FROM Customer c
WHERE c.Ranking = 1 AND c.Type != 'Store'

UNION ALL

SELECT
   RowId,
   CustId,
   Type,
   Ranking
FROM Customer c
WHERE c.Type = 'Store' AND Ranking = 2

UNION ALL

SELECT
   RowId,
   CustId,
   Type,
   Ranking
FROM Customer c
WHERE c.Type = 'Store' AND Ranking = 1 AND NOT EXISTS (SELECT 1 FROM Customer WHERE CustId = c.CustId AND Ranking = 2)

回答2:

As with the other answer, I haven't done a lot of thorough testing, but here's what I'd look at. The idea here is to build a row_number over the data set prioritizing type:store to the top, and then using rank as the secondary sort condition.

select *
from (
select
    rid = row_number() over  (partition by CustID, order by case when type = 'Store' then 0 else 1 end, Rank desc),
    rowid,
    CustID,
    Type,
    Ranking
from customer)
where RID = 1

回答3:

Try:

SELECT *
FROM Customer c
WHERE
-- There's only one row for this customer
(
    SELECT COUNT(*)
    FROM Customer
    WHERE CustID = c.CustID
) = 1
-- There's a row with Ranking = 1 and Type = 'Store', so select Ranking = 2
OR (Ranking = 2 AND EXISTS (SELECT 1 FROM Customer WHERE CustID = c.CustID AND Ranking = 1 AND Type = 'Store'))
-- There's a row with Ranking = 1 that's not Type = 'Store'
OR (Ranking = 1 AND Type <> 'Store')