有点难度的SQL语句
例如表如下:
season body color
K GC001C 03
K GC001C 84
K GC002C 03
K GC002C 67
K GC002C 70
J GC003C 03
J GC003C 20
我想得到如下结果:
season body color
K GC001C 03
K GC001C 84
K GC001C null
K GC001C null
K GC002C 03
K GC002C 67
K GC002C 70
K GC002C null
J GC003C 03
J GC003C null
J GC003C null
J GC003C null
意思是按照season,body分组,4条记录为一组,不够的就补足,其中color用null补足.
如何实现呢?
[解决办法]
create table #
(season varchar(5), body varchar(10), color varchar(5))
insert into # select 'K ', 'GC001C ', '03 '
union all select 'K ', 'GC001C ', '84 '
union all select 'K ', 'GC002C ', '03 '
union all select 'K ', 'GC002C ', '67 '
union all select 'K ', 'GC002C ', '70 '
union all select 'J ', 'GC003C ', '03 '
union all select 'J ', 'GC003C ', '20 '
select * from #
union all
select season,body,null from
(select season,body,count(1)[num] from # group by season,body having count(1) <4)a
,
(select 1[newnum] union select 2 union select 3 union select 4)b
where b.newnum> a.num
order by season,body,color desc
season body color
------ ---------- -----
J GC003C 20
J GC003C 03
J GC003C NULL
J GC003C NULL
K GC001C 84
K GC001C 03
K GC001C NULL
K GC001C NULL
K GC002C 70
K GC002C 67
K GC002C 03
K GC002C NULL
------解决方案--------------------
if object_id( 'tempdb..#temp ') is not null
drop table #temp
go
create table #temp(season varchar(1) not null,body varchar(10) not null,color varchar(10) null)
go
insert #temp
select 'K ', 'GC001C ', '03 ' union all
select 'K ', 'GC001C ', '84 ' union all
select 'K ', 'GC002C ', '03 ' union all
select 'K ', 'GC002C ', '67 ' union all
select 'K ', 'GC002C ', '70 ' union all
select 'J ', 'GC003C ', '03 ' union all
select 'J ', 'GC003C ', '20 '
select * from #temp
/*
season body color
------ ---------- ----------
K GC001C 03
K GC001C 84
K GC002C 03
K GC002C 67
K GC002C 70
J GC003C 03
J GC003C 20
*/
select * from
(
select * from #temp
union all
select a.season,a.body,NULL color from (select season,body,4-count(*) cn from #temp group by season,body ) a,
(
select 1 row union all select 2 union all select 3 union all select 4
) b where a.cn> =b.row
) a
order by 1,2,case when color is null then 1 else 0 end,3
/*
season body color
------ ---------- ----------
J GC003C 03
J GC003C 20
J GC003C NULL
J GC003C NULL
K GC001C 03
K GC001C 84
K GC001C NULL
K GC001C NULL
K GC002C 03
K GC002C 67
K GC002C 70
K GC002C NULL
*/
