如何在参数更改方面在一个选择语句中获取数据?

Question

>我有这样的表格：

----------
name | surname
----------
abc  |  def
-----------
xxx  |  def
----------
yyy  |  def
----------
Comy |  Xardas
----------

例如，我声明：

declare @name varchar(10)
set @name = 'abc'

我需要获取姓氏等于@name's姓氏的所有名字。 如果@name为 null，则获取所有记录。在一选语句中

Answer 1

对列姓氏的同一表进行内部连接

SELECT *
FROM   table t1
INNER JOIN table t2 ON t1.surname = t2.surname 
WHERE  t1.name = COALESCE(@name, t1.name)

Answer 2

SELECT t1.*
FROM table t1
WHERE (ISNULL(@name,0) != 0 AND t1.surname = @name) OR (ISNULL(@name,0)=0)

Answer 3

DECLARE @MyTable TABLE (name varchar(20), surname varchar(20))
INSERT INTO @MyTable VALUES ('abc','def'),('xxx','def'),('yyy','def'), ('Comy','Xardas')
declare @name varchar(10)
set @name = 'abc'
SELECT T.* FROM 
@MyTable T
LEFT JOIN @MyTable T1 ON T.surname = T1.surname AND @name IS NOT NULL
WHERE (T.name = @name or @name IS NULL)

Answer 4

我认为你可以使用它：

declare @name varchar(10)
set @name = 'abc'
SELECT * 
FROM YT T1 
WHERE EXISTS (SELECT 1 FROM YT T2 WHERE name=@name AND T1.SURNAME=T2.SURNAME) OR @name IS NULL

示例数据

CREATE TABLE YT (name varchar(20), surname varchar(20));
INSERT INTO YT VALUES ('abc','def'),('xxx','def'),('yyy','def'), ('Comy','Xardas');

名称 = 空的输出：

name    surname
abc def
xxx def
yyy def
Comy    Xardas

名称 = 'abc' 的输出：

name    surname
abc def
xxx def
yyy def