N_id N_abc N_yn
111 222 kk
112 222 pp
113 333 kk
114 333 pp
115 333 pp
我想选择出这样的结果
在前台显示(按行数的大小排列)所属类 总行数 主要参数(kk对应的id)
333 3 111
222 2 113
111 222 kk
112 222 pp
113 333 kk
114 333 pp
115 333 pp
我想选择出这样的结果
在前台显示(按行数的大小排列)所属类 总行数 主要参数(kk对应的id)
333 3 111
222 2 113
333 3 111
222 2 113 ----------------
樓主,你的結果打錯了吧
insert @tablename select '111','222','kk'
union all select '112','222','pp'
union all select '113','333','kk'
union all select '114','333','pp'
union all select '115','333','pp'select a.所属类,a.总行数,b.N_id as 主要参数 from (select N_abc as 所属类,count(*) as 总行数 from @tablename group by N_abc) a,@tablename b where a.所属类=b.N_abc and b.N_yn='kk'
111 222 kk
112 222 pp
113 333 kk
114 333 pp
115 333 pp
333 3 111
222 2 113
select N_abc,count(*) N_count,(select top 1 n_id from 表名 where n_yn='kk') n_id
from 表名 group by n_abc order by 2
select N_abc,count(*) N_count,(select top 1 n_id from 表名 where n_yn='kk') n_id
from 表名 group by n_abc order by 2 desc
insert into tb values(111, 222, 'kk')
insert into tb values(112, 222, 'pp')
insert into tb values(113, 333, 'kk')
insert into tb values(114, 333, 'pp')
insert into tb values(115, 333, 'pp')
go
select a.N_abc 所属类,a.cnt 总行数,b.N_id '主要参数(kk对应的id)' from
(select N_abc ,count(*) cnt from tb group by N_abc) a,
(select * from tb where N_yn = 'kk') b
where a.N_abc = b.N_abc
order by a.cnt desc
drop table tb/*
所属类 总行数 主要参数(kk对应的id)
----------- ----------- -------------
333 3 113
222 2 111(所影响的行数为 2 行)
*/
应加desc从大到小排列
select N_abc,count(*) N_count,(select top 1 n_id from 表名 where n_yn='kk') n_id
from 表名 group by n_abc order by 2 desc------------------------
--你取的全部是111select N_abc,count(*) N_count,(select n_id from 表名 where n_yn='kk' and N_abc=a.N_abc) n_id
from 表名 a group by n_abc order by count(*) desc