现有一张表:
id qx xzmc
1 aaa bbb
2 aaa bbb
3 hhh ddd
4 hhh eee根据这张表要求显示内容如下:
id qx xzmc count
1 aaa bbb 2
2 hhh ddd 1
3 hhh eee 1现根据group 对qx 和xzmc进行分类汇总,但如何把ID也显示出来呢?请大家写一下语句,谢谢
id qx xzmc
1 aaa bbb
2 aaa bbb
3 hhh ddd
4 hhh eee根据这张表要求显示内容如下:
id qx xzmc count
1 aaa bbb 2
2 hhh ddd 1
3 hhh eee 1现根据group 对qx 和xzmc进行分类汇总,但如何把ID也显示出来呢?请大家写一下语句,谢谢
(select qx , xzmc , count(1) [count] from tb group by qx , xzmc) m where m.qx < n.qx or (m.qx = n.qx and m.xzmc < n.xzmc)) + 1 , * from
(select qx , xzmc , count(1) [count] from tb group by qx , xzmc) n
order by id
insert into tb values(1 ,'aaa', 'bbb')
insert into tb values(2 ,'aaa', 'bbb')
insert into tb values(3 ,'hhh', 'ddd')
insert into tb values(4 ,'hhh', 'eee')
goselect id = (select count(1) from
(select qx , xzmc , count(1) [count] from tb group by qx , xzmc) m where m.qx < n.qx or (m.qx = n.qx and m.xzmc < n.xzmc)) + 1 , * from
(select qx , xzmc , count(1) [count] from tb group by qx , xzmc) n
order by iddrop table tb/*
id qx xzmc count
----------- ---------- ---------- -----------
1 aaa bbb 2
2 hhh ddd 1
3 hhh eee 1(所影响的行数为 3 行)*/如果是sql 2005则如下:
select row_number() over(order by qx,xzmc) id , * from
(
select qx , xzmc , count(1) [count] from tb group by qx , xzmc
) t