27,580
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(2),[物品] nvarchar(2))
Insert #T
select N'小明',N'杯子' union all
select N'小明',N'手机' union all
select N'小红',N'画笔' union all
select N'小红',N'衣服' union all
select N'小红',N'眼睛' union all
select N'小刚',N'钱包' union all
select N'小刚',N'裤子'
Go
declare @s nvarchar(4000),@i varchar(5)
Select top 1 @i=COUNT(1),@s='' from #T group by [姓名] order by COUNT(1) desc
if not object_id('Tempdb..#T1') is null
drop table #T1
select *,ID=IDENTITY(int,1,1),RN=cast(null as int) into #T1 from #T
update a
set RN=(select COUNT(1) from #T1 where [姓名]=a.[姓名] and ID<=a.ID)
from #T1 as a
SELECT [姓名],[物品] FROM #T1 ORDER BY rn,id
/*
姓名 物品
小明 杯子
小红 画笔
小刚 钱包
小明 手机
小红 衣服
小刚 裤子
小红 眼睛
*/
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(2),[物品] nvarchar(2))
Insert #T
select N'小明',N'杯子' union all
select N'小明',N'手机' union all
select N'小红',N'画笔' union all
select N'小红',N'衣服' union all
select N'小红',N'眼睛' union all
select N'小刚',N'钱包' union all
select N'小刚',N'裤子'
Go
declare @s nvarchar(4000),@i varchar(5)
Select top 1 @i=COUNT(1),@s='' from #T group by [姓名] order by COUNT(1) desc
if not object_id('Tempdb..#T1') is null
drop table #T1
select *,ID=IDENTITY(int,1,1),RN=cast(null as int) into #T1 from #T
update a
set RN=(select COUNT(1) from #T1 where [姓名]=a.[姓名] and ID<=a.ID)
from #T1 as a
while @i>0
select @s=' UNION ALL SELECT [姓名],[物品] FROM #T1 WHERE RN='+@i+@s ,@i=@i-1
set @s=STUFF(@s,1,10,'')
exec(@s)
/*
姓名 物品
小明 杯子
小红 画笔
小刚 钱包
小明 手机
小红 衣服
小刚 裤子
小红 眼睛
*/
;with a
as
(
select
[姓名],[物品],
rn=ROW_NUMBER()over(partition by [姓名] order by rn)
from
(select
*,rn=ROW_NUMBER()over(order by rand())
from #T
)t
)
select * from a where rn=1--轮数
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(2),[物品] nvarchar(2))
Insert #T
select N'小明',N'杯子' union all
select N'小明',N'手机' union all
select N'小红',N'画笔' union all
select N'小红',N'衣服' union all
select N'小红',N'眼睛' union all
select N'小刚',N'钱包' union all
select N'小刚',N'裤子'
Go
declare @s nvarchar(4000),@i varchar(5)
Select top 1 @i=COUNT(1),@s='' from #T group by [姓名] order by COUNT(1) desc
while @i>0
select @s=',[第'+@i+'轮]=max(case when rn='+@i+' then [物品] else '''' end)'+@s ,@i=@i-1
exec('select [姓名]'+@s+' from (select *,rn=row_number()over(partition by [姓名] order by rand()) from #T) as T group by [姓名] order by min(rn)')
/*
姓名 第1轮 第2轮 第3轮
小刚 钱包 裤子
小红 画笔 衣服 眼睛
小明 杯子 手机
*/
declare @T table (姓名 varchar(4),物品 varchar(4))
insert into @T
select '小明','杯子' union all
select '小明','手机' union all
select '小红','画笔' union all
select '小红','衣服' union all
select '小红','眼睛' union all
select '小刚','钱包' union all
select '小刚','裤子'
declare @i int set @i=3 --这个表示轮数(你可以改成1,2,3,4等分别看看结果)
;with maco as
(
select row_number() over
(partition by 姓名 order by (select 1)) as rid, * from @T
)
select * from maco t
where rid=
case when @i%(select count(1) from @T where 姓名=t.姓名)=0 then (select count(1) from @T where 姓名=t.姓名)
else @i%(select count(1) from @T where 姓名=t.姓名) end
with t(id,姓名,物品) as(
select 1,'小明','杯子'
union all select 2,'小明','手机'
union all select 3,'小红','画笔'
union all select 4,'小红','衣服'
union all select 5,'小红','眼睛'
union all select 6,'小刚','钱包'
union all select 7,'小刚','裤子'
)
select 姓名,物品 from (
select row_number() over (partition by 姓名 order by id) rn,id,姓名,物品 from t) t1
order by rn,id
/*
姓名 物品
---- ----
小明 杯子
小红 画笔
小刚 钱包
小明 手机
小红 衣服
小刚 裤子
小红 眼睛
(7 行受影响)
*/