问题描述
早上好，
我一直在尝试编写一个连接到表格的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)
NoShipping Label MaterialDescription
15010014530362B 000001BN68-02776AMANUAL USERS-03,IB;Comm,English,250PC
16010014530362B 000001BN96-12469GASSY CABLE P;PN50C450,FFC CABLE,405MM,30250PC
17010014530362B 000001BP68-00052BLABEL-00,RATING;CCTV,TETRON PAPER,T0.05,250PC
1010014530362B 000002BN39-01285ALEAD CONNECTOR;PS50C550G1,UL1007#28,12p250PC
1010014530362B 000003BH68-00653CLABEL SERIAL-00;ALL MODEL,ART PAPER 90G,500PC
1010014530362B 000004BN68-02568ELABEL-PDP-POP;P430(50/42),PET,T0.05,100,250PC
The shipping label is composed from ProductionNumber + WorkGroup + BoxNo
StuffingLists
ContainerWork GroupBox IDBox No
GLDU7083219B SM4
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
10014530362PS50C431A2SXA228
10014530363UA46C6200URSXA47
10014530364LA46C750R2RSXA57
10014530365PS42C431A2SXA676
10014530366PS42C430A1SXA702
10014530367PS50C430A1SXA355
10014530368PS50C450B1SXA58
10014530369PS50C550G1RSXA439
10014530370UA32C6900VRSXA54
10014530378UA40C6200URSXA53
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
10014530362PS50C431A2SXA228
10014530363UA46C6200URSXA47
10014530364LA46C750R2RSXA57
10014530365PS42C431A2SXA676
10014530366PS42C430A1SXA702
10014530367PS50C430A1SXA355
10014530368PS50C450B1SXA58
10014530369PS50C550G1RSXA439
10014530370UA32C6900VRSXA54
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
10014530362PS50C431A2SXA17921792
10014530363UA46C6200URSXA656656
10014530364LA46C750R2RSXA753753
10014530365PS42C431A2SXA34533453
10014530366PS42C430A1SXA35093509
10014530367PS50C430A1SXA24142414
10014530368PS50C450B1SXA745745
10014530369PS50C550G1RSXA27602760
10014530370UA32C6900VRSXA728728
10014530378UA40C6200URSXA717717
What is wrong with my query.
推荐答案
这篇关于需要SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！