代码之家 › 专栏 › 技术社区 › Bradley

SQL:如何返回一列,该列是另一列上返回的行的集合?

sql

Bradley · 技术社区 · 6 年前

我有一张公司和发票的表格。我的查询汇总每个公司发票的总值,以按公司提供“总销售额”。我想返回的是与同一查询中的其他公司相比,每个公司销售额的正负号。

Select Company.name, sum(Invoice.total) as InvoiceTotal
From Company
Inner Join Invoice on Invoice.CompanyId = Company.Id
Group By Company.name

假设上述查询产生:

Company          InvoiceTotal
A                100
B                200
C                600

我希望附加列提供超出或低于InvoiceTotal列平均值的金额:

Company          InvoiceTotal    Difference
A                100             -200
B                200             -100
C                600             300

如何在单个查询中提取该值?

4 回复 | 直到 6 年前

EzLo tumao kaixin 6 年前

如果你的数据库可以处理窗口平均数(大多数DO),你可以使用一个窗口平均函数,它应用于发票金额,然后用发票金额做其余的计算。

以下是SQL Server的一个示例:

;WITH Data AS
(
    SELECT
        *
    FROM
        (
        VALUES
            ('a', 50),
            ('a', 25),
            ('a', 25),

            ('b', 125),
            ('b', 75),

            ('c', 275),
            ('c', 50),
            ('c', 75),
            ('c', 200)) V(Company, Invoice)
)
SELECT
    Company = V.Company,
    Invoice = SUM(V.Invoice),
    AverageAcrossAllCompanies = AVG(SUM(V.Invoice)) OVER (),
    AverageInvoiceDifference = SUM(V.Invoice) - AVG(SUM(V.Invoice)) OVER ()
FROM
    Data AS V
GROUP BY
    V.Company

结果:

Company Invoice AverageAcrossAllCompanies   AverageInvoiceDifference
a       100     300                         -200
b       200     300                         -100
c       600     300                         300

Sudipta Mondal 6 年前

假设您的数据库支持CTE

with total as (
    select avg(invoicetotal) totalsum
from table_name)
    select t.company, 
           t.invoicetotal, 
           t.invoicetotal - total.totalsum
from table_name t, total

Company INVOICETOTAL    T.INVOICETOTAL-TOTAL.TOTALSUM
A        100            -200
B        200            -100
C        600             300

Eponyme Web 6 年前

这里有另一种方法可以得到你想要的方差

Select
    name,
    InvoiceTotal,
    InvoiceTotal - avg(InvoiceTotal) as Difference
From
    (Select Company.name, sum(Invoice.total) as InvoiceTotal
    From Company
    Inner Join Invoice on Invoice.CompanyId = Company.Id
    Group By Company.name)
group by name, InvoiceTotal

M. Arnold 6 年前

结果可以使用以下查询来实现:

Select Company.company, sum(Invoice.total) as InvoiceTotal, (SELECT AVG(Invoice.total) FROM Invoice) AS InvoiceAvg,(SELECT AVG(Invoice.total) FROM Invoice) -sum(Invoice.total) AS Difference
From Company
Inner Join Invoice on Invoice.CompanyId = Company.Id
Group By Company.company