查询数据的百分比
查询表的男女比例
查询jeecgboot表中的男女比例(sex字段,1为男性,0为女性)
- 分别查询男女人数
-- 查询男女人数
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;
查询年龄
获取系统的时间减去出生日期得到天数在除以365天
--不唯一
select
name,year(now())-year(birthday)as age
from demo
数据排序
根据年龄排序
order by 语句
asc升序,desc降序
select
name,year(now())-year(birthday)as age
from demo
order by age asc
添加查询的列属性
可以使用row_number()
over内部根据name排序
select
name,year(now())-year(birthday)as age,row_number()over(order by name) as "排序列"
from demo
order by name
UNION合并查询
UNION ALL可以重复
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