我有6个表:LS_CLIENT_INSEE_A, B, C等。每个表只包含1个字段:INSEE.
我只是想知道我的输入是否存在于其中一个表中,并返回表名中相应的字母(A,B,C等)。
还有其他原因吗?
@TheInsee int
AS
BEGIN
declare @Zone char(1)
declare @CountA int
declare @CountB int
declare @CountC int
declare @CountD int
declare @CountF int
declare @CountP int
SELECT @CountA = COUNT(*) FROM LS_CLIENT_INSEE_A WHERE NO_INSEE = @TheInsee
SELECT @CountB = COUNT(*) FROM LS_CLIENT_INSEE_B WHERE NO_INSEE = @TheInsee
SELECT @CountC = COUNT(*) FROM LS_CLIENT_INSEE_C WHERE NO_INSEE = @TheInsee
SELECT @CountD = COUNT(*) FROM LS_CLIENT_INSEE_D WHERE NO_INSEE = @TheInsee
SELECT @CountF = COUNT(*) FROM LS_CLIENT_INSEE_F WHERE NO_INSEE = @TheInsee
SELECT @CountP = COUNT(*) FROM LS_CLIENT_INSEE_P WHERE NO_INSEE = @TheInsee
set @Zone =
CASE
WHEN @CountA > 0 THEN 'A'
WHEN @CountB > 0 THEN 'B'
WHEN @CountC > 0 THEN 'C'
WHEN @CountD > 0 THEN 'D'
WHEN @CountF > 0 THEN 'F'
WHEN @CountP > 0 THEN 'P'
END
END
上面的查询是实现这一目标的最佳方式吗?
提前谢谢你,Stev
这应该通过停止选择一旦它发现命中,最坏的情况仍然是当它不在其中
注意:假设SQL Server(区别是TOP 1
与LIMIT 1
):
@TheInsee int
AS
BEGIN
declare @Zone char(1)
SELECT @Zone =
CASE
WHEN (SELECT TOP 1 1 FROM LS_CLIENT_INSEE_A WHERE NO_INSEE = @TheInsee) > 0 THEN 'A'
WHEN (SELECT TOP 1 1 FROM LS_CLIENT_INSEE_B WHERE NO_INSEE = @TheInsee) > 0 THEN 'B'
WHEN (SELECT TOP 1 1 FROM LS_CLIENT_INSEE_C WHERE NO_INSEE = @TheInsee) > 0 THEN 'C'
WHEN (SELECT TOP 1 1 FROM LS_CLIENT_INSEE_D WHERE NO_INSEE = @TheInsee) > 0 THEN 'D'
WHEN (SELECT TOP 1 1 FROM LS_CLIENT_INSEE_F WHERE NO_INSEE = @TheInsee) > 0 THEN 'F'
WHEN (SELECT TOP 1 1 FROM LS_CLIENT_INSEE_P WHERE NO_INSEE = @TheInsee) > 0 THEN 'P'
END
END
TOP 1 1
是一个快捷方式,以防止它扫描整个表,这是很重要的,如果NO_INSEE
没有索引,表很大,它将停止当它得到第一个命中,而不是继续扫描整个表。
您可以删除所有@CountX
变量,并执行以下操作:
(SQL EXISTS()有效地检查是否至少有单行退出并立即返回,因此它将比COUNT(*)更快,因为当找到第一行时退出)
SELECT @Zone = CASE WHEN EXISTS (
SELECT * FROM LS_CLIENT_INSEE_A
WHERE NO_INSEE = @TheInsee
)
THEN 'A' END
...