DECLARE @t TABLE(Pid NVARCHAR(10),Qty INT,Subid NVARCHAR(10),SubQty INT);
INSERT INTO @t(Pid,Qty,Subid,SubQty)
SELECT 'A',1,'B',10 UNION ALL
SELECT 'B',1,'C',100 UNION ALL
SELECT 'A',1,'D',50 UNION ALL
SELECT 'A',1,'E',200 SELECT * FROM @t--我就想在下层的时候能够 10*100 就是所需物料咯
--结构是这样的,查找A
--Pid Qty Subid SubQty
------------ ----------- ---------- -----------
--A 1 B 10
--B 1 C 100*10 --就是这里有技巧,
--A 1 D 50
--A 1 E 200
--想一步到位可能吗?谢谢大侠,指点
DECLARE @t TABLE(Pid NVARCHAR(10),Qty INT,Subid NVARCHAR(10),SubQty INT);
INSERT INTO @t(Pid,Qty,Subid,SubQty)
SELECT 'A',1,'B',10 UNION ALL
SELECT 'B',1,'C',100 UNION ALL
SELECT 'A',1,'D',50 UNION ALL
SELECT 'A',1,'E',200 SELECT * FROM @t--我就想在下层的时候能够 10*100 就是所需物料咯
--结构是这样的,查找A
--Pid Qty Subid SubQty
------------ ----------- ---------- -----------
--A 1 B 10
--B 1 C 100*10 --就是这里有技巧,
--A 1 D 50
--A 1 E 200
--想一步到位可能吗?谢谢大侠,指点select pid,qty,subid,subqty*isnull((select subqty from @t where subid = t.pid),1)
from @t t/************Pid Qty Subid SubQty
---------- ----------- ---------- -----------
A 1 B 10
B 1 C 100
A 1 D 50
A 1 E 200(4 行受影响)pid qty subid
---------- ----------- ---------- -----------
A 1 B 10
B 1 C 1000
A 1 D 50
A 1 E 200(4 行受影响)
DECLARE @t TABLE(Pid NVARCHAR(10),Qty INT,Subid NVARCHAR(10),SubQty INT);
INSERT INTO @t(Pid,Qty,Subid,SubQty)
SELECT 'A',1,'B',10 UNION ALL
SELECT 'B',1,'C',100 UNION ALL
SELECT 'A',1,'D',50 UNION ALL
SELECT 'A',1,'E',200 --SELECT * FROM @t
SELECT a.pid,a.qty,a.subid,ISNULL(c.SubQty1,a.SubQty) AS SubQty FROM @t a
OUTER APPLY
(
SELECT CAST(a.SubQty AS VARCHAR(20))+'*'+CAST(b.SubQty AS VARCHAR(20)) AS SubQty1 FROM @t b
WHERE a.pid=b.subid
) c/*
A 1 B 10
B 1 C 100*10
A 1 D 50
A 1 E 200
*/