sql匹配查询排序

[翠花琳]

表A中有Address,Aname,Asex三个字段
insert into a values('上海','张三','男')
insert into a values('北京','李四','女')
insert into a values('深圳','王五','男')
insert into a values('上海','赵六','女')
insert into a values('北京','陈七','男')
insert into a values('上海','张三','男')
...
查询所有结果,地址最多的排第一，姓名排第二，性别第三，输出结果
Address Aname Asex
上海 张三 男
上海 张三 男
上海 赵六 女
北京 ....
....




请教如何实现?

[ACMAIN_CHM]

select Address,Aname,Asex
from 表A t
order by (select count(*) from 表A where Aname= t.Aname) desc,Aname

[aimee_99]

select * 
from ta a
order by isnull((select count(*) from ta where Address=a.Address),0)desc,
         isnull((select count(*) from ta where Aname=a.Aname),0)desc,
         isnull((select count(*) from ta where Asex=a.Asex),0)desc

[ACMAIN_CHM]

select t.*
from 表A t inner join (select Aname,count(*) as cnt from 表A group by Aname) b
	on t.Aname=b.Aname
order by cnt desc,a.Aname

[dawugui]

create table A(Address varchar(10),Aname varchar(10),Asex varchar(10))
insert into a values('上海','张三','男') 
insert into a values('北京','李四','女') 
insert into a values('深圳','王五','男') 
insert into a values('上海','赵六','女') 
insert into a values('北京','陈七','男') 
insert into a values('上海','张三','男') 
go

select a.* from a 
left join
(select Address , count(1) cnt from a group by Address) b
on a.Address = b.Address
left join
(select Aname , count(1) cnt from a group by Aname) c
on a.Aname = c.Aname
left join
(select Asex , count(1) cnt from a group by Asex) d
on a.Asex = d.Asex
order by b.cnt desc , c.cnt desc , d.cnt desc



drop table a 


/*
Address    Aname      Asex       
---------- ---------- ---------- 
上海         张三         男
上海         张三         男
上海         赵六         女
北京         陈七         男
北京         李四         女
深圳         王五         男

（所影响的行数为 6 行）


*/

[--小F--]

select
 * 
from
 tb t
order by
 isnull((select count(1) from tb where Address=t.Address),0)desc,
 isnull((select count(1) from tb where Aname=t.Aname),0)desc,
 isnull((select count(1) from tb where Asex=t.Asex),0)desc

[sgtzzc]

select a.*
from a
left join
(select Address,count(1)px1 from a group by Address)b
on a.Address=b.Address 
left join 
(select Aname,count(1)px2 from a group by Aname)c
on a.Aname=c.Aname
left join
(select Asex,count(1)px3 from a group by Asex)d
on a.Asex=b.Asex
order by
  b.px1 desc,
  c.px2 desc,
  d.px3

[翠花琳]

测试结果不正确!

[--小F--]

--抄袭乌龟数据
create table a(Address varchar(10),Aname varchar(10),Asex varchar(10))
insert into a values('上海','张三','男') 
insert into a values('北京','李四','女') 
insert into a values('深圳','王五','男') 
insert into a values('上海','赵六','女') 
insert into a values('北京','陈七','男') 
insert into a values('上海','张三','男') 
go

select
 * 
from
 a t
order by
 isnull((select count(1) from a where Address=t.Address),0)desc,
 isnull((select count(1) from a where Aname=t.Aname),0)desc,
 isnull((select count(1) from a where Asex=t.Asex),0)desc
/*Address    Aname      Asex
---------- ---------- ----------
上海         张三         男
上海         张三         男
上海         赵六         女
北京         陈七         男
北京         李四         女
深圳         王五         男

(6 行受影响)
*/

drop table a

[sgtzzc]

[Quote=引用 4 楼 dawugui 的回复:]
SQL codecreatetable A(Addressvarchar(10),Anamevarchar(10),Asexvarchar(10))insertinto avalues('上海','张三','男')insertinto avalues('北京','李四','女')insertinto avalues('深圳','王五','男')insertinto avalues('上海','赵六?-
[/Quote]

乌龟这速度！

[ACMAIN_CHM]

[Quote= #7楼 chensirbbk(chensirbbk) ]测试结果不正确![/Quote]

1> select * from a
2> go
Address   |Aname     |Asex
----------|----------|----------
上海        |张三        |男
北京        |李四        |女
深圳        |王五        |男
上海        |赵六        |女
北京        |陈七        |男
上海        |张三        |男

(6 rows affected)
1> select Address,Aname,Asex
2> from a t
3> order by (select count(*) from a where Aname= t.Aname) desc,Aname
4> go
Address   |Aname     |Asex
----------|----------|----------
上海        |张三        |男
上海        |张三        |男
北京        |陈七        |男
北京        |李四        |女
深圳        |王五        |男
上海        |赵六        |女

(6 rows affected)
1>

[Mr_Nice]

select
 * 
from
 a t
order by
 isnull((select count(1) from a where Address=t.Address),0)desc,
 isnull((select count(1) from a where Aname=t.Aname),0)desc,
 isnull((select count(1) from a where Asex=t.Asex),0)desc

[ACMAIN_CHM]

1> select t.*
2> from a t inner join (select Aname,count(*) as cnt from a group by Aname) b
3>     on t.Aname=b.Aname
4> order by cnt desc,t.Aname
5> go
Address   |Aname     |Asex
----------|----------|----------
上海        |张三        |男
上海        |张三        |男
北京        |陈七        |男
北京        |李四        |女
深圳        |王五        |男
上海        |赵六        |女

(6 rows affected)
1>

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[黄_瓜]

[Quote=引用 7 楼 chensirbbk 的回复:]
测试结果不正确!
[/Quote]
你想要什么结果？

create table A(Address varchar(10),Aname varchar(10),Asex varchar(10))
insert into a values('上海','张三','男') 
insert into a values('北京','李四','女') 
insert into a values('深圳','王五','男') 
insert into a values('上海','赵六','女') 
insert into a values('北京','陈七','男') 
insert into a values('上海','张三','男') 
go

select * from a as b  order by
 isnull((select count(Address) from  a  where Address=b.Address),0) desc ,
 isnull((select count(Aname) from  a  where Aname=b.Aname),0) desc ,
 isnull((select count(Asex) from  a  where Asex=b.Asex),0) desc
/*
Address    Aname      Asex
---------- ---------- ----------
上海         张三         男
上海         张三         男
上海         赵六         女
北京         陈七         男
北京         李四         女
深圳         王五         男

(6 行受影响)


*/