declare @t table (plan_no varchar(20),source_port varchar(30)) insert @t select '200512016','鲅鱼圈抚顺' union all select '200512071','鲅鱼圈抚顺' union all select '200512080','鲅鱼圈抚顺' union all select '200512096','鲅鱼圈辽河' union all select '200512052','鲅鱼圈辽河' union all select '200512095','鲅鱼圈辽河' union all select '200512072','鲅鱼圈中转' union all select '200512055','鲅鱼圈中转' union all select '200512103','鲅鱼圈中转' union all select '200512054','鲅鱼圈中转' union all select '200512003','笔架山锦西' union all select '200512031','笔架山锦西' union all select '200512008','笔架山锦西' union all select '200512127','笔架山锦州' union all select '200512074','笔架山锦州' union all select '200512015','笔架山锦州' union all select '200512025','笔架山锦州' union all select '200512136','大港' union all select '200512006','甘井子' union all select '200512128-2','甘井子' union all select '200512130','甘井子' union all select '200512011','甘井子' union all select '200512076','甘井子' union all select '200512022','甘井子' union all select '200512002','甘井子' union all select '200512026','甘井子' union all select '200512113','甘井子' union all select '200512043','甘井子转运' union all select '200512038','甘井子转运' union all select '200512039','甘井子转运' union all select '200512134','葫芦岛' union all select '200512128-1','鲶鱼湾' union all select '200512135-1','鲶鱼湾' union all select '200512032','鲶鱼湾' union all select '200512060','鲶鱼湾' union all select '200512097','鲶鱼湾' union all select '200512128-4','鲶鱼湾' union all select '外采200512001','上海金山' union all select '外采200512002','上海金山' union all select '外采200512003','上海金山' union all select '2005200512005','寺儿沟' union all select '200512116','寺儿沟' union all select '200512012','寺儿沟' union all select '200512017','寺儿沟' union all select '200512109','寺儿沟' select source_port,(select top 1 plan_no from @t a where a.source_port=b.source_port order by newid()) as plan_no from @t b group by source_port source_port plan_no ------------------------------ -------------------- 鲅鱼圈抚顺 200512016 鲅鱼圈辽河 200512096 鲅鱼圈中转 200512054 笔架山锦西 200512008 笔架山锦州 200512127 大港 200512136 甘井子 200512002 甘井子转运 200512039 葫芦岛 200512134 鲶鱼湾 200512060 上海金山 外采200512002 寺儿沟 200512012(所影响的行数为 12 行)
(plan_no varchar(20),source_port varchar(30))
insert @t
select '200512016','鲅鱼圈抚顺' union all
select '200512071','鲅鱼圈抚顺' union all
select '200512080','鲅鱼圈抚顺' union all
select '200512096','鲅鱼圈辽河' union all
select '200512052','鲅鱼圈辽河' union all
select '200512095','鲅鱼圈辽河' union all
select '200512072','鲅鱼圈中转' union all
select '200512055','鲅鱼圈中转' union all
select '200512103','鲅鱼圈中转' union all
select '200512054','鲅鱼圈中转' union all
select '200512003','笔架山锦西' union all
select '200512031','笔架山锦西' union all
select '200512008','笔架山锦西' union all
select '200512127','笔架山锦州' union all
select '200512074','笔架山锦州' union all
select '200512015','笔架山锦州' union all
select '200512025','笔架山锦州' union all
select '200512136','大港' union all
select '200512006','甘井子' union all
select '200512128-2','甘井子' union all
select '200512130','甘井子' union all
select '200512011','甘井子' union all
select '200512076','甘井子' union all
select '200512022','甘井子' union all
select '200512002','甘井子' union all
select '200512026','甘井子' union all
select '200512113','甘井子' union all
select '200512043','甘井子转运' union all
select '200512038','甘井子转运' union all
select '200512039','甘井子转运' union all
select '200512134','葫芦岛' union all
select '200512128-1','鲶鱼湾' union all
select '200512135-1','鲶鱼湾' union all
select '200512032','鲶鱼湾' union all
select '200512060','鲶鱼湾' union all
select '200512097','鲶鱼湾' union all
select '200512128-4','鲶鱼湾' union all
select '外采200512001','上海金山' union all
select '外采200512002','上海金山' union all
select '外采200512003','上海金山' union all
select '2005200512005','寺儿沟' union all
select '200512116','寺儿沟' union all
select '200512012','寺儿沟' union all
select '200512017','寺儿沟' union all
select '200512109','寺儿沟'
select source_port,(select top 1 plan_no from @t a where a.source_port=b.source_port order by newid())
as plan_no from @t b group by source_port
source_port plan_no
------------------------------ --------------------
鲅鱼圈抚顺 200512016
鲅鱼圈辽河 200512096
鲅鱼圈中转 200512054
笔架山锦西 200512008
笔架山锦州 200512127
大港 200512136
甘井子 200512002
甘井子转运 200512039
葫芦岛 200512134
鲶鱼湾 200512060
上海金山 外采200512002
寺儿沟 200512012(所影响的行数为 12 行)