问题是这样的:从一个视图中查出以下数据
SCode Code Grade
5602 00001162 A++
5614 00001162 A++
7010 00001214 A++
7039 00001214 A++
659 00001312 A++
5302 00001312 A++
2754 00002288 A++
821 00002288 A++
2761 00002289 A++
2025 00002289 A++
2119 00002290 A++
1704 00002290 A++
4053 00002291 A++
4066 00002291 A++
4098 00002293 A++
2303 00002293 A++
339 00002294 A++
340 00002294 A++
2502 00002297 A++
759 00002297 A++
1687 00002308 A++
1686 00002308 A++
5080 00002317 A++
5090 00002317 A++
5019 00002318 A++
5005 00002319 A++
4058 00002319 A++
5000 00002320 A++
2008 00002320 A++
5197 00002323 A++
5480 00002323 A++
2646 00002324 A++
6563 00002324 A++
5750 00002329 A++
5755 00002329 A++
5634 00002332 A++
5640 00002332 A++
5650 00002333 A++
5686 00002338 A++
5691 00002338 A++
5723 00002340 A++
5770 00002340 A++
5730 00002341 A++
5743 00002341 A++
5727 00002342 A++
5729 00002342 A++
5677 00002344 A++
5673 00002344 A++
206 00002350 A++
2473 00002351 A++
2472 00002351 A++
3657 00002356 A++
3658 00002356 A++
3164 00002358 A++
6533 00002358 A++
5237 00002360 A++
5210 00002373 A++
5209 00002373 A++
5201 00002374 A++
2264 00002374 A++
2425 00002376 A++
1505 00002376 A++
6535 00002384 A++
971 00002394 A++
2089 00002396 A++
2090 00002396 A++
3092 00002397 A++
5378 00002398 A++
7025 00002398 A++
901 00002398 A++
5398 00002400 A++
5414 00002400 A++
992 00002406 A++
2978 00002412 A++
2979 00002412 A++
197 00002418 A++
5425 00002423 A++
5429 00002423 A++
1833 00002426 A++
1574 00002426 A++
5647 00002428 A++
5641 00002428 A++
5799 00002434 A++
5608 00002440 A++
5607 00002440 A++
5804 00002444 A++
5490 00002444 A++
5643 00002448 A++
5658 00002448 A++
6580 00002450 A++
5502 00002450 A++
5540 00002450 A++
422 00002458 A++
3254 00002458 A++
86 00002461 A++
85 00002461 A++
2235 00002466 A++
2237 00002466 A++
5285 00002469 A++
5291 00002469 A++
5172 00002476 A++
5166 00002476 A++
1044 00002476 A++
7018 00002606 A++
7017 00002606 A++
959 00002759 A++
3084 00002759 A++
SCode是唯一的,把SCode按50个分为一组,要求每一组中的每个SCode的Code不同,并把剩下的不能分的分为一组(一个Code可能对应几个不同的SCode).例如有200个数据,如果能分成两组SCode及其Code不同的,可能分成50,48两组,那就把其余的102分为一组.
很麻烦的问题,哪个高手能帮一下忙,或提供一个思路?先谢过了!
------解决方案--------------------
--有多少个不同的px,就有多少组.
select t.* , px = (select count(*) from tb where code = t.code and scode < t.scode) + 1 from tb t
------解决方案--------------------
- SQL code
--> 测试数据: #if object_id('tempdb.dbo.#') is not null drop table #create table # (SCode int,Code varchar(8),Grade varchar(3))insert into #select 5602,'00001162','A++' union allselect 5614,'00001162','A++' union allselect 7010,'00001214','A++' union allselect 7039,'00001214','A++' union allselect 659,'00001312','A++' union allselect 5302,'00001312','A++' union allselect 2754,'00002288','A++' union allselect 821,'00002288','A++' union allselect 2761,'00002289','A++' union allselect 2025,'00002289','A++' union allselect 2119,'00002290','A++' union allselect 1704,'00002290','A++' union allselect 4053,'00002291','A++' union allselect 4066,'00002291','A++' union allselect 4098,'00002293','A++' union allselect 2303,'00002293','A++' union allselect 339,'00002294','A++' union allselect 340,'00002294','A++' union allselect 2502,'00002297','A++' union allselect 759,'00002297','A++' union allselect 1687,'00002308','A++' union allselect 1686,'00002308','A++' union allselect 5080,'00002317','A++' union allselect 5090,'00002317','A++' union allselect 5019,'00002318','A++' union allselect 5005,'00002319','A++' union allselect 4058,'00002319','A++' union allselect 5000,'00002320','A++' union allselect 2008,'00002320','A++' union allselect 5197,'00002323','A++' union allselect 5480,'00002323','A++' union allselect 2646,'00002324','A++' union allselect 6563,'00002324','A++' union allselect 5750,'00002329','A++' union allselect 5755,'00002329','A++' union allselect 5634,'00002332','A++' union allselect 5640,'00002332','A++' union allselect 5650,'00002333','A++' union allselect 5686,'00002338','A++' union allselect 5691,'00002338','A++' union allselect 5723,'00002340','A++' union allselect 5770,'00002340','A++' union allselect 5730,'00002341','A++' union allselect 5743,'00002341','A++' union allselect 5727,'00002342','A++' union allselect 5729,'00002342','A++' union allselect 5677,'00002344','A++' union allselect 5673,'00002344','A++' union allselect 206,'00002350','A++' union allselect 2473,'00002351','A++' union allselect 2472,'00002351','A++' union allselect 3657,'00002356','A++' union allselect 3658,'00002356','A++' union allselect 3164,'00002358','A++' union allselect 6533,'00002358','A++' union allselect 5237,'00002360','A++' union allselect 5210,'00002373','A++' union allselect 5209,'00002373','A++' union allselect 5201,'00002374','A++' union allselect 2264,'00002374','A++' union allselect 2425,'00002376','A++' union allselect 1505,'00002376','A++' union allselect 6535,'00002384','A++' union allselect 971,'00002394','A++' union allselect 2089,'00002396','A++' union allselect 2090,'00002396','A++' union allselect 3092,'00002397','A++' union allselect 5378,'00002398','A++' union allselect 7025,'00002398','A++' union allselect 901,'00002398','A++' union allselect 5398,'00002400','A++' union allselect 5414,'00002400','A++' union allselect 992,'00002406','A++' union allselect 2978,'00002412','A++' union allselect 2979,'00002412','A++' union allselect 197,'00002418','A++' union allselect 5425,'00002423','A++' union allselect 5429,'00002423','A++' union allselect 1833,'00002426','A++' union allselect 1574,'00002426','A++' union allselect 5647,'00002428','A++' union allselect 5641,'00002428','A++' union allselect 5799,'00002434','A++' union allselect 5608,'00002440','A++' union allselect 5607,'00002440','A++' union allselect 5804,'00002444','A++' union allselect 5490,'00002444','A++' union allselect 5643,'00002448','A++' union allselect 5658,'00002448','A++' union allselect 6580,'00002450','A++' union allselect 5502,'00002450','A++' union allselect 5540,'00002450','A++' union allselect 422,'00002458','A++' union allselect 3254,'00002458','A++' union allselect 86,'00002461','A++' union allselect 85,'00002461','A++' union allselect 2235,'00002466','A++' union allselect 2237,'00002466','A++' union allselect 5285,'00002469','A++' union allselect 5291,'00002469','A++' union allselect 5172,'00002476','A++' union allselect 5166,'00002476','A++' union allselect 1044,'00002476','A++' union allselect 7018,'00002606','A++' union allselect 7017,'00002606','A++' union allselect 959,'00002759','A++' union allselect 3084,'00002759','A++'--> 2000if object_id('tempdb.dbo.#T') is not null drop table #Tselect id=identity(int,0,1),cn=(select count(Scode) from # where Code=a.Code),* into #T from # a order by 2declare @max intselect @max=max(id)/50+2 from #T where cn=1select GroupNo=case cn when 1 then id/50+1 else @max end,SCode,Code,Grade from #T order by 1,2,3