问题是这样的:
id name
1 haha
2 haha
3 haha
4 haha
5 wawa
6 wawa
7 wawa
8 123
9 123
10 abc
11 abc
12 fwefewf大家有看明白么?就是name列排列,排列方法按照相同的名字从多到少排列。如果相同的名字数量相同,则按字母序。
求sql语句PHP里发帖没人回答。只好来这了。听说高人很多。帮帮忙,很急
id name
1 haha
2 haha
3 haha
4 haha
5 wawa
6 wawa
7 wawa
8 123
9 123
10 abc
11 abc
12 fwefewf大家有看明白么?就是name列排列,排列方法按照相同的名字从多到少排列。如果相同的名字数量相同,则按字母序。
求sql语句PHP里发帖没人回答。只好来这了。听说高人很多。帮帮忙,很急
from tb
order by len(name),name
order by len(name) desc,name
drop table tb
Go
Create table tb([id] int,[name] nvarchar(7))
Insert tb
select 1,N'haha' union all
select 2,N'haha' union all
select 3,N'haha' union all
select 4,N'haha' union all
select 5,N'wawa' union all
select 6,N'wawa' union all
select 7,N'wawa' union all
select 8,N'123' union all
select 9,N'123' union all
select 10,N'abc' union all
select 11,N'abc' union all
select 12,N'fwefewf'
Go
select *
from tb
order by len(name)desc,name
/*
id name
----------- -------
12 fwefewf
1 haha
2 haha
3 haha
4 haha
5 wawa
6 wawa
7 wawa
8 123
9 123
10 abc
11 abc
*/
(select name , count(1) cnt from tb group by name) n
where m.name = n.name
order by n.cnt desc , m.name
Insert tb
select 1,N'haha' union all
select 2,N'haha' union all
select 3,N'haha' union all
select 4,N'haha' union all
select 5,N'wawa' union all
select 6,N'wawa' union all
select 7,N'wawa' union all
select 8,N'123' union all
select 9,N'123' union all
select 10,N'abc' union all
select 11,N'abc' union all
select 12,N'fwefewf'select m.* from tb m ,
(select name , count(1) cnt from tb group by name) n
where m.name = n.name
order by n.cnt desc , m.namedrop table tb/*
id name
----------- -------
1 haha
2 haha
3 haha
4 haha
5 wawa
6 wawa
7 wawa
8 123
9 123
10 abc
11 abc
12 fwefewf(所影响的行数为 12 行)
*/
from tb
order by count(name),name
group by id ,name
select *
from tb
order by count(name) desc,name
group by id ,name
Create table tb([id] int,[name] nvarchar(7))
Insert tb
select 1,N'haha' union all
select 2,N'haha' union all
select 3,N'haha' union all
select 4,N'haha' union all
select 5,N'wawa' union all
select 6,N'wawa' union all
select 7,N'wawa' union all
select 8,N'123' union all
select 9,N'123' union all
select 10,N'abc' union all
select 11,N'abc' union all
select 12,N'fwefewf'
select *
from tb
group by id ,name
order by count(name) desc,name
Create table tb([id] int,[name] nvarchar(7))
Insert tb
select 1,N'haha' union all
select 2,N'haha' union all
select 3,N'haha' union all
select 4,N'haha' union all
select 5,N'wawa' union all
select 6,N'wawa' union all
select 7,N'wawa' union all
select 8,N'123' union all
select 9,N'123' union all
select 10,N'abc' union all
select 11,N'abc' union all
select 12,N'fwefewf'
select a.*
from tb a
group by a.id ,a.name
order by (select count(b.name) from tb b where a.id =b.id)2 haha
3 haha
4 haha
5 wawa
6 wawa
7 wawa
8 123
9 123
10 abc
11 abc
12 fwefewf