主页 / dba / 问题 / 102095
Accepted
M.I.M.F
Asked: 2015-05-21 13:30:56 +0800 CST

如何合并列和行中的相同值

  • 772

如何合并相同的 Stock 项目值并获取表中两个值的总和

这是查询：

select Invoice_TBL.InvoiceNo
    ,Invoice_TBL.ClientNo
    ,Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
    ,SUM(InvoiceItems_TBL.Quantity) as TotalQuantity
    ,InvoiceItems_TBL.SellingPrice
    ,SUM(InvoiceItems_TBL.TotalAmount) as TotalAmount
from Stock_TBL
inner join InvoiceItems_TBL on Stock_TBL.StockID = InvoiceItems_TBL.StockID
inner join Invoice_TBL on InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
where (Invoice_TBL.IsTableStatus = 1)
    and (InvoiceItems_TBL.IsTableStatus = 1)
    and (Stock_TBL.IsTableStatus = 1)
    and Invoice_TBL.ClientNo = 5 --AND (Invoice_TBL.ClientNo > 0)
group by Invoice_TBL.InvoiceNo
    ,Invoice_TBL.ClientNo
    ,Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
    ,InvoiceItems_TBL.SellingPrice
order by Invoice_TBL.ClientNo asc

结果是

在此处输入图像描述

但我需要这样显示（中表）：

在此处输入图像描述

从底部表格 402 Invoice No Merge with 335 Invoice No... 我不需要显示 Invoice No 列和 stock id 列...但是应该合并和添加相同的项目值（总量和总量）..我想要，每个库存项目中的一个客户在他/她的所有发票中花了多少（应该计算总数量和金额）

请帮助我...谢谢

sql-server-2008 t-sql

1 个回答

  1. Best Answer

    按照上面评论中的建议删除。这对你有用吗？

    select 
    Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
    ,SUM(InvoiceItems_TBL.Quantity) as TotalQuantity
    ,SUM(InvoiceItems_TBL.TotalAmount) as TotalAmount
from Stock_TBL
inner join InvoiceItems_TBL on Stock_TBL.StockID = InvoiceItems_TBL.StockID
inner join Invoice_TBL on InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
where (Invoice_TBL.IsTableStatus = 1)
    and (InvoiceItems_TBL.IsTableStatus = 1)
    and (Stock_TBL.IsTableStatus = 1)
    and Invoice_TBL.ClientNo = 5 --AND (Invoice_TBL.ClientNo > 0)
group by Invoice_TBL.ClientNo
    ,Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
order by Invoice_TBL.ClientNo asc
    • 0

相关问题