请教一sql 语句
现在我查询出来的结果是
ScheduleNo ProductNo GetSort Num
1 917 1 1
1 917 2 1
1 917 3 1
1 917 12 1
1 917 123 1
1 917 23 1
我想要的结果是
ScheduleNo ProductNo GetSort Num
1 917 1 3
1 917 2 4
1 917 3 3
就是 如果 getsort是12的话 就num 的值分别加在 getsort 是1 和 2 的num上 ,请问sql 应该如何实现啊 或者其他方法
[解决办法]
create table #(ScheduleNo int, ProductNo int, GetSort int, Num int)
insert into #
select 1,917,1,1 union all
select 1,917,2,1 union all
select 1,917,3,1 union all
select 1,917,12,1 union all
select 1,917,123,1 union all
select 1,917,23,1
select ScheduleNo,ProductNo,GetSort,Num = (select count(1) from # where charindex(rtrim(a.GetSort),rtrim(GetSort)) > 0) from # a
where len(GetSort) = 1
[解决办法]
--这样就可以
create table #(ScheduleNo int, ProductNo int, GetSort nvarchar(20), Num int)
insert into #
select 1,917, '1 ',1 union all
select 1,917, '2 ',1 union all
select 1,917, '3 ',1 union all
select 1,917, '12 ',1 union all
select 1,917, '123 ',1 union all
select 1,917, '23 ',1
select ScheduleNo,ProductNo,GetSort,Num = (select count(1) from # where charindex(a.GetSort,GetSort) > 0) from # a
where len(GetSort) = 1
[解决办法]
---创建测试环境
Declare @T Table(ScheduleNo int,ProductNo int,GetSort int,Num int)
Insert @T Select 1, 917, 1, 1
Union All Select 1, 917, 2, 1
Union All Select 1, 917, 3, 1
Union All Select 1, 917, 12, 1
Union All Select 1, 917, 123, 1
Union All Select 1, 917, 23, 1
Select * From @T
---查询结果
Select
ScheduleNo,
ProductNo,
GetSort,
(Select Count(1) From @T Where CharIndex(Cast(T.GetSort As Varchar),GetSort)> 0) As Num
From
@T T
Where Len(GetSort)=1
---结果
/*
ScheduleNo ProductNo GetSort Num
----------- ----------- ----------- -----------
1 917 1 3
1 917 2 4
1 917 3 3
（所影响的行数为 3 行）
*/
[解决办法]
declare @t table (ScheduleNo int, ProductNo int, GetSort varchar(20), Num varchar(20))
insert into @t
select 1,917,1,1 union all
select 1,917,2,1 union all
select 1,917,3,1 union all
select 1,917,12,1 union all
select 1,917,123,1 union all
select 1,917,23,1
select a.ScheduleNo,a.ProductNo,b.GetSort,count(*) Num from @t a
join (select '1 ' GetSort union select '2 ' union select '3 ') b on charindex(b.GetSort,a.GetSort)> 0
group by a.ScheduleNo,a.ProductNo,b.GetSort
[解决办法]
用视图可以。就你讲的这个情况，可以一条语句这样写：
select a.scheduleno,a.productno,a.getsort,sum(b.returnnum)as num from tbtaokouget a join tbtaokoureturn b on a.getid =b.getid
join (select '1 ' GetSort union select '2 ' union select '3 ') c on charindex(c.GetSort,a.GetSort)> 0
group by a.getsort ,a.scheduleno,a.productno
[解决办法]
Select
ScheduleNo,
ProductNo,
GetSort,
(Select Count(1) From @T Where CharIndex(Cast(T.GetSort As Varchar),GetSort)> 0) As Num
From (
select a.scheduleno,
a.productno,
a.getsort,
sum(b.returnnum)as num
from tbtaokouget a inner join tbtaokoureturn b on a.getid =b.getid
group by a.getsort ,a.scheduleno,a.productno
) T
Where Len(GetSort)=1
[解决办法]
create table tb (ScheduleNo int,ProductNo int, GetSort varchar(10),Num int)
insert into tb values(1, 917, '1 ' ,1)
insert into tb values(1, 917, '2 ' ,1)
insert into tb values(1, 917, '3 ' ,1)
insert into tb values(1, 917, '12 ', 1)
insert into tb values(1, 917, '123 ', 1)
insert into tb values(1, 917, '23 ' ,1)
select t2.ScheduleNo ,t2.ProductNo , t2.GetSort,count(*) Num from tb t1,
(
select * from tb where len(getsort) = 1
) t2
where charindex(t2.getsort,t1.getsort) > 0
group by t2.ScheduleNo ,t2.ProductNo , t2.GetSort
drop table tb
/*
ScheduleNo ProductNo GetSort Num
----------- ----------- ---------- -----------
1 917 1 3
1 917 2 4
1 917 3 3
（所影响的行数为 3 行）
*/
