组合查询SQL-Server

Question

我希望组合两个查询，只是把它放在这个结果的顶部。我不确定这是否可能。我目前正在通过一份报告进行工作，但该报告还远未得到优化。

查询#1：

;with cte as 
(
    SELECT
        tt.StepNo,
        o.PartNo,
        tt.WorkCntr,
        o.WorkCntr as owork,
        matl.WhereUsed as WhereUsed,
        tt.JobNo,
        tt.TicketDate,
        (tt.CycleTime + tt.SetupTime) * tt.ActualPayRate as ttLaborCost,
        (tt.CycleTime + tt.SetupTime) * tt.BurdenRate as mimicBurdenCost,
        tt.SetupTime as ttSetupTime,
        tt.CycleTime as ttCycleTime,
        tt.PiecesFinished,
        tt.PiecesScrapped,
        tt.ActualPayRate,
        tt.BurdenRate,
        o.SetupTime,
        o.TimeUnit,
        o.CycleTime,
        o.CycleUnit,
        o.MachRun as RoutMachRun,
        tt.MachRun,
        o.UnattendOp,
        od.DueDate,
        od.QtyToMake,
        (od.QtyToMake - od.QtyShipped2Stock) as NumOpen,
        CASE
        WHEN o.MachRun <> tt.MachRun Then 'Incorrect Ratio'
        ELSE ''
        END as IncorrectRatio,
        ROUND(cast (o.PctEff as FLOAT),2) as pctEff,
        ROUND(cast (o.ScrapPct as FLOAT),2) as scrapEff,
        (ROUND(cast((od.QtyToMake - od.QtyShipped2Stock) as FLOAT)/od.QtyToMake,3)) as Prorate,
        POTotalCost.POTotalCost,
        ROW_NUMBER() OVER(PARTITION BY tt.Stepno, tt.JobNo ORDER BY tt.StepNo, tt.TicketDate) as RowNum,
        ROW_NUMBER() OVER(PARTITION BY tt.JobNo ORDER BY tt.JobNo) as RowNum2


    FROM
        TimeTicketDet as tt

    LEFT JOIN
        OrderRouting as o
        on tt.JobNo = o.JobNo and tt.StepNo = o.Stepno
    LEFT JOIN
        OrderDet as od
        on tt.JobNo = od.JobNo and o.PartNo = od.PartNo

    LEFT JOIN
        (
            SELECT DISTINCT
                matl1.SubPartNo,
                STUFF((
                        SELECT ', ' + matl2.PartNo
                        FROM Materials as matl2
                        WHERE matl2.SubPartNo = matl1.SubPartNo
                        FOR XML PATH('')
                        ),1,1,'') as WhereUsed
            FROM
                Materials as matl1
        ) matl
        ON matl.SubPartNo = o.PartNo

--DELETED LEFT JOIN--



    WHERE
        od.Status = 'Open'

)

SELECT *

***WHERE CLAUSE DELETED FOR EASE OF READING***


FROM cte

查询#2:我想在查询1的“顶部”添加的查询是：

SELECT
    JobNo,
    CASE
        WHEN StockUnit = 'LOT' 
           THEN SUM(StockingCost * 1) 
           ELSE SUM(StockingCost * QtyPosted1)
    END as TotalCost
FROM 
    JobMaterials
GROUP BY 
    JobNo, StockUnit

单独查询的结果：

+----------------------+
| Results from query 2 |
+----------------------+
|                12345 |
|                12345 |
+----------------------+

+----------------------+
| Results from query 1 |
+----------------------+
|                67890 |
|                67890 |
|                67890 |
+----------------------+

所需输出：

+------------------+
| Combined Results |
+------------------+
|            12345 |
|            12345 |
|            67890 |
|            67890 |
|            67890 |
+------------------+

当我尝试在查询1中使用左连接时的结果：

+------------------+
| Combined Results |
+------------------+
|            12345 |
|            12345 |
|            67890 |
|            12345 |
|            12345 |
|            67890 |
|            12345 |
|            12345 |
|            67890 |
+------------------+

我知道数据非常模糊，如果不使用真实的数据就很难解释，而且输出结果会吃光一个JobNo的33k字符限制。我看过很多组合帖子，要么它们不适合我，要么我没有正确地执行它们(可能是后者)。在SQL方面，我仍然是一个超级新手，所以，对我好一点。

Answer 1

如果结果列具有相同的数据类型和名称，则可以执行以下操作

 <query 1>

 UNION ALL

 <query 2>

这将“把它们放在一起”。

注意，您不能保证获得订单，因此您可能希望执行以下操作

 SELECT A, B, C
 FROM (

     SELECT A, B, C, 1 AS Ord
     FROM -- rest of query

     UNION ALL

     SELECT A, B, C, 2 AS Ord
     FROM -- rest of query

 ) AS sub
 ORDER BY Ord ASC

Answer 2

你要找的是一个联盟。

SELECT column FROM Table WHERE key = 1
UNION 
SELECT column FROM Table WHERE key = 2

将给出一个结果，其中显示了表中键1和键2的结果。

您还可以查找其他术语INTERSECT和EXCEPT (或减号)。INTERSECT将为您提供两个查询中匹配的所有行。EXCEPT将给出第一个查询的所有结果，而这些结果在第二个查询中并不存在。

因此，如果我们的两个结果集是A{1,2,3,4,5}和B{1,3,5,7,9}。

A并B= {1,2,3,4,5,7,9}

交集B= {1,3,5}

除B外的A(或A减去B) = {2,4}

其他需要记住的事情，UNION和INTERSECT是交换的。所以(A并B) = (B并A)和(A相交B=B相交A)。这不是EXCEPT的情况。再次使用上面的示例：

除B外的A(或A减去B) = {2,4}

B除A(或B减去A) = {7,9}