SQL当前查询条件数据需要调用其他数据时创建临时表实现

BEGIN

IF OBJECT_ID('tempdb..#TB') IS NOT NULL

DROP TABLE #TB

CREATE TABLE #TB(
WorkcenterId CHAR(12),
MOId CHAR(12),
MOName NVARCHAR(50),
WorkcenterName NVARCHAR(50),
MODescription NVARCHAR(50),
ProductId CHAR(12),
ProductDescription NVARCHAR(200)
)

INSERT INTO #TB    (
WorkcenterId,
MOId,
MOName,
WorkcenterName,
MODescription,
ProductId,
ProductDescription
)
SELECT m.WorkcenterId,m.MOId,m.MOName,w.WorkcenterName,m.MODescription,m.ProductId,pd.ProductDescription FROM  dbo.MO m 
    RIGHT OUTER JOIN dbo.Product pd ON pd.ProductId=m.ProductId
    RIGHT OUTER JOIN dbo.ProductRoot pr ON  pr.ProductRootId=pd.ProductRootId
    RIGHT OUTER JOIN dbo.Workcenter w ON w.WorkcenterId=m.WorkcenterId
    WHERE 
    --w.WorkcenterName='1030001'
        --MOName like '%'+@I_FiltCondition+'%'
          MOQtyRequired > ISNULL(MOQtyDone,0)
            AND m.MOStates NOT IN ('10','40','31','41','60')
            --AND m.WorkcenterId IN ('WKC1000001IK','WKC1000001IL')
            AND m.WorkcenterId=w.WorkcenterId
            ORDER BY MOId DESC 
INSERT INTO #TB
(
    WorkcenterId,
    MOId,
    MOName,
    WorkcenterName,
    MODescription,
    ProductId,
    ProductDescription
)
SELECT 'WKC1000001IO',m.MOId,m.MOName,'1030001',m.MODescription,m.ProductId,pd.ProductDescription FROM  dbo.MO m 
    RIGHT OUTER JOIN dbo.Product pd ON pd.ProductId=m.ProductId
    RIGHT OUTER JOIN dbo.ProductRoot pr ON  pr.ProductRootId=pd.ProductRootId
    RIGHT OUTER JOIN dbo.Workcenter w ON w.WorkcenterId=m.WorkcenterId
    WHERE 
    --w.WorkcenterName='1030001'
        --MOName like '%'+@I_FiltCondition+'%'
          MOQtyRequired > ISNULL(MOQtyDone,0)
            AND m.MOStates NOT IN ('10','40','31','41','60')
            AND m.WorkcenterId  ='WKC1000001IM'
            AND m.WorkcenterId=w.WorkcenterId
            ORDER BY MOId DESC 
INSERT INTO #TB
(
    WorkcenterId,
    MOId,
    MOName,
    WorkcenterName,
    MODescription,
    ProductId,
    ProductDescription
)
SELECT 'WKC1000001IP',m.MOId,m.MOName,'1030002',m.MODescription,m.ProductId,pd.ProductDescription FROM  dbo.MO m 
    RIGHT OUTER JOIN dbo.Product pd ON pd.ProductId=m.ProductId
    RIGHT OUTER JOIN dbo.ProductRoot pr ON  pr.ProductRootId=pd.ProductRootId
    RIGHT OUTER JOIN dbo.Workcenter w ON w.WorkcenterId=m.WorkcenterId
    WHERE 
    --w.WorkcenterName='1030001'
        --MOName like '%'+@I_FiltCondition+'%'
          MOQtyRequired > ISNULL(MOQtyDone,0)
            AND m.MOStates NOT IN ('10','40','31','41','60')
            AND m.WorkcenterId  ='WKC1000001IN'
            AND m.WorkcenterId=w.WorkcenterId
            ORDER BY MOId DESC 

SELECT * FROM #TB

DROP TABLE #TB

END