求一存儲過程(2個表,參數數組）

Question

表一：ID為主鍵
ID    PN     SubPN  InQty
1     A      a1       3
2     A      a1       1
3     A      b1       2
4     B      a1       5
5     B      b1       2
6     C      a1       6
表二：PN及SubPN為聯合主鍵
PN    SubPN    TotalOutQty
A      a1        2
B      b1        3
B      a1        5
C      a1        3現求一存儲過程：要求傳入的字符串參數相當與PN數組（即"A;B;C;"這種類型）
現假如傳入的參數為"A;B",需得到如下結果
SubPN  InQty   OutQty
a1      9        7
b1      4        3其中結果9等於表一中的3+1+5, 4等於表一中的2+2　，7等於表二中的2+5，3等於表二中的3謝謝！

Answer 1

参数：a,b
查 SubPN，sum(a.InQty ),sum(b.TotalOutQty)
----------------------
接分咯

Answer 2

--未测试
create proc pr_q
@Pns varchar(2000)
as
declare @sql varchar(8000)
set @sql=left(stuff(replace(','+@Pns+',',',',''','''),1,2,''),len(stuff(replace(','+@Pns+',',',',''','''),1,2,''))-2)exec('select SubPN,sum(InQty) as InQty,sum(OutQty) as OutQty from ( select SubPN,InQty,cast(0 as int) as OutQty from 表一 where PN in ('+@sql+') union all select SubPN,0 as InQty,TotalOutQty as OutQty from 表二 where PN in ('+@sql+')) as t group by SubPN')

Answer 3

declare @a table (id int,pn varchar(5),subpn varchar(5),inqty int)
insert into @a select 1,'a','a1',3
insert into @a select 2,'a','a1',1
insert into @a select 3,'a','b1',2
insert into @a select 4,'b','a1',5
insert into @a select 5,'b','b1',2
insert into @a select 6,'c','a1',6declare @b table (pn varchar(5),subpn varchar(5),totaloutqty int)
insert into @b select 'a','a1',2
insert into @b select 'b','b1',3
insert into @b select 'b','a1',5
insert into @b select 'c','a1',3
--SubPN     InQty       OutQty
--a1             9                 7
--b1             4                 3 
declare @s varchar(50)
set @s='A;B'
select subpn,sum(inqty),qutqty=(select sum(totaloutqty) from @b where subpn=a.subpn and charindex(pn,@s)>0) from @a a where charindex(pn,@s)>0
group by subpn
subpn (无列名) qutqty
a1 9 7
b1 4 3

Answer 4

create table tb1(ID int, PN varchar(8), SubPN varchar(8), InQty int) 
insert tb1 select 1,           'A',             'a1'     ,          3 
union all select 2  ,         'A'  ,           'a1'     ,          1 
union all select 3   ,        'A'   ,          'b1'    ,           2 
union all select 4    ,       'B'    ,         'a1'  ,             5 
union all select 5     ,      'B',             'b1' ,              2 
union all select 6      ,     'C',             'a1',               6 go
create table tb2(PN varchar(8), SubPN varchar(8), TotalOutQty int)  
insert tb2 select 'A',             'a1'  ,               2 
union all select 'B'  ,           'b1'  ,              3 
union all select 'B'   ,          'a1' ,                5 
union all select 'C'    ,         'a1',                 3 go
create procedure sp_Test
@PNs varchar(1000)
as
begin
select SubPN=coalesce(T1.SubPN, T2.SubPN), InQty=isnull(InQty,0), OutQty=isnull(OutQty,0)
from
(select SubPN,InQty=sum(InQty) from tb1
where charindex(';'+PN+';',';'+@PNs+';')>0
group by SubPN
) T1 full join
(select SubPN,OutQty=sum(TotalOutQty) from tb2
where charindex(';'+PN+';',';'+@PNs+';')>0
group by SubPN
) T2 on T1.SubPN=T2.SubPN
end
goexec sp_Test 'A;B'
/*
SubPN    InQty       OutQty
-------- ----------- -----------
a1       9           7
b1       4           3(2 row(s) affected)
*/drop procedure sp_Test
drop table tb1,tb2

Answer 5

可能分隔符看错了
用;作为分隔符set @sql=left(stuff(replace(','+@Pns+',',',',''','''),1,2,''),len(stuff(replace(','+@Pns+',',',',''','''),1,2,''))-2)
-----〉
set @sql=left(stuff(replace(';'+@Pns+';',';',''','''),1,2,''),len(stuff(replace(';'+@Pns+';',';',''','''),1,2,''))-2)

Answer 6

create table tb1 (
 ID int identity(1,1) primary key
,PN char(1)
,SubPN char(2)
,InQty int
)
insert into tb1
select 'A','a1',3 union 
select 'A','a1',1 union 
select 'A','b1',2 union
select 'B','a1',5 union
select 'B','b1',2 union
select 'C','a1',6
go
create table tb2 (
 PN char(1)
,SubPN char(2)
,TotalOutQty int
primary key (PN,SubPN)
)
insert into tb2
select 'A','a1',2 union
select 'B','b1',3 union
select 'B','a1',5 union
select 'C','a1',3
godeclare @q varchar(32) set @q = 'A;B'select subpn, sum(TotalInQty), sum(TotalOutQty)
from (
select a.pn,a.subpn,TotalInQty,TotalOutQty
from (
select pn, subpn, TotalInQty = sum(InQty)
from tb1
group by pn, subpn
) as a
left join tb2 as b on a.pn=b.pn and a.subpn=b.subpn
where charindex(a.pn,@q)>0
) as c
group by subpn
godrop table tb1,tb2
go

Answer 7

TO:wzy_love_sly
　你的sql語句好象只能得到我剛才舉例的結果。如果PN存在a,ab,b這樣紀錄的好象就不行
declare @a table (id int,pn varchar(5),subpn varchar(5),inqty int)
insert into @a select 1,'ab','a1b1',3
insert into @a select 2,'ab','a1',1
insert into @a select 3,'ab','b1',2
insert into @a select 4,'b','a1b1',5
insert into @a select 5,'b','b1',2
insert into @a select 6,'a','a1',6declare @b table (pn varchar(5),subpn varchar(5),totaloutqty int)
insert into @b select 'ab','a1b1',2
insert into @b select 'b','b1',3
insert into @b select 'b','a1',5
insert into @b select 'a','a1',3declare @s varchar(50)
set @s='ab;a'
select subpn,sum(inqty),qutqty=(select sum(totaloutqty) from @b where subpn=a.subpn and charindex(pn,@s)>0) from @a a where charindex(pn,@s)>0
group by subpn
subpn (无列名) qutqty
a1   7  8
a1b1 8  2
b1   4  3正確的應該是
a1   7    3
a1b1 3    2
b1   2    null我猜測是CharIndex的問題把