如果满足某些条件，如何选择或跳到下一行 [英] How to select or skip to the next row if meets certain criteria

问题描述

我真的不知道该往哪个方向发展……我正在尝试根据以下规则选择客户列表:

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:

• 从 Customer 中选择排名 = 1 的所有行，

• 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 Ranking = 1 AND Type = Store then Rank 1 and return the row with Rank 2.

OR 如果客户只有 1 行，即使 type = Store 也返回它.

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

未在查询中使用 Rank 语句分配排名.相反，它是 Customer 表中的一个实际列(由执行排名的存储过程填充).

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).

使用下面的示例，我希望返回第 1、4、6 和 10 行.

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

客户表

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!

推荐答案

你可以尝试这样的事情(我还没有测试过！):

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)

这篇关于如果满足某些条件，如何选择或跳到下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！