连接语句中列的一部分的总和 [英] Sum of part of a column in a join statement

问题描述

钢铁订单

OrderNumber
-----------
0000843989    
0000843124    
0000881234    
0000123456    
-----------

FinalizedPrintedStickers

SN                  |  PanelBuildTime
-------------------------------------
0000843989-8R8-4-0  |  360
0000843989-8R8-4-1  |  200
0000843989-8R8-4-2  |  900    
0000843989-8R8-4-3  |  360
0000843989-8R8-4-4  |  460
0000843989-8S-1-0   |  220    
0000843989-8-2-0    |  360
etc..
-------------------------------------

所需的结果是:

OrderNumber |  PanelBuildTime
-----------------------------
0000843989  |  2860    
etc..
-----------------------------

我尝试了什么?

Select OrderNumber, Sum(PanelBuildTime) from SteelOrders 
INNER JOIN FinalizedPrintedStickers 
ON 
SteelOrders.OrderNumber = LEFT(FinalizedPrintedStickers.SN,10)

不幸的是，我不知道如何在 steelorder 表中的每个项目上添加 WHERE 子句

Unfortunately I have no idea how to add a WHERE clause on to this per item in the steelorder table

另外(因为我在我的 C# 代码中使用 oledb 从钢铁订单表中读取)我已经尝试了每次读取 OleDbDataReader 执行 Select Sum(PanelBuildtime)其中 ordernumber = reader["OrderNumber"]，但速度非常慢

Also (Since I'm using oledb in my C# code to read from the steel order table) I've tried for every read of the OleDbDataReader Executing the Select Sum(PanelBuildtime) where ordernumber = reader["OrderNumber"], but that's extremely slow

推荐答案

可以按订单号分组，显示构建时间汇总

You can group by the order number to show the summary of the build times

Select OrderNumber, Sum(PanelBuildTime) from SteelOrders 
INNER JOIN FinalizedPrintedStickers 
ON 
SteelOrders.OrderNumber = LEFT(FinalizedPrintedStickers.SN,10)
GROUP BY SteelOrders.OrderNumber 

这篇关于连接语句中列的一部分的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！