如何写这个选择陈述 [英] How to write this select statment

问题描述

我有一张包含一些数据和两个键的表。 Key1 not null，Key2 null able

Key1& Key2对于每一行都应该是唯一的。我需要编写select语句，对于每个给定的key2，从匹配Key2的表中返回数据，或者Key2为NULL而不重复key1。因此，如果我有两个具有相同Key1的行，其中一个具有Key2而另一个具有key2为NULL，则我应该仅返回具有匹配Key2的行。 key2为NULL的行可能存在也可能不存在，同样适用于key2不为空的行

以下是示例：

I have a table with some data and two keys. Key1 not null, Key2 null able
Combination of Key1 & Key2 should be unique for every row. I need to write select statement that for every given key2 returning the data from table matching Key2 or where Key2 is NULL without duplicates of key1. So, if I have two rows with the same Key1 and one of them has Key2 and another where key2 is NULL I should only return row with matching Key2. Row where key2 is NULL may or may not exists, same goes for rows where key2 is not null
Here is the example:

Data	Key1	Key2
A	1	1
B	1	NULL
C	1	2
D	2	NULL
E	3	1

key2 = 1的结果>

Result for key2=1

Data	Key1	Key2
A	1	1
D	2	NULL
E	3	1

key2 = 2的结果

Result for key2 = 2

Data	Key1	Key2
C	1	2
D	2	NUL

结果key2 = 3

Result for key2 =3

Data	Key1	Key2
B	1	NULL
D	2	NULL

需要在用户定义的单一选择语句中使用将kye2作为参数的函数



我尝试过：



我已经尝试了几个语句，但是其中一个案例的每个人都失败了

非常感谢帮助这个

It needs to be in single select statement to be used inside user defined function that taking kye2 as parameter

What I have tried:

I have tried several statements, but everyone fails for one of the cases
Would appreciate help on this one

推荐答案

我把它分解成了这两个部分

1 - 列 key2 的所有内容都与参数匹配

2 - 任何事情（请注意更改在措辞中）列 key2 为NULL且列 key1 未出现在上述1的结果中



然后我将其转换为两个公用表表达式并UNIONed结果：

I broke this down into the two parts
1 - Everything where column key2 matches the parameter
2 - Anything (note the change in wording) where column key2 is NULL and column key1 does not appear in the results from 1 above

I then converted that into two Common Table Expressions and UNIONed the results:

;with CTE1 AS
(
	SELECT 'CTE1' AS SOURCE, *
	FROM @tmp WHERE Key2 = @key
)
, CTE2 AS
(
	SELECT 'CTE2' AS SOURCE, *
	FROM @tmp WHERE Key2 IS NULL
	AND Key1 NOT IN (SELECT Key1 FROM CTE1)
)
SELECT * FROM CTE1 UNION SELECT * FROM CTE2
ORDER BY [Data]

我收录了 SOURCE 专栏只是为了清楚数据实际来自哪里 - 你应该删除它。



在设置这个功能方面我的 @key 将是该函数的参数。如果你不熟悉这个函数，下面是一些关于如何设置函数的说明 - 实用示例的SQL Server表值函数 [ ^ ]



最后这里是我使用的测试数据，我需要回到这个问题:-)

I included the SOURCE column just for clarity on where the data was actually coming from - you should remove it.

In terms of setting this up as a function my @key would be the parameter to that function. Here are some instructions on how to set up a function if you are not familiar with that - SQL Server Table-Valued Function By Practical Examples[^]

Finally here is the test data I used, in case I need to come back to this question :-)

declare @tmp table ([Data] varchar(10),	Key1 int, Key2 int)
insert into @tmp ([Data], Key1, Key2) values
('A',1,	1), ('B',1,	NULL), ('C',1,	2), ('D',2,	NULL), ('E',3,	1)

declare @key int 

set @key = 3

如果按键1进行分组，然后按key2 desc进行排序，然后选择顶部每组一个？

What if you did group by key 1 and then order by key2 desc, and select the top one from each set?

这篇关于如何写这个选择陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！