如果有3行num相同 你要怎么显示? ID num add user 1 1 XXX DE 5 1 333 XE 6 1 333 XE如果num都相同的,能不能呈现这样的结果 ID NUM ADD USER ID2 USER2 1 1 xxx DE 5 XE 5 1 xxx de 6 xe 要这个样显示吗?
drop table tb2 create table tb2( id int, mxid int, sex nvarchar(10) ) go insert into tb2 values(1,1,'男') insert into tb2 values(2,1,'女') insert into tb2 values(3,3,'女') insert into tb2 values(4,1,'女') insert into tb2 values(5,3,'女') insert into tb2 values(6,6,'男') insert into tb2 values(7,1,'男') ;with tb as ( select row_number() over (order by tb2.id) as number, tb2.id,tb2.mxid , tb2.sex, a.id as newid ,a.sex as newsex from tb2 a inner join tb2 on tb2.id <a.id and tb2.mxid= a.mxid) select id,mxid,sex,newid,newsex from tb where number in (select min(number) from tb group by id)这种看看是不是你要的?
if OBJECT_ID('test') is not null drop table test go create table test(id int,num int,[add] char(3),[user] char(2)) insert into test select 1 , 1 , 'XXX', 'DE' union select 5, 1 , '333', 'XE' ;with sel as( select *,ROW_NUMBER()over(PARTITION by num order by getdate()) as row from test ) select a.id,a.num,a.[add],a.[user],b.id as id2,b.[user] [user2] from sel a join sel b on a.row=1 and b.row=2 /* id num add user id2 user2 ----------- ----------- ---- ---- ----------- ----- 1 1 XXX DE 5 XE */
ID num add user
1 1 XXX DE
5 1 333 XE
6 1 333 XE如果num都相同的,能不能呈现这样的结果
ID NUM ADD USER ID2 USER2
1 1 xxx DE 5 XE
5 1 xxx de 6 xe 要这个样显示吗?
create table tb2(
id int,
mxid int,
sex nvarchar(10)
)
go
insert into tb2 values(1,1,'男')
insert into tb2 values(2,1,'女')
insert into tb2 values(3,3,'女')
insert into tb2 values(4,1,'女')
insert into tb2 values(5,3,'女')
insert into tb2 values(6,6,'男')
insert into tb2 values(7,1,'男')
;with tb as (
select row_number() over (order by tb2.id) as number, tb2.id,tb2.mxid , tb2.sex, a.id as newid ,a.sex as newsex from tb2 a inner join tb2
on tb2.id <a.id and tb2.mxid= a.mxid)
select id,mxid,sex,newid,newsex from tb where number in (select min(number) from tb group by id)这种看看是不是你要的?
if OBJECT_ID('test') is not null
drop table test
go
create table test(id int,num int,[add] char(3),[user] char(2))
insert into test
select 1 , 1 , 'XXX', 'DE' union
select 5, 1 , '333', 'XE'
;with sel as(
select *,ROW_NUMBER()over(PARTITION by num order by getdate()) as row from test
)
select a.id,a.num,a.[add],a.[user],b.id as id2,b.[user] [user2] from sel a join sel b
on a.row=1 and b.row=2
/*
id num add user id2 user2
----------- ----------- ---- ---- ----------- -----
1 1 XXX DE 5 XE
*/