从视图中查询数据并分类
问题是这样的:从一个视图中查出以下数据
SCode Code Grade
560200001162A++
561400001162A++
701000001214A++
703900001214A++
65900001312A++
530200001312A++
275400002288A++
82100002288A++
276100002289A++
202500002289A++
211900002290A++
170400002290A++
405300002291A++
406600002291A++
409800002293A++
230300002293A++
33900002294A++
34000002294A++
250200002297A++
75900002297A++
168700002308A++
168600002308A++
508000002317A++
509000002317A++
501900002318A++
500500002319A++
405800002319A++
500000002320A++
200800002320A++
519700002323A++
548000002323A++
264600002324A++
656300002324A++
575000002329A++
575500002329A++
563400002332A++
564000002332A++
565000002333A++
568600002338A++
569100002338A++
572300002340A++
577000002340A++
573000002341A++
574300002341A++
572700002342A++
572900002342A++
567700002344A++
567300002344A++
20600002350A++
247300002351A++
247200002351A++
365700002356A++
365800002356A++
316400002358A++
653300002358A++
523700002360A++
521000002373A++
520900002373A++
520100002374A++
226400002374A++
242500002376A++
150500002376A++
653500002384A++
97100002394A++
208900002396A++
209000002396A++
309200002397A++
537800002398A++
702500002398A++
90100002398A++
539800002400A++
541400002400A++
99200002406A++
297800002412A++
297900002412A++
19700002418A++
542500002423A++
542900002423A++
183300002426A++
157400002426A++
564700002428A++
564100002428A++
579900002434A++
560800002440A++
560700002440A++
580400002444A++
549000002444A++
564300002448A++
565800002448A++
658000002450A++
550200002450A++
554000002450A++
42200002458A++
325400002458A++
8600002461A++
8500002461A++
223500002466A++
223700002466A++
528500002469A++
529100002469A++
517200002476A++
516600002476A++
104400002476A++
701800002606A++
701700002606A++
95900002759A++
308400002759A++
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
[解决办法]
--> 测试数据: #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