问题描述
我有两个表，我想在其中加入并显示数量和详细信息。
表与ITM，DIA联接在一起，并且ITM / DIA组合上的两个表的总数量相等。
I have two table where i want to join and show quantity with details.table are join with ITM,DIA , and total Qty is equal in both table on ITM/DIA combination
我想拆分table1上的table2数量并填充table2数据和table1数据。
I want to split table2 quantity on table1 and populate table2 data along with table1 data.
我有以下数据供您参考： table1和 table2。并且您可以在表 tableResult中看到我的预期结果。
I have below data for your reference, "table1" and "table2". and you can see my expected result in table "tableResult"
CREATE TABLE table1
(`ITM` varchar(5), `DIA` varchar(4), `LOC` varchar(4), `ID` varchar(3), `QTY` int)
;
INSERT INTO table1
(`ITM`, `DIA`, `LOC`, `ID`, `QTY`)
VALUES
('Item1', 'DIA1', 'LOC1', 'ID1', 3),
('Item1', 'DIA1', 'LOC2', 'ID2', 4),
('Item1', 'DIA1', 'LOC2', 'ID2', 6),
('Item1', 'DIA2', 'LOC2', 'ID2', 6),
('Item1', 'DIA2', 'LOC3', 'ID3', 18),
('Item1', 'DIA2', 'LOC4', 'ID4', 90),
('Item1', 'DIA2', 'LOC4', 'ID5', 23),
('Item1', 'DIA3', 'LOC5', 'ID6', 50),
('Item1', 'DIA3', 'LOC6', 'ID7', 20),
('Item2', 'DIA1', 'LOC4', 'ID8', 44),
('Item2', 'DIA2', 'LOC5', 'ID8', 21),
('Item2', 'DIA3', 'LOC6', 'ID9', 20)
;
CREATE TABLE table2
(`ITM` varchar(5), `DIA` varchar(4), `NTA` varchar(5), `QTY` int)
;
INSERT INTO table2
(`ITM`, `DIA`, `NTA`, `QTY`)
VALUES
('Item1', 'DIA1', 'NTA1', 10),
('Item1', 'DIA1', 'NTA2', 3),
('Item1', 'DIA2', 'NTA3', 30),
('Item1', 'DIA2', 'NTA4', 7),
('Item1', 'DIA2', 'NTA5', 100),
('Item1', 'DIA3', 'NTA6', 70),
('Item2', 'DIA1', 'NTA7', 22),
('Item2', 'DIA1', 'NTA8', 20),
('Item2', 'DIA2', 'NTA9', 6),
('Item2', 'DIA2', 'NTA10', 15),
('Item2', 'DIA3', 'NTA11', 8),
('Item2', 'DIA3', 'NTA11', 12)
;
CREATE TABLE tableResult
(`ITM` varchar(5), `DIA` varchar(4), `LOC` varchar(4), `ID` varchar(3), `QTY` int, `NTA` varchar(5), `NewQTY` int)
;
INSERT INTO tableResult
(`ITM`, `DIA`, `LOC`, `ID`, `QTY`, `NTA`, `NewQTY`)
VALUES
('Item1', 'DIA1', 'LOC1', 'ID1', 3, 'NTA1', 3),
('Item1', 'DIA1', 'LOC2', 'ID2', 4, 'NTA1', 4),
('Item1', 'DIA1', 'LOC2', 'ID2', 6, 'NTA1', 3),
('Item1', 'DIA1', 'LOC2', 'ID2', 6, 'NTA2', 3),
('Item1', 'DIA2', 'LOC2', 'ID2', 6, 'NTA3', 6),
('Item1', 'DIA2', 'LOC3', 'ID3', 18, 'NTA3', 18),
('Item1', 'DIA2', 'LOC4', 'ID4', 90, 'NTA3', 6),
('Item1', 'DIA2', 'LOC4', 'ID4', 90, 'NTA4', 7),
('Item1', 'DIA2', 'LOC4', 'ID4', 90, 'NTA5', 77),
('Item1', 'DIA2', 'LOC4', 'ID5', 23, 'NTA5', 23),
('Item1', 'DIA3', 'LOC5', 'ID6', 50, 'NTA6', 50),
('Item1', 'DIA3', 'LOC6', 'ID7', 20, 'NTA6', 20),
('Item2', 'DIA1', 'LOC4', 'ID8', 44, 'NTA7', 22),
('Item2', 'DIA1', 'LOC4', 'ID8', 44, 'NTA8', 20),
('Item2', 'DIA2', 'LOC5', 'ID8', 21, 'NTA9', 6),
('Item2', 'DIA2', 'LOC5', 'ID8', 21, 'NTA10', 15),
('Item2', 'DIA3', 'LOC6', 'ID9', 20, 'NTA11', 8),
('Item2', 'DIA3', 'LOC6', 'ID9', 20, 'NTA11', 12)
;
下面是数据的屏幕截图；
Below is screenshot of data;
我可以使用proc使其跟随光标，但是我想有什么简单的方法借助SQL 2014，我知道CTE追溯技巧将有所帮助。.
I can make it with a proc and follow cursor, but I want to is there any easy way with SQL 2014 and I know for a fact CTE recusive trick will help..
能否请您分享一下有关此问题的解决方案？
Could you please share your solution on this? appreciate lot on your valuable ideas..
推荐答案
您只需要将table1和table2的单位数量爆炸，然后将它们耦合即可
注意FN_NUMBERS（n），它是一个仅返回数字从1到n的列的函数，您在数据库中需要它，有很多方法可以使用，只需在Google上查找理货表格或。
我使用以下命令：
You simply need to explode quantities in units both for table1 and table2 and then couple them side by side.
Pay attention to FN_NUMBERS(n), it is a function that returns only one column with numbers from 1 to n, you need it in you database, there are many ways to do it, just google for "tally tables" or look here.
I use the following:
CREATE FUNCTION FN_NUMBERS(
@MAX INT
)
RETURNS @N TABLE (N INT NOT NULL PRIMARY KEY)
BEGIN
WITH
Pass0 as (select '1' as C union all select '1'), --2 rows
Pass1 as (select '1' as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select '1' as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select '1' as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select TOP (@MAX) '1' as C from Pass3 as A, Pass3 as B) --65536 rows
,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass2 as B, Pass1 as C) --4194304 rows
--,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass3 as B) --16777216 rows
--,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass4 as B) --4294836225 rows
INSERT INTO @N
SELECT TOP (@MAX) ROW_NUMBER() OVER(ORDER BY C) AS N
FROM Tally
RETURN
END
返回到SQL。
;with
t1 as (
select *, ROW_NUMBER() over (partition by itm,dia order by loc,id) rn
from table1 t1
join FN_NUMBERS(500) on n<=t1.qty
),
t2 as (
select *, ROW_NUMBER() over (partition by itm,dia order by nta) rn
from table2 t2
join FN_NUMBERS(500) on n<=t2.qty
),
t3 as (
select t1.itm, t1.dia, t1.loc, t1.id, t1.qty, t2.nta, count(t1.n) NewQTY
from t1
join t2 on t1.itm=t2.itm and t1.dia = t2.dia and t1.rn=t2.rn
group by t1.itm, t1.dia, t1.loc, t1.id, t1.qty, t2.nta
)
select *
from t3
order by 1,2,3,4,5,6
这篇关于SQL如何根据目标源上的数量将数量分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！