需要SQL查询 [英] Need SQL Query

问题描述

早上好，

我一直在尝试编写一个连接到表格的SQL查询，并计算在收货项目中扫描的箱子.

两张表:
装箱单
包含实际的货运标签(正在扫描的序列号)
但是由于此表实际上是关于实际发货的零件，因此列出了多个发货标签:(010014530362B 000001 x 17)
没有运输标签材料说明

15 010014530362B 000001 BN68-02776A MANUAL USERS-03，IB; Comm，English，250 PC
16 010014530362B 000001 BN96-12469G组件电缆P; PN50C450，FFC电缆，405MM，30 250 PC
17 010014530362B 000001 BP68-00052B LABEL-00，额定值;闭路电视，铁氧体纸，T0.05，250个PC
1 010014530362B 000002 BN39-01285A引线连接器; PS50C550G1，UL1007#28,12p 250 PC
1 010014530362B 000003 BH68-00653C标签系列-00;所有型号，ART PAPER 90G，500 PC
1 010014530362B 000004 BN68-02568E LABEL-PDP-POP; P430(50/42)，PET，T0.05,100，250 PC
运输标签由ProductionNumber + WorkGroup + BoxNo
组成
填充列表
集装箱工作组箱ID箱
GLDU7083219 B SM 4

所以:
装箱清单:

Good day,

I have been trying to write an SQL query that joins to tabels and counts the boxes that were scanned in a goods receiving project.

The two tables:
PackingLists
Containing the actual ShippingLabels (serial number being scanned)
But because this table is actually about the actual parts shipped there are more than 1 shippinglabel listed: (010014530362B 000001 x 17)
No Shipping Label Material Description

15 010014530362B 000001 BN68-02776A MANUAL USERS-03,IB;Comm,English, 250 PC
16 010014530362B 000001 BN96-12469G ASSY CABLE P;PN50C450,FFC CABLE,405MM,30 250 PC
17 010014530362B 000001 BP68-00052B LABEL-00,RATING;CCTV,TETRON PAPER,T0.05, 250 PC
1 010014530362B 000002 BN39-01285A LEAD CONNECTOR;PS50C550G1,UL1007#28,12p 250 PC
1 010014530362B 000003 BH68-00653C LABEL SERIAL-00;ALL MODEL,ART PAPER 90G, 500 PC
1 010014530362B 000004 BN68-02568E LABEL-PDP-POP;P430(50/42),PET,T0.05,100, 250 PC
The shipping label is composed from ProductionNumber + WorkGroup + BoxNo

StuffingLists
Container Work Group Box ID Box No
GLDU7083219 B SM 4

So:
PackingLists:

SELECT CondesedP.ProductionNo, CondesedP.Model, count(CondesedP.Shippinglabel) as Found
FROM
(
SELECT ProductionNo, Model, cast(right(ShippingLabel,6)as int) as BoxNo , ShippingLabel, boxStatus, WeekNo FROM PackingLists
Group By ProductionNo, Model, ShippingLabel, BoxStatus, WeekNo
) CondesedP 
WHERE CondesedP.WeekNo='W35' and CondesedP.BoxStatus='FOUND'
Group By
CondesedP.ProductionNo, CondesedP.Model
Order By 
CondesedP.ProductionNo, CondesedP.Model

节目:
生产中未找到型号数量
10014530362 PS50C431A2SXA 228
10014530363 UA46C6200URSXA 47
10014530364 LA46C750R2RSXA 57
10014530365 PS42C431A2SXA 676
10014530366 PS42C430A1SXA 702
10014530367 PS50C430A1SXA 355
10014530368 PS50C450B1SXA 58
10014530369 PS50C550G1RSXA 439
10014530370 UA32C6900VRSXA 54
10014530378 UA40C6200URSXA 53

AND:
填充列表:

Shows:
ProductionNo Model Qty Found
10014530362 PS50C431A2SXA 228
10014530363 UA46C6200URSXA 47
10014530364 LA46C750R2RSXA 57
10014530365 PS42C431A2SXA 676
10014530366 PS42C430A1SXA 702
10014530367 PS50C430A1SXA 355
10014530368 PS50C450B1SXA 58
10014530369 PS50C550G1RSXA 439
10014530370 UA32C6900VRSXA 54
10014530378 UA40C6200URSXA 53

AND:
StuffingLists:

SELECT ProductionNo, Model, count(BoxNo), WeekNo FROM StuffingLists
WHERE WeekNo='W35'
Group By ProductionNo, Model,  WeekNo
Order By ProductionNo, Model

暂无产品型号
10014530362 PS50C431A2SXA 228
10014530363 UA46C6200URSXA 47
10014530364 LA46C750R2RSXA 57
10014530365 PS42C431A2SXA 676
10014530366 PS42C430A1SXA 702
10014530367 PS50C430A1SXA 355
10014530368 PS50C450B1SXA 58
10014530369 PS50C550G1RSXA 439
10014530370 UA32C6900VRSXA 54

然后，我尝试将它们结合在一起以显示:
对于星期，请按特定的生产数量和型号进行搜索，相对于已发货的总盒子数，发现有多少.

ProductionNo Model Qty
10014530362 PS50C431A2SXA 228
10014530363 UA46C6200URSXA 47
10014530364 LA46C750R2RSXA 57
10014530365 PS42C431A2SXA 676
10014530366 PS42C430A1SXA 702
10014530367 PS50C430A1SXA 355
10014530368 PS50C450B1SXA 58
10014530369 PS50C550G1RSXA 439
10014530370 UA32C6900VRSXA 54

Then I tried to join them together in order to show:
For weekno by specific production number and model how many have been found against the total number of boxes shipped.

SELECT S.ProductionNo, S.Model, Count(S.BoxNo), Count(P.BoxNos) 
FROM
(
SELECT ProductionNo, Model, cast(right(ShippingLabel,6)as int) as BoxNos , ShippingLabel, boxStatus, WeekNo FROM PackingLists
WHERE WeekNo='W35'
Group By ProductionNo, Model, ShippingLabel, BoxStatus, WeekNo
) P 
 left Outer JOIN
(
SELECT ProductionNo, Model, BoxNo, WeekNo FROM StuffingLists
WHERE WeekNo='W35'
Group By ProductionNo, Model, BoxNo, WeekNo
) S
on S.BoxNo = P.BoxNos
WHERE s.Model not like 'C%'
Group By S.ProductionNo, S.Model
Order By S.ProductionNo, S.Model

我得到:几乎就像所有的星期都在计数
10014530362 PS50C431A2SXA 1792 1792
10014530363 UA46C6200URSXA 656656
10014530364 LA46C750R2RSXA 753753
10014530365 PS42C431A2SXA 3453 3453
10014530366 PS42C430A1SXA 3509 3509
10014530367 PS50C430A1SXA 2414 2414
10014530368 PS50C450B1SXA 745 745
10014530369 PS50C550G1RSXA 2760 2760
10014530370 UA32C6900VRSXA 728728
10014530378 UA40C6200URSXA 717717

我的查询出了什么问题.

and i get: almost like it is counting all the weeks
10014530362 PS50C431A2SXA 1792 1792
10014530363 UA46C6200URSXA 656 656
10014530364 LA46C750R2RSXA 753 753
10014530365 PS42C431A2SXA 3453 3453
10014530366 PS42C430A1SXA 3509 3509
10014530367 PS50C430A1SXA 2414 2414
10014530368 PS50C450B1SXA 745 745
10014530369 PS50C550G1RSXA 2760 2760
10014530370 UA32C6900VRSXA 728 728
10014530378 UA40C6200URSXA 717 717

What is wrong with my query.

推荐答案

我找到了解决方案.如果有人遇到此问题:

选择SELECT T.ContainerNo，Sum(T.ContainerTotalBoxes)作为
来自
(选择S.ContainerNo，S.ContainertotalBoxes
来自
(选择生产号，型号，运输标签，将(右(ShippingLabel，6)作为整数)强制转换为BoxNo，WeekNo，BoxStatus
从装箱单
按生产分组编号，型号，运输标签，星期编号，BoxStatus)P
左外联接
(选择ContainerNo，ProductionNo，ContainerTotalBoxes，BoxNo，WeekNo
来自StuffingLists
按货柜号，生产号，货柜总数，箱号，周号分组S
ON P.BoxNo = S.BoxNo AND P.WeekNo = S.WeekNo AND P.ProductionNo = S.ProductionNo
在哪里P.WeekNo =''W33''和P.BoxStatus =''找到''
按S.ContainerNo，S.ContainerTotalBoxes分组)T
/* WHERE T.ContainerNo =''PONU7123208''*/
按T.ContainerNo分组

I found the solution. If anyone runs into this:

SELECT T.ContainerNo, Sum(T.ContainerTotalBoxes) AS FOUND
FROM
(SELECT S.ContainerNo, S.ContainertotalBoxes
FROM
(SELECT ProductionNo, Model, ShippingLabel, cast(right(ShippingLabel,6) as int) as BoxNo, WeekNo, BoxStatus
FROM PackingLists
Group By ProductionNo, Model, ShippingLabel, WeekNo, BoxStatus ) P
LEFT OUTER JOIN
(SELECT ContainerNo, ProductionNo, ContainerTotalBoxes, BoxNo, WeekNo
FROM StuffingLists
Group By ContainerNo, ProductionNo, ContainerTotalBoxes, BoxNo, WeekNo) S
ON P.BoxNo=S.BoxNo AND P.WeekNo = S.WeekNo AND P.ProductionNo=S.ProductionNo
WHERE P.WeekNo=''W33'' AND P.BoxStatus=''Found''
Group By S.ContainerNo, S.ContainerTotalBoxes ) T
/*WHERE T.ContainerNo=''PONU7123208''*/
Group By T.ContainerNo

这篇关于需要SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！