时间戳

首页 > 解决方案 > 非唯一的 SQL 右连接

sql - 非唯一的 SQL 右连接

问题描述

我希望我在想这个。但是我需要对一个没有唯一链接的列进行求和，并且当我这样做时会加倍列。

这是我当前的 SQL，直到我添加连接vwBatchInData然后它加倍每条记录，实现这一目标的最佳方法是什么？

select b.fldBatchID as 'ID',SUM(bIn.fldBatchDetailsWeight)  as 'Batch In', sum(t.fldTransactionNetWeight) as 'Batch Out' , format((sum(t.fldTransactionNetWeight) / sum(bIn.fldBatchDetailsWeight)),'P2' ) as 'Yield'
    from [TRANSACTION] t 
        right join vwBatchInData bIn on bIn.fldBatchID = t.fldBatchID
        inner join Batch b on b.fldBatchID = t.fldBatchID
    where CAST(b.fldBatchDate as date) = '2020-03-04'
group by  b.fldBatchID**

vwBatchInData 表

    +------------+---------------+-----------------------+
    | fldBatchID | fldKillNumber | fldBatchDetailsWeight |
    +------------+---------------+-----------------------+
    |       2862 |        601598 | 164.40                |
    |       2862 |        601599 | 190.80                |
    |       2862 |        601596 | 195.00                |
    |       2862 |        601597 | 200.20                |
    |       2862 |        601594 | 176.60                |
    +------------+---------------+-----------------------+

交易表

+------------+------------------+-------------------------+
| fldBatchID | fldTransactionID | fldTransactionNetWeight |
+------------+------------------+-------------------------+
|       2862 |         10242352 | 16.26                   |
|       2862 |         10242353 | 22.82                   |
|       2862 |         10242362 | 18.52                   |
|       2862 |         10242363 | 21.44                   |
|       2862 |         10242364 | 20.32                   |
+------------+------------------+-------------------------+

批处理表

+------------+-------------------------+
| fldBatchID |      fldBatchDate       |
+------------+-------------------------+
|       2862 | 2020-03-04 00:00:00.000 |
+------------+-------------------------+

上述片段的所需输出

+------+----------+-----------+---------+
|  ID  | Batch In | Batch Out |  Yield  |
+------+----------+-----------+---------+
| 2862 | 927.00   | 90.36     | 10.76 % |
+------+----------+-----------+---------+

标签： sqlsql-server

解决方案

我认为您只想在ing之前进行聚合： join

select b.fldBatchID as ID,
       (bIn.fldBatchDetailsWeight)  as batch_in,
       (t.fldTransactionNetWeight) as batch_out, 
       format(t.fldTransactionNetWeight / bIn.fldBatchDetailsWeight, 'P2' ) as Yield
    from batch b left join
         (select bin.fldBatchID, sum(fldBatchDetailsWeight) as fldBatchDetailsWeight
          from vwBatchInData bin
          group by bin.fldBatchID
         ) bin
         on bIn.fldBatchID = b.fldBatchID left join
         (select t.fldBatchID, sum(fldTransactionNetWeight) as fldTransactionNetWeight
          from transactions t
          group by t.fldBatchID
         ) bin
         on t.fldBatchID = b.fldBatchID 
    where CAST(b.fldBatchDate as date) = '2020-03-04';

推荐阅读