如何合并来自同一个表的两个GROUPBY查询？

d8tt03nd 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(382)

我需要将来自同一个表的两个查询合并到一个表中：

SELECT

       t.[Origin Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Inbound Shipments',

       SUM(t.[Total Revenue]) as 'Inbound Revenue'

  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  GROUP BY t.[Origin Terminal Name]

  ORDER BY t.[Origin Terminal Name] ASC

  SELECT

       t.[Destination Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Outbound Shipments',

       SUM(t.[Total Revenue]) as 'Outbound Revenue'

  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  GROUP BY t.[Destination Terminal Name]

  ORDER BY t.[Destination Terminal Name] ASC

它们都产生了以下结果：

Terminal    Inbound Shipments   Inbound Revenue
Abilene            520             75970.08
Albany             877            130560.28

Terminal    Outbound Shipments  Outbound Revenue
Abilene             2684           419523.83
Albany              2820           407871.89

我需要这个作为输出：

Terminal    Inbound Shipments  Inbound Revenue  Outbound Shipments  Outbound Revenue
Abilene            520              75970.08           2684              419523.83
Albany             877              130560.28          2820              407871.89

我尝试了以下查询：

SELECT 

       t.[Origin Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Inbound Shipments',

       SUM(t.[Total Revenue]) as 'Inbound Revenue',

            (SELECT

            t2.[Destination Terminal Name] as 'Terminal',

            COUNT(t2.[Pro Number]) as 'Outbound Shipments',

            SUM(t2.[Total Revenue]) as 'Outbound Revenue'

            FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t2

            GROUP BY t2.[Destination Terminal Name])

  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  LEFT JOIN [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t2 ON t.[Origin Terminal Name] = t2.[Destination Terminal Name]

  GROUP BY t.[Origin Terminal Name]

  ORDER BY t.[Origin Terminal Name] ASC

我收到这个错误：
msg 116，level 16，state 1，line 22当子查询没有引入exists时，只能在select列表中指定一个表达式。
子查询是解决这个问题的方法吗？我很感激你的建议

sql sql-server unpivot tsql ssms

来源：https://stackoverflow.com/questions/62519440/how-to-merge-two-group-by-queries-from-the-same-table

2条答案

按热度按时间

a1o7rhls1#

如果我跟对了，你可以用 cross apply 要取消将列拆分为行，然后进行聚合，请执行以下操作：

SELECT
    x.terminal,
    SUM(CASE WHEN x.which = 'inbound'  THEN 1 ELSE 0 END) as inbound_shipments,
    SUM(CASE WHEN x.which = 'inbound'  THEN x.total_revenue ELSE 0 END) as inbound_revenue,
    SUM(CASE WHEN x.which = 'outbound' THEN 1 ELSE 0 END) as outbound_shipments,
    SUM(CASE WHEN x.which = 'outbound' THEN x.total_revenue ELSE 0 END) as outbound_revenue 
FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t
CROSS APPLY (VALUES 
    ('inbound',  [Origin Terminal Name],      [Total Revenue]),
    ('outbound', [Destination Terminal Name], [Total Revenue])
) AS x(which, is_destination, terminal, total_revenue)
GROUP BY x.terminal
ORDER BY x.terminal

赞(0）回复(0）举报 2021-07-24

velaa5lx2#

SELECT

       t.[Origin Terminal Name] as 'Terminal',

       COUNT(t.[Pro Number]) as 'Inbound Shipments',

       SUM(t.[Total Revenue]) as 'Inbound Revenue',

       max(t2.[Outbound Shipments]) as 'Outbound Shipments',

       max(t2.[Outbound Revenue]) as 'Outbound Revenue'

  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t

  INNER JOIN  (SELECT

       t2.[Destination Terminal Name] as 'Terminal',

       COUNT(t2.[Pro Number]) as 'Outbound Shipments',

       SUM(t2.[Total Revenue]) as 'Outbound Revenue'

  FROM [AX2cTestStage].[dbo].[AX2cTestAdapter_dbo_AAATRANSPORTTABLE_V] t2

  GROUP BY t2.[Destination Terminal Name]) t2 on t.[Origin Terminal Name] = t2.Terminal

  GROUP BY t.[Origin Terminal Name]

  ORDER BY t.[Origin Terminal Name] ASC

赞(0）回复(0）举报 2021-07-24

我来回答

如何合并来自同一个表的两个GROUPBY查询？

2条答案

相关问题

热门标签

最新问答