查询中的每一行可能有不同的条件吗？ [英] Is possible have different conditions for each row in a query?

问题描述

如何选择一组行，每行匹配不同的条件？

How I can select a set of rows where each row match a different condition?

示例：

假设我有一个名为name的表，仅当第一行名称匹配 A，第二行名称匹配 B且第三行名称匹配 C时，才需要结果'。

Supposing I have a table with a column called name, I want the result ONLY IF the first row name matches 'A', the second row name matches 'B' and the third row name matches 'C'.

编辑：

我想做到这一点而无需固定大小，但可以定义 R ， X ， V之类的序列， P ， T ，它与序列匹配，每个序列连续，但是

I want to do this to work without a fixed size, but in a way I can define the sequence like R,X,V,P,T and it matches the sequence, each one in a row, but in the order.

推荐答案

假设您知道如何为行提供行号（在SQL Server中为ROW_NUMBER（），例如），您可以创建一个查找（匹配）表并加入该表。参见下面的说明：

Assuming that You know how to provide a row number to your rows (ROW_NUMBER() in SQL Server, for instance), You can create a lookup (match) table and join on it. See below for explanation:

查询表：

RowNum    Value
1         A
2         B
3         C

您的SourceTable源表（假设您已经向其中添加了RowNum，以防万一，只需为其引入子查询（或SQL Server 2005或更高版本的CTE）：

Your SourceTable source table (assuming You already added RowNum to it-in case You didn't, just introduce subquery for it (or CTE for SQL Server 2005 or newer):

RowNum Name
-----------
1      A
2      B
3      C
4      D

现在，您需要将LookupTable与SourceTable内连接到 LookupTable.RowNum = SourceTable.RowNum LookupTable.Name = SourceTable.Name 。然后仅对RowNum的LookupTable进行此结果的左联接。如果存在 LookupTable.RowNum IS NULL 在最终结果中，那么您知道至少一行上没有完全匹配。

Now You need to inner join LookupTable with your SourceTable on LookupTable.RowNum = SourceTable.RowNum AND LookupTable.Name = SourceTable.Name. Then do a left join of this result with LookupTable on RowNum only. If there is LookupTable.RowNum IS NULL in final result then You know that there is no complete match on at least one row.

以下是联接的代码：

SELECT T.*, LT2.RowNum AS Matched 
FROM LookupTable LT2
LEFT JOIN 
(
    SELECT ST.*
    FROM SourceTable ST
    INNER JOIN LookupTable LT ON LT.RowNum = ST.RowNum AND LT.Name = ST.Name
) T
    ON LT2.RowNum = T.RowNum

上述查询的结果集将包含具有以下内容的行如果行与LookupTable表中的条件不匹配，则匹配的IS NULL 。

Result set of above query will contain rows with Matched IS NULL if row is not matching condition from LookupTable table.

这篇关于查询中的每一行可能有不同的条件吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！