如何筛选嵌套查询中的记录，然后从筛选的结果中选择同一表中的其他列？

Question

首先，我希望在两个嵌套查询(基于用户输入的参数@Borough和@WOEntity)中筛选并只选择OBJECTIDS，然后将同一表中的列(Type和优先级)连接回最精细的记录集。这样做的目的是提高SSRS报告后面存储过程的性能。

我收到了这些错误信息：

Msg 156，15级，状态1，13线 关键字“SELECT”附近的不正确语法 Msg 102，15级，状态1，36线 不正确的语法靠近')‘

到目前为止，我的查询如下：

declare @Borough int
declare @WOEntityINT int = NULL --0, 1, 3, 4, 11, 10, NULL

set @Borough = 1
set @WOEntityINT = 1


SELECT 
    B.OBJECTID, WO.Type, WO.Priority
FROM
    CFAdmin.WorkOrder_EVW as WO
WHERE 
    B.OBJECTID = WO.OBJECTID

(
SELECT A.ObjectID
FROM
    (SELECT
         ObjectID,
         CASE 
            WHEN WOEntity = 0 THEN 0 -- In-House
            WHEN WOEntity IN (1, 2) THEN 1 -- Contract
            WHEN WOEntity IN (3, 4) THEN 3 -- Utility 
            WHEN WOEntity IN (5, 6) THEN 4 -- Permitted
            WHEN WOEntity IS NULL THEN 10 -- No Entity
            ELSE 11 --11 = Other
         END AS WOEntityINT                                         
     FROM 
         CFAdmin.WorkOrder_EVW
     WHERE
         (Status NOT IN (1, 2)) AND
         (Borough IN (@Borough))) AS A
 WHERE 
     (@WOEntityINT IS NULL OR (WOEntityINT = @WOEntityINT)) --field WOEntityINT is a derived field 
 ) AS B 

Answer 1

您的查询必须与模式：SELECT FROM WHERE匹配。我是说，你可以重新订购条款：

SELECT B.OBJECTID, WO.Type, WO.Priority
FROM CFAdmin.WorkOrder_EVW as WO
INNER JOIN (
  SELECT A.ObjectID
  FROM
    (
    SELECT
        ObjectID,
            CASE 
                WHEN WOEntity = 0 THEN 0 -- In-House
                WHEN WOEntity IN (1,2) THEN 1 -- Contract
                WHEN WOEntity IN (3,4) THEN 3 -- Utility 
                WHEN WOEntity IN (5,6) THEN 4 -- Permitted
                WHEN WOEntity IS NULL THEN 10 -- No Entity
                ELSE 11 --11 = Other
            END AS WOEntityINT                                          

        FROM CFAdmin.WorkOrder_EVW
        WHERE
            (Status not in (1,2)) AND
            (Borough IN (@Borough)) 

     ) AS A

   WHERE (@WOEntityINT IS NULL OR (WOEntityINT = @WOEntityINT)) --field 
   WOEntityINT is a derived field 
 ) AS B
 on B.OBJECTID = WO.OBJECTID

记住，您可以编写CTE以提高可读性：

WITH a AS 
( 
       SELECT objectid, 
              CASE 
                     WHEN woentity = 0 THEN 0      -- In-House 
                     WHEN woentity IN (1,2) THEN 1 -- Contract 
                     WHEN woentity IN (3,4) THEN 3 -- Utility 
                     WHEN woentity IN (5,6) THEN 4 -- Permitted 
                     WHEN woentity IS NULL THEN 10 -- No Entity 
                     ELSE 11                       --11 = Other 
              END AS woentityint 
       FROM   cfadmin.workorder_evw 
       WHERE  ( 
                     status NOT IN (1,2)) 
       AND    ( 
                     borough IN (@Borough)) )
, b AS 
( 
       SELECT a.objectid 
       FROM   a 
       WHERE  ( 
                     @WOEntityINT IS NULL 
              OR     ( 
                            woentityint = @WOEntityINT)) --field 
              woentityint IS a derived field 
) 
SELECT     b.objectid, 
           wo.type, 
           wo.priority 
FROM       cfadmin.workorder_evw AS wo 
INNER JOIN b 
ON         b.objectid = wo.objectid

免责声明:这只是一个开始调试和隔离您的问题的建议。我想，在这个变化之后，你会发现新的问题。请不要在这篇文章中要求新的问题。