具有INTERSECT，占位符值的多个SQL SELECT WHERE [英] Multiple SQL SELECT WHERE with INTERSECT, placeholder value

问题描述

我正在使用SQL Server，并尝试执行以下操作:

I'm using SQL Server and am trying to do the following:

SELECT dword FROM Details WHERE dskey = '51a'
INTERSECT
SELECT dword FROM Details WHERE dskey = '52b'
INTERSECT
SELECT dword FROM Details WHERE dskey = '53i'
INTERSECT
SELECT dword FROM Details WHERE dskey = '54d'
INTERSECT
SELECT dword FROM Details WHERE dskey = '55e';

这很好.但是，我需要构建一个通用的SELECT像这样:

This works fine. However, I need to build a generic SELECT like:

SELECT dword FROM Details WHERE dskey = value1
INTERSECT
SELECT dword FROM Details WHERE dskey = value2
INTERSECT
SELECT dword FROM Details WHERE dskey = value3
INTERSECT
SELECT dword FROM Details WHERE dskey = value4
INTERSECT
SELECT dword FROM Details WHERE dskey = value5;

但是，在任何给定的执行情况下，我可能都不会拥有全部五个键值.我需要某种方式来获得不会干扰他INTERSECTs的虚拟键值.

However, at any given execution I probably won't have all five key values. I need someway to have dummy key values that won't interfere with he INTERSECTs.

例如，假设我只有1个值，其余4个为null.但是，INTERSECT无法(正确)运行.

For example, say I have only 1 value, the remaining 4 are null. But the INTERSECT doesn't (correctly) work.

无论如何，有不会干扰INTERSECTS的伪值.

Is there anyway to have dummy values that won't interfere with the INTERSECTS.

我讨厌不得不做嵌套的ifs，如果我只有一个值，那么我只会执行一个SELECT.如果我有两个值，那么我有两个SELECTS，中间有INTERSECT，依此类推.

I hate to have to do nested ifs where if I only have one value I only perform one SELECT. If I have two values then I have two SELECTS with an intervening INTERSECT and so on.

这就是全部内容:

说我有一个5个字符的单词，我希望能够执行SELECT WHERE(选择位置)以返回说出所有在第3个位置带有"i"的5个字符的单词的列表.很简单.然后，我可能想选择第三个位置为"i"而第五个位置为"e"的位置.这类似于命运之轮".我可能拥有所有五个值a-b-i-d-e，因此仅应遵守返回的设置.因此，最好有一组5个SELECTS和四个插入的INTERSECTS，该构造可以处理1到5个值.

Say I have a 5 character word, I want to be able to do a SELECT WHERE to return a list of say all 5 character words that have 'i' in the third position. Easy enough. Then I may want to SELECT where the third position is 'i' and fifth is 'e'. This resembles "Wheel of Fortune". I may have all five values a-b-i-d-e so the returned set only should be abide. Hence, it would be nice to have one set of 5 SELECTS with four intervening INTERSECTS that this construct could handle 1 to 5 values.

我尝试了NULL值，但是显然不起作用.

I've tried NULL values and that clearly doesn't work as it shouldn't.

推荐答案

戈登(Gordon)像个吊饰一样工作！

Gordon this worked like a charm!

use wofwords;
go

DECLARE @value1 varchar(25) = '51a';
DECLARE @value2 varchar(25) = '52b';
DECLARE @value3 varchar(25) = NULL;
DECLARE @value4 varchar(25) = NULL;
DECLARE @value5 varchar(25) = NULL;


SELECT dword FROM Details WHERE dskey = @value1 or @value1 IS NULL
INTERSECT
SELECT dword FROM Details WHERE dskey = @value2 or @value2 IS NULL
INTERSECT
SELECT dword FROM Details WHERE dskey = @value3 or @value3 IS NULL
INTERSECT
SELECT dword FROM Details WHERE dskey = @value4 or @value4 IS NULL
INTERSECT
SELECT dword FROM Details WHERE dskey = @value5 or @value5 IS NULL;
go

它给了我正确的答案，并且是最好的，我现在可以编写c#部分并进行单个查询，而不是嵌套的ifs ...

It gives me the correct answers and best of all, I can now code up the c# part and have a single query instead of nested ifs...

Johnny

这篇关于具有INTERSECT，占位符值的多个SQL SELECT WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！