22,210
社区成员
发帖
与我相关
我的任务
分享
with table1 as
(
select 'A1' 身高, 'A班' 班级 union all
select 'A2' 身高, 'A班' 班级 union all
select 'A3' 身高, 'A班' 班级 union all
select 'A4' 身高, 'A班' 班级 union all
select 'A5' 身高, 'A班' 班级 union all
select 'B1' 身高, 'B班' 班级 union all
select 'B2' 身高, 'B班' 班级 union all
select 'B3' 身高, 'B班' 班级 union all
select 'C1' 身高, 'C班' 班级 union all
select 'C2' 身高, 'C班' 班级 union all
select 'C3' 身高, 'C班' 班级 union all
select 'C4' 身高, 'C班' 班级 union all
select 'C5' 身高, 'C班' 班级 union all
select 'C6' 身高, 'C班' 班级
)
select a.身高 身高矮, b.身高 身高中, c.身高 身高高, a.班级 from table1 a
inner join table1 b on a.身高<b.身高 and a.班级=b.班级
inner join table1 c on b.身高<c.身高 and b.班级=c.班级
order by a.班级, a.身高, b.身高, c.身高
with table1 as
(
select 'A1' 身高, 'A班' 班级 union all
select 'A2' 身高, 'A班' 班级 union all
select 'A3' 身高, 'A班' 班级 union all
select 'A4' 身高, 'A班' 班级 union all
select 'A5' 身高, 'A班' 班级 union all
select 'B1' 身高, 'B班' 班级 union all
select 'B2' 身高, 'B班' 班级 union all
select 'B3' 身高, 'B班' 班级 union all
select 'C1' 身高, 'C班' 班级 union all
select 'C2' 身高, 'C班' 班级 union all
select 'C3' 身高, 'C班' 班级 union all
select 'C4' 身高, 'C班' 班级 union all
select 'C5' 身高, 'C班' 班级 union all
select 'C6' 身高, 'C班' 班级
)
, table2 as
(
select ROW_NUMBER() OVER(PARTITION BY 班级 ORDER BY 身高) rn, 身高, 班级 from table1
)
select a.身高 身高矮, b.身高 身高中, c.身高 身高高, a.班级 from table2 a
inner join table2 b on a.rn<b.rn and a.班级=b.班级
inner join table2 c on b.rn<c.rn and b.班级=c.班级
order by a.班级, a.身高, b.身高, c.身高
with table1 as
(
select 1 id, 'A1' c1 union all
select 2 id, 'A2' union all
select 3 id, 'A3' union all
select 4 id, 'A4' union all
select 5 id, 'A5'
)
select a.c1, b.c1, c.c1 from table1 a
left join table1 b on a.id<b.id
inner join table1 c on b.id=c.id-1
order by a.id, b.id
with table1 as
(
select 1 id, 'A1' c1 union all
select 2 id, 'A2' union all
select 3 id, 'A3' union all
select 4 id, 'A4' union all
select 5 id, 'A5'
)
select a.c1, b.c1, c.c1 from table1 a
inner join table1 b on a.id<b.id
inner join table1 c on b.id<c.id
order by a.id, b.id, c.id