时间戳

首页 > 解决方案 > 在 SQL Server 中使用联接的正确方法

sql - 在 SQL Server 中使用联接的正确方法

问题描述

我的数据库中有四个表，如下所示

  1. Transactions区分买卖交易的表格
  2. Products维护产品详细信息的表
  3. Stocks保持产品数量的表
  4. 表BillDetails维护Purchase和Sales

在此处输入图像描述

我确实尝试使用界面进行购买和销售操作，它可以正常工作，但是我在使用 SQL Server 连接从这三个表中检索数据时遇到问题，以向我展示（从哪个产品购买了多少，卖了多少）。

我试过的代码如下。

select 
    pro_ID
    , sum(case when trn_type = 'PRCH' then stk_quantity else 0 end) as stock
    , sum(case when trn_type = 'PRCH' then bdt_quantity else 0 end) as purchase
    , sum(case when trn_type = 'SALE' then bdt_quantity else 0 end) as Sale
    , sum(case when trn_type = 'PRCH' then bdt_quantity else 0 end)-(sum(case 
        when trn_type = 'PRCH' then bdt_quantity else 0 end)- sum(case when 
        trn_type = 'SALE' then bdt_quantity else 0 end)) as diff
from 
    stocks
join 
    Products on Products.pro_ID = Stocks.stk_Product
right outer join 
    BillDetails on Products.pro_iD = BillDetails.bdt_Product
join 
    Transactions on BillDetails.bdt_trnRef = Transactions.trn_Reference
where 
    pro_ID = '6260100600362'
group by 
    pro_ID 
order by 
    pro_ID asc

由于我只销售了上述产品中的一种，但我的查询给了我错误的销售和购买结果，如下面的屏幕截图所示。

在此处输入图像描述

根据表格信息，正确的信息或结果应如下所示。

  1. 股票 = 123
  2. 购买 = 124
  3. 销售额 = 1
  4. 差异 = 1

标签： sqlsql-servertsql

解决方案

这里的问题是您使用股票表作为您的基表。并且您的基表中有两个相同 proID/stk_product 条目。然后，每当您使用此 id 上的条件进行连接时，您将在连接表上拥有两个具有相同值的条目。所以在你做总和之后，你的价值就会翻倍。

我建议您使用产品作为基表，然后进行连接。

你可以试试这个：

with stocks as (
    select 
        Stk_trnref
        , stk_Product
        , sum(stk_quantity) stk_quantity
    group by Stk_trnref, stk_Product
),

select 
  stk_Product
  , sum(case when trn_type = 'PRCH' then stk_quantity else 0 end) as stock
  , sum(case when trn_type = 'PRCH' then bdt_quantity else 0 end) as purchase
  , sum(case when trn_type = 'SALE' then bdt_quantity else 0 end) as Sale
  , sum(case when trn_type = 'PRCH' then bdt_quantity else 0 end)-(sum(case 
    when trn_type = 'PRCH' then bdt_quantity else 0 end)- sum(case when 
    trn_type = 'SALE' then bdt_quantity else 0 end)) as diff
from Products
left join stocks on Products.pro_ID = Stocks.stk_Product
left join BillDetails on Products.pro_iD = BillDetails.bdt_Product
left join Transactions on BillDetails.bdt_trnRef = Transactions.trn_Reference
where pro_ID = '6260100600362'
group by pro_ID order by pro_ID asc

推荐阅读