Question

我有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

是否有最佳办法实现这一目标?

Thanks you in advance, Stev

Answer 1

这应该通过在被点击后停止挑选,而最糟糕的是,在其中无一例的情况下,选择仍然会停止。

注:假定服务器(参考代码<>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是一个捷径,以防止其扫描整个表,如果“代码>NO_INSEE没有编入索引,该表是大的,那么该表将停止第一次打,继续扫描整个表。

Answer 2

您可以删除所有<代码>@CountX变量,并做到如下:

(SQL EXISTS()。有效检查至少单行的出行和立即返回,这样速度就会加快。因为在发现第一行时撤离

SELECT @Zone = CASE WHEN EXISTS (
                           SELECT * FROM LS_CLIENT_INSEE_A 
                           WHERE NO_INSEE = @TheInsee 
                           )
                THEN  A  END

...

友情链接