从 T-SQL UNION 中的第二个查询引用第一个查询中的列

发布于 2024-11-27 17:01:13 字数 1058 浏览 4 评论 0原文

我有两个联合的 SELECT 语句，如下面的伪代码所示。

SELECT A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost
FROM someTable A
UNION
SELECT InvoiceNumberFromSomeTable, B.itemCode, B.itemDescription, ***IwantTheSumOfAllCostsInTheFirstQueryForThisInvoiceNumber*** as Cost
FROM yetAnotherTable B

预期结果是

InvoiceNumber    itemCode         itemDescription                           Cost
I001             A000001          This is Item01 From SomeTable             15
I001             A000002          This is Item02 From SomeTable             16
I001             Total            This is Total From YetAnotherTable        31
I002             A000001          This is Item01 From SomeTable             25
I002             B000002          This is Item99 From SomeTable             26
I002             Total            This is Total From YetAnotherTable        51

在我的情况下，第二个查询将始终返回一行，并且成本值应该是第一个查询中所有成本值的总和。

怎样才能做到这一点呢？我已经尝试了所有我熟悉的方法，但无法使其发挥作用。请帮助并感谢 SOF 社区。

编辑：我忘记添加发票号码列。每张发票都应进行总计。很抱歉没有早点添加这个。

原文

I have two SELECT statements that are UNIONed, as in the pseudo code below

SELECT A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost
FROM someTable A
UNION
SELECT InvoiceNumberFromSomeTable, B.itemCode, B.itemDescription, ***IwantTheSumOfAllCostsInTheFirstQueryForThisInvoiceNumber*** as Cost
FROM yetAnotherTable B

The expected result is

InvoiceNumber    itemCode         itemDescription                           Cost
I001             A000001          This is Item01 From SomeTable             15
I001             A000002          This is Item02 From SomeTable             16
I001             Total            This is Total From YetAnotherTable        31
I002             A000001          This is Item01 From SomeTable             25
I002             B000002          This is Item99 From SomeTable             26
I002             Total            This is Total From YetAnotherTable        51

In my case, the second query will always return one row and the Cost value should be the sum of all the Cost Values in first query.

How can one accomplish this? I've tried every approach I'm familiar with, but couldn't make it work. Please help and Thank you SOF community.

EDIT: I forgot to add the invoice number column. The totalling should happen for each invoice. Apologies for not adding this earlier.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抱着落日 2024-12-04 17:01:13

您可以做的就是将 SUM 作为额外列，

SELECT
    A.itemCode, A.itemDescription, A.Cost,
    SUM(A.Cost) OVER () AS SumCostA
FROM someTable A
UNION
SELECT B.itemCode, B.itemDescription,
    Whatever1, Whatever2 --NULLs?
FROM yetAnotherTable B

而让 AnotherTable itemCode 和 itemDescription 与另一个表中的数据相关并没有多大意义。 YetAnotherTable 中的 Cost 和 Sum(Cost) 怎么样？

YetAnotherTable 是父表还是类别表？如果是这样， JOIN （无 UNION ）会更有意义

编辑，更新后

SELECT
    A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost,
    SUM(A.Cost) OVER (PARTITION BY A.InvoiceNumber) AS SumCostAPerInvoice
FROM someTable A
UNION
SELECT InvoiceNumberFromSomeTable, B.itemCode, B.itemDescription,
    Whatever1, Whatever2 --NULLs?
FROM yetAnotherTable B

What you can do is to SUM as an extra column

SELECT
    A.itemCode, A.itemDescription, A.Cost,
    SUM(A.Cost) OVER () AS SumCostA
FROM someTable A
UNION
SELECT B.itemCode, B.itemDescription,
    Whatever1, Whatever2 --NULLs?
FROM yetAnotherTable B

It doesn't that much sense to have yetAnotherTable itemCode and itemDescription related to data from another table. What about Cost and Sum(Cost) from yetAnotherTable?

Is yetAnotherTable parent or category table? If so, a JOIN (no UNION) would make more sense

Edit, after update

SELECT
    A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost,
    SUM(A.Cost) OVER (PARTITION BY A.InvoiceNumber) AS SumCostAPerInvoice
FROM someTable A
UNION
SELECT InvoiceNumberFromSomeTable, B.itemCode, B.itemDescription,
    Whatever1, Whatever2 --NULLs?
FROM yetAnotherTable B

回复收藏 0 原文

迷雾森÷林ヴ 2024-12-04 17:01:13

SELECT InvoiceNumber, itemCode, itemDescription, Cost 
FROM 
(
    SELECT A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost, 1 dummy
    FROM someTable A
    UNION
    SELECT A.InvoiceNumber, 'Sub total', B.itemDescription, SUM(A.Cost), 2
    FROM yetAnotherTable B
    JOIN someTabel A ON A.InvoiceNumber = B.InvoiceNumberFromSomeTable
    GROUP BY A.InvoiceNumber, B.itemDescription
) a
ORDER BY InvoiceNumber,dummy

你也应该尝试这个。尽管它不会为您提供第二个表中的项目描述

http://msdn.microsoft .com/en-us/library/ms177673.aspx

SELECT InvoiceNumber, itemCode, itemDescription, Cost 
FROM 
(
    SELECT A.InvoiceNumber, A.itemCode, A.itemDescription, A.Cost, 1 dummy
    FROM someTable A
    UNION
    SELECT A.InvoiceNumber, 'Sub total', B.itemDescription, SUM(A.Cost), 2
    FROM yetAnotherTable B
    JOIN someTabel A ON A.InvoiceNumber = B.InvoiceNumberFromSomeTable
    GROUP BY A.InvoiceNumber, B.itemDescription
) a
ORDER BY InvoiceNumber,dummy

You should also try this. Although it will not give you the itemdescription from your second table

http://msdn.microsoft.com/en-us/library/ms177673.aspx

回复收藏 0 原文

~没有更多了~