有没有一个SQL可以完成下面的要求
数据是:
id userName userEge
1 a 21
2 b 26
3 c 30
4 d 35
5 e 22
6 f 28
7 g 32
8 h 34要求,显示出组合userEge加起来为56的数据
输出显示是:如
group id username userEge
1 1 a 21
1 4 d 35
2 5 e 22
2 8 h 34
3 2 b 26
3 3 c 30不知道能不能在mssql里完成,存储过程或者直接SQL完成,求大神看看,谢谢啦SQL
数据是:
id userName userEge
1 a 21
2 b 26
3 c 30
4 d 35
5 e 22
6 f 28
7 g 32
8 h 34要求,显示出组合userEge加起来为56的数据
输出显示是:如
group id username userEge
1 1 a 21
1 4 d 35
2 5 e 22
2 8 h 34
3 2 b 26
3 3 c 30不知道能不能在mssql里完成,存储过程或者直接SQL完成,求大神看看,谢谢啦SQL
with tb(id,userName,userEge)as(
select 1,'a',21 union
select 2,'b',26 union
select 3,'c',30 union
select 4,'d',35 union
select 5,'e',22 union
select 6,'f',28 union
select 7,'g',32 union
select 8,'h',34)
,tc as(
select *,b=(select userEge from tb where userEge+a.userEge=56 and id>a.id),
c=(select id from tb where userEge+a.userEge=56 and id>a.id) ,
d=(select username from tb where userEge+a.userEge=56 and id>a.id) from tb a)
,td as(
select id [group],id,userName,userege from tc where b is not null
union
select id,c,d,b from tc where b is not null)
select [group]=dense_rank() over(order by [group]),id,username,userege from td
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[userName] VARCHAR(1),[userEge] INT)
INSERT [tb]
SELECT 1,'a',21 UNION ALL
SELECT 2,'b',26 UNION ALL
SELECT 3,'c',30 UNION ALL
SELECT 4,'d',35 UNION ALL
SELECT 5,'e',22 UNION ALL
SELECT 6,'f',28 UNION ALL
SELECT 7,'g',32 UNION ALL
SELECT 8,'h',34
--------------开始查询--------------------------;WITH cte AS (
SELECT [group] = ROW_NUMBER() OVER (ORDER BY [userEge]) , *
FROM [tb] AS t
WHERE [userEge] <= 28
AND EXISTS ( SELECT 1
FROM [tb]
WHERE [userEge] > 28
AND t.[userEge] = 56 - [userEge] )
UNION ALL
SELECT [group] = ROW_NUMBER() OVER (ORDER BY [userEge] DESC) , *
FROM [tb] AS t
WHERE [userEge] > 28
AND EXISTS ( SELECT 1
FROM [tb]
WHERE [userEge] < 28
AND t.[userEge] = 56 - [userEge] )
)
SELECT [group],[id],[userName],[userEge] FROM cte ORDER BY [group],[userEge]
----------------结果----------------------------
/*
group id userName userEge
1 1 a 21
1 4 d 35
2 5 e 22
2 8 h 34
3 2 b 26
3 3 c 30
*/
DROP table #cu1;
IF object_id('tempdb..#cu2') is not null
DROP table #cu2;select 1 id,'a' userName,21 userEge into #cu1 union all
select 2,'b',26 union all
select 3,'c',30 union all
select 4,'d',35 union all
select 5,'e',22 union all
select 6,'f',28 union all
select 7,'g',32 union all
select 8,'h',34select top 0 cast(0 as int) [group],* into #cu2 from #cu1declare @i1 int,@i2 int,@i3 int,@i4 int
select @i1=max(id),@i2=1,@i4=1 from #cu1
while @i2<@i1
begin
set @i3=@i2+1
while @i3<=@i1
begin
if (select userEge from #cu1 where id=@i2)+(select userEge from #cu1 where id=@i3)=56
begin
insert into #cu2
select @i4,* from #cu1 where id=@i2
union all
select @i4,* from #cu1 where id=@i3
set @i4=@i4+1
end
set @i3=@i3+1
end
set @i2=@i2+1
end
select * from #cu2
DROP table #cu1
IF object_id('tempdb..#cu2') is not null
DROP table #cu2select 1 id,'a' userName,21 userEge into #cu1 union all
select 2,'b',10 union all
select 3,'c',30 union all
select 4,'d',16 union all
select 5,'e',40 union all
select 6,'f',12 union all
select 7,'g',4 union all
select 8,'h',52select top 0 cast(0 as int) [group],* into #cu2 from #cu1declare @i1 int,@i2 int,@i3 int,@i4 int,@i5 int,@i6 int,@c1 varchar(max),@c2 varchar(max)
select @i1=max(id),@i2=1,@i4=1 from #cu1
while @i2<@i1
begin
set @i3=@i2+1
select @i5=userEge,@i6=userEge from #cu1 where id=@i2
set @c1=cast(@i2 as varchar)
while @i3<=@i1
begin
set @i6=@i6+(select userEge from #cu1 where id=@i3)
set @c1=@c1+','+cast(@i3 as varchar)
if @i6=56
begin
set @c2='insert into #cu2 select '+cast(@i4 as varchar)+',* from #cu1 where id in ('+@c1+')'
exec(@c2)
set @i4=@i4+1
end
if @i6>=56
begin
set @i6=@i5
set @c1=cast(@i2 as varchar)
end
set @i3=@i3+1
end
set @i2=@i2+1
end
select * from #cu2