现有如下数据群号 字段1 字段2
? A1 B1
? A1 B2
? A2 B1
? A3 B2
? A4 B2
? A5 B3需根据字段1和字段2,确定群号。上例中,得出如下结果:群号 字段1 字段2
GROUP1 A1 B1
GROUP1 A1 B2
GROUP1 A2 B1
GROUP1 A3 B2
GROUP1 A4 B2
GROUP2 A5 B3即前四个为一个群,后一个为另一个群,请问用SQL语句如何实现?
可以多个sql,可以有中间表
? A1 B1
? A1 B2
? A2 B1
? A3 B2
? A4 B2
? A5 B3需根据字段1和字段2,确定群号。上例中,得出如下结果:群号 字段1 字段2
GROUP1 A1 B1
GROUP1 A1 B2
GROUP1 A2 B1
GROUP1 A3 B2
GROUP1 A4 B2
GROUP2 A5 B3即前四个为一个群,后一个为另一个群,请问用SQL语句如何实现?
可以多个sql,可以有中间表
declare @T table (字段1 varchar(2),字段2 varchar(2))
insert into @T
select 'A1','B1' union all
select 'A1','B2' union all
select 'A2','B1' union all
select 'A3','B2' union all
select 'A4','B4' union all
select 'A5','B3' union all
select 'A1','B3' union all
select 'A1','B2' union all
select 'A2','B1' union all
select 'A3','B6' union all
select 'A4','B2' union all
select 'A5','B3' union all
select 'A1','B8' union all
select 'A1','B2' union all
select 'A2','B1' union all
select 'A3','B1' union all
select 'A4','B2' union all
select 'A5','B8' union all
select 'A1','B2' union all
select 'A1','B2' union all
select 'A2','B1' union all
select 'A3','B2' union all
select 'A4','B2' union all
select 'A5','B3'
select
'Group'+LTRIM(((row_number() over(order by getdate()))-1)/4+1) as grp,
* from @T
/*
grp 字段1 字段2
----------------------------- ---- ----
Group1 A1 B1
Group1 A1 B2
Group1 A2 B1
Group1 A3 B2
Group2 A4 B4
Group2 A5 B3
Group2 A1 B3
Group2 A1 B2
Group3 A2 B1
Group3 A3 B6
Group3 A4 B2
Group3 A5 B3
Group4 A1 B8
Group4 A1 B2
Group4 A2 B1
Group4 A3 B1
Group5 A4 B2
Group5 A5 B8
Group5 A1 B2
Group5 A1 B2
Group6 A2 B1
Group6 A3 B2
Group6 A4 B2
Group6 A5 B3
*/
if OBJECT_ID('taba') is not null
drop table taba
if OBJECT_ID('tabb') is not null
drop table tabb
go
create table taba(groupid varchar(10),a varchar(4),b varchar(4))
create table tabb(groupid varchar(10),a varchar(4),b varchar(4))
insert into taba(a,b)
select 'A1', 'B1' union all
select 'A1' ,'B2' union all
select 'A2', 'B1' union all
select 'A3', 'B2' union all
select 'A4', 'B2' union all
select 'A5', 'B3'insert into tabb(groupid,a,b) values('GROUP1', 'A1', 'B1'),
('GROUP1', 'A1', 'B2'),
('GROUP1', 'A2', 'B1'),
('GROUP1', 'A3', 'B2'),
('GROUP1', 'A4', 'B2'),
('GROUP2', 'A5', 'B3')
select groupid=(select groupid from tabb where a=ta.a and b=ta.b),a,b from taba ta不知道这个是不是你想要的结果。