Mysql语句_查询数据百分比、人员年龄、数据排序、添加查询时的列属性、合并查询结果

-- 查询男女人数
select 
case sex
when 1 then '男'
else '女' end as sex,count(sex) as peoplecount
from demo
group by sex

-- 查询总人数
select sum(b.peoplecount)
from (select 
case sex
when 1 then '男'
else '女' end as sex,count(sex) as peoplecount
from demo
group by sex)b;

select 
case a.sex
when 1 then '男' else '女' end as sex,
count(a.sex)/(
select sum(b.peoplecount)
from (select 
case sex
when 1 then '男'
else '女' end as sex,count(sex) as peoplecount
from demo
group by sex) b ) as percent
from demo a
group by sex;

--不唯一
select 
name,year(now())-year(birthday)as age
from demo

select 
name,year(now())-year(birthday)as age
from demo
order by age asc

select 
name,year(now())-year(birthday)as age,row_number()over(order by name) as "排序列"
from demo
order by name

select 
case sex
when 1 then '男'
else '女' end as sex,count(sex) as peoplecount
from demo
group by sex
union all
select 
case sex
when 1 then '男'
else '女' end as sex,count(sex) as peoplecount
from demo
group by sex