来自两个访问查询的SQL子查询

Question

我想使用子查询将两个查询合并成一个查询，但我无法找到创建子查询的正确语法。

查询B是查询A正常工作所需引用的查询。

任何帮助都是有帮助的，因为我刚刚开始接受Transact-SQL的教育。

-这两个查询正在从Access迁移为单独的查询--

查询A：

SELECT Shipment.[Shipment Description], Shipment.[Load ID], Shipment.[Origin Name], Shipment.[Origin City], Shipment.[Origin State], Shipment.[Origin Zip], Shipment.[Origin Country], Shipment.[Destination Name], TMS_Shipment.[Destination State], Shipment.[Destination City], Shipment.[Destination Zip], Shipment.[Destination Country], Shipment.[Pickup To Date/Time], Shipment_Container.Pallets, Shipment_Container.Pieces, [QUERY B].[SumOfReference Number] AS 'Original Number of Pieces', Shipment_Container.Length, Shipment_Container.Width, Shipment_Container.Height, Shipment_Container.[Scaled Weight], Shipment_Container.[Stackability Indicator], Month([Shipment].[Pickup To Date/Time]) AS [Month], Year([Shipment].[Pickup To Date/Time]) AS [Year], [Shipment_Container].[Scaled Weight]/42000 AS [Weight Utilization],Round((100/[Width]),0) AS [# Wide], Round(([QUERY B].[SumOfReference Number]/(Round((100/[Width]),0)))/[Shipment_Container].[Stackability Indicator],0) AS [# Long], Load.[Service Code], (((Round(([QUERY B].[SumOfReference Number]/(Round((100/[Width]),0)))/[Shipment_Container].[Stackability Indicator],0))*[Shipment_Container].[Length])/(629*0.85)) AS Cube, Shipment.[Party Responsible for Freight cost], Load.[Number of Stops]
    Into Qry_Utilization
FROM (Load INNER JOIN (Shipment_Container INNER JOIN Shipment ON Shipment_Container.[Shipment Description] = Shipment.[Shipment Description]) ON Load.[Load ID] = Shipment.[Load ID]) INNER JOIN [QUERY B] ON Shipment_Container.[Shipment Description] = [QUERY B].[Shipment Description]
WHERE (((Shipment_Container.Length)>1) AND ((Shipment_Container.Width)>1) AND ((Shipment_Container.Height)>1) AND ((Load.[Service Code])='TL' Or (Load.[Service Code])='SPTL' Or (Load.[Service Code])='SPFB' Or (Load.[Service Code])='TLMR'));

查询B：

(SELECT Shipment_Container_Reference.[Shipment Description], Shipment_Container_Reference.[Reference Type Desc], Sum(Shipment_Container_Reference.[Reference Number]) AS [SumOfReference Number]
FROM Shipment_Container_Reference
GROUP BY Shipment_Container_Reference.[Shipment Description], Shipment_Container_Reference.[Reference Type Desc]
HAVING (((Shipment_Container_Reference.[Reference Type Desc]) Like '*number of pieces*')))

Answer 1

MSAccess，至少在我使用它的时候，没有正确地支持子查询，所以您必须像您已经展示的那样做一些事情；在大多数情况下，将它转换为SQL只是一个更改类似于

SELECT stuff 
FROM TableA 
INNER JOIN QueryB ON blah

至

SELECT stuff 
FROM TableA 
INNER JOIN (
   SELECT other_stuff 
   FROM TableB 
   WHERE blahB
) AS QueryB ON blah`

除此外

• 您需要将任何*通配符转换为%通配符
• 如果您确实在使用MySQL (正如您当前的标记所建议的那样)，则需要用`(未移动的~键)替换[和]字段分隔符。

Answer 2

当您需要执行一个查询(本例中为B)，然后在另一个查询上使用其结果(本例中为A)时，SQL标准将为您提供通用表表达式(CTE)。

在您的示例中，查询(与CTE一起)应该采用以下形式：

with b as (
  select ... -- all your SQL select here
)
select ... from a join b ... -- note that here you can use any table, as well as B

在您的例子中(添加了一些格式)：

with b as
(
    SELECT
      Shipment_Container_Reference.[Shipment Description],
      Shipment_Container_Reference.[Reference Type Desc],
      Sum(Shipment_Container_Reference.[Reference Number]) AS [SumOfReference Number]
    FROM Shipment_Container_Reference
    GROUP BY Shipment_Container_Reference.[Shipment Description],
      Shipment_Container_Reference.[Reference Type Desc] HAVING
      (
          (
              (
                  Shipment_Container_Reference.[Reference Type Desc]
              )
              Like '*number of pieces*'
          )
      )
  )
SELECT
  Shipment.[Shipment Description],
  Shipment.[Load ID],
  Shipment.[Origin Name],
  Shipment.[Origin City],
  Shipment.[Origin State],
  Shipment.[Origin Zip],
  Shipment.[Origin Country],
  Shipment.[Destination Name],
  TMS_Shipment.[Destination State],
  Shipment.[Destination City],
  Shipment.[Destination Zip],
  Shipment.[Destination Country],
  Shipment.[Pickup To Date/Time],
  Shipment_Container.Pallets,
  Shipment_Container.Pieces,
  [QUERY B].[SumOfReference Number] AS 'Original Number of Pieces',
  Shipment_Container.Length,
  Shipment_Container.Width,
  Shipment_Container.Height,
  Shipment_Container.[Scaled Weight],
  Shipment_Container.[Stackability Indicator],
  Month([Shipment].[Pickup To Date/Time]) AS [Month],
  Year([Shipment].[Pickup To Date/Time]) AS [Year],
  [Shipment_Container].[Scaled Weight]/42000 AS [Weight Utilization],
  Round((100/[Width]),0) AS [# Wide],
  Round(([QUERY B].[SumOfReference Number]/(Round((100/[Width]),0)))/[Shipment_Container].[Stackability Indicator],0) AS [# Long],
  Load.[Service Code],
  (((Round(([QUERY B].[SumOfReference Number]/(Round((100/[Width]),0)))/[Shipment_Container].[Stackability Indicator],0))*[Shipment_Container].[Length])/(629*0.85)) AS Cube,
  Shipment.[Party Responsible for Freight cost],
  Load.[Number of Stops] Into Qry_Utilization
FROM
  (
      Load
      INNER JOIN
      (
          Shipment_Container
          INNER JOIN Shipment ON Shipment_Container.[Shipment Description] = Shipment.[Shipment Description]
      )
      ON Load.[Load ID] = Shipment.[Load ID]
  )
  INNER JOIN [QUERY B] ON Shipment_Container.[Shipment Description] = [QUERY B].[Shipment Description]
WHERE
  (
      ((Shipment_Container.Length)>1)
      AND ((Shipment_Container.Width)>1)
      AND ((Shipment_Container.Height)>1)
      AND
      (
          (
              Load.[Service Code]
          )
          ='TL' Or
          (
              Load.[Service Code]
          )
          ='SPTL' Or (Load.[Service Code])='SPFB' Or (Load.[Service Code])='TLMR'
      )
  )
  ;

请注意，这个查询在Transact-SQL中不是100%正确的，因为它仍然有一些MS-Access (非标准的)怪癖。