sql server语言 - JOIN中sql查询优化问题

Question

我在一个查询中有多个JOIN语句，我必须检查JOIN中的两个设置。怎样才能做到最好呢?

table: WebSettings
-------------------------------------------------------
Name            | Setting   | InstID    | WebSettingID
-------------------------------------------------------
'EnableError'   | 2 | 1111  | 1
'ErrorsSetting' | 0 | 2121  | 2
Index :
InstID
WebSettingID (InstID)

table: InstProd
-------------------------------------------------------
InstitutionID   | Name
-------------------------------------------------------
1111        | 'Bank of Ind'
2121        | 'IOB'

Index :
InstitutionID

SELECT 
Column1,
DATEADD(.....)
FROM 
dbo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON
WS1.InstitutionID = I.InstID AND
WS1.Name = 'EnableError' AND WS1.Setting=2
INNER JOIN dbo.WebSettings WS2 ON
WS2.InstitutionID = I.InstID AND
WS2.Name = 'ErrorsSetting' AND WS2.Setting=0
WHERE.....

我想检查JOIN中的一个WebSettings = 2和other = 0

有最好的解决方案吗?

Answer 1

您可以决定不使用JOIN，作为我的以下解决方案

如果您想要所有InstProd与EnableError = 2和ErrorSettings = 0

试试这个:

SELECT I.*
FROM InstProd I
WHERE EXISTS
    (SELECT 'EnableError = 2'
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'EnableError' AND WS.Setting = 2)
AND EXISTS
   (SELECT 'EnableError = 2'
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'ErrorSettings' AND WS.Setting = 0)

如果你想让所有的InstProd检查两个设置值

试试这个:

SELECT I.*,
CASE
    WHEN
        (SELECT COUNT(1)
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'EnableError' AND WS.Setting = 2) > 0
        AND
        (SELECT COUNT(1)
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'ErrorSettings' AND WS.Setting = 0) > 0
  THEN 'OK'
  ELSE 'KO'
END
FROM InstProd I

Answer 2

是的，你不需要加入WebSettings两次，虽然你没有发布你的预期输出，所以我不知道该怎么做，但调整这个:

SELECT <column1>,<column2>,
       MAX(CASE WHEN ws.Name = 'EnableError' AND ws.settings = 2 THEN <YourDesiredColumn> END) as enable_column,
       MAX(CASE WHEN ws.Name = 'ErrorsSetting' AND ws.settings = 0 THEN <YourDesiredColumn2> END) as Errors_column
FROM bo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON
WS1.InstitutionID = I.InstID AND
(WS.Name,ws.setting) in (('EnableError',2),('ErrorsSetting',0)) 
GROUP BY <column1>,<column2>

这将在同一行中提供enable和error列，就像您的查询一样。

Answer 3

如果您不使用WS2，则最小化JOINS可能会得到您期望的结果

SELECT 
 Column1,
 DATEADD(.....)
FROM
dbo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON WS1.InstitutionID = I.InstID 
                               AND ( WS1.Name = 'EnableError' 
                                     AND WS1.Setting = 2 )
                               OR ( WS1.Name = 'ErrorsSetting' 
                                    AND WS1.Setting = 0)  
WHERE.....