整理的几个常用mysql语句

查询一年中的12个月的统计数据

select dno,sum(case when substring(datetime,6,2)=4 then 1 else 0 end) '4' ,
sum(case when substring(datetime,6,2)=5 then 1 else 0 end) '5',
sum(case when substring(datetime,6,2)=6 then 1 else 0 end) '6',
sum(case when substring(datetime,6,2)=7 then 1 else 0 end) '7',
count(*)  'total'
from dept
group by dno;

查询出总数大于2的部门跟部门的总数，分组查询

select dno,count(*)
from dept
group by dno
having count(*)>2;

 

查询成绩都大于80的人的姓名

select distinct name
 from source
where name
not in(select distinct name from source where fenshu<=80);

查询出姓名相同的记录（显示的姓名一样的记录）

两种语句
select * from dept where name in (select name from dept group by name having count(*) >1) group by name;
select * from dept group by name having count(name)>1;多此一举

查询出姓名相同的记录 并把所有的都显示出来
 select * from dept where name in (select name from dept group by name having count(*) >1);

查询出姓名相同的记录 并把所有的 都显示出来 排序显示出来

select * from dept where name in (select name from dept group by name having count(*) >1) order by name;

查询12个月的统计数量 按月统计

select sum(case when substring(datetime,6,2)='01' then 1 else 0 end ) '1月',
    -> sum(case when substring(datetime,6,2)='02' then 1 else 0 end ) '2月',
    -> sum(case when substring(datetime,6,2)='03' then 1 else 0 end ) '3月',
    -> sum(case when substring(datetime,6,2)='04' then 1 else 0 end ) '4月',
    -> sum(case when substring(datetime,6,2)='05' then 1 else 0 end ) '5月',
    -> sum(case when substring(datetime,6,2)='06' then 1 else 0 end ) '6月',
    -> sum(case when substring(datetime,6,2)='07' then 1 else 0 end ) '7月',
    -> sum(case when substring(datetime,6,2)='08' then 1 else 0 end ) '8月',
    -> sum(case when substring(datetime,6,2)='09' then 1 else 0 end ) '9月',
    -> sum(case when substring(datetime,6,2)='10' then 1 else 0 end ) '10月',
    -> sum(case when substring(datetime,6,2)='11' then 1 else 0 end ) '11月',
    -> sum(case when substring(datetime,6,2)='12' then 1 else 0 end ) '12月'
    -> from dept;

分组查询记录 查询出条数大于3的组的 成员id最大的那项

select * from dept where nameid in (select max(nameid) from dept where dno in (select dno from dept group by dno having count(*)>3) group by dno);

查询出出生在6月的并且id最大的那个记录

select * from dept where nameid = (select max(nameid) from dept where substring(datetime,6,2)='06');

查询年月日以这种形式显示（2010年06月06日 ）
 select concat(concat(substring(datetime,1,4),'年'),concat(substring(datetime,6,2),'月'),concat(substring(datetime,9,2),'日')) from dept;