declare @t table(city varchar(10), sex varchar(10), photo varchar(10))
insert @t
select '武汉', '女', '有照片' union all
select '武汉', '女', '有照片' union all
select '武汉', '女', '有照片' union all
select '武汉', '女', '无照片' union all
select '武汉', '男', '无照片' union all
select '广州', '女', '无照片' union all
select '广州', '女', '无照片' union all
select '武汉', '男', '无照片' union all
select '深圳', '男', '无照片'select top 1 city,sex,photo from
(select city,sex,photo,count(*) as num from @t group by city,sex,photo) as a
order by num DESC/*结果
city sex photo
---------- ---------- ----------
武汉 女 有照片
*/
insert @t
select '武汉', '女', '有照片' union all
select '武汉', '女', '有照片' union all
select '武汉', '女', '有照片' union all
select '武汉', '女', '无照片' union all
select '武汉', '男', '无照片' union all
select '广州', '女', '无照片' union all
select '广州', '女', '无照片' union all
select '武汉', '男', '无照片' union all
select '深圳', '男', '无照片'select top 1 city,sex,photo from
(select city,sex,photo,count(*) as num from @t group by city,sex,photo) as a
order by num DESC/*结果
city sex photo
---------- ---------- ----------
武汉 女 有照片
*/
insert @t
select '武汉', '女', '有照片' union all
select '武汉', '女', '有照片' union all
select '武汉', '女', '有照片' union all
select '武汉', '女', '无照片' union all
select '武汉', '男', '无照片' union all
select '广州', '女', '无照片' union all
select '广州', '女', '无照片' union all
select '武汉', '男', '无照片' union all
select '深圳', '男', '无照片'Select TOP 1 city, sex, photo From @t Group By city, sex, photo Order By Count(*) Desc/*结果
city sex photo
---------- ---------- ----------
武汉 女 有照片
*/
where city=(select top 1 city from dt group by city order by count(*) desc )
and sex=(select top 1 sex from dt
where city=(select top 1 city from dt group by city order by count(*) desc)
group by recvmes order by count(*) desc)
group by city, sex, photo order by count(*) desc
where city=(select top 1 city from @t group by city order by count(*) desc )
and sex=(select top 1 sex from @t
where city=(select top 1 city from @t group by city order by count(*) desc)
group by sex order by count(*) desc)
group by city, sex, photo order by count(*) desc