mysql统计语句,mysql 常用的统计语句

drop table if exists test;

create table test

select '2005-05-09' date,'胜' flag union all

select '2005-05-09','胜' union all

select '2005-05-09','负' union all

select '2005-05-09','负' union all

select '2005-05-10','胜' union all

select '2005-05-10','负' union all

select '2005-05-10','负';

drop table if exists test1;

create table test1

select date,

sum(case when flag='胜' then 1 else 0 end) win,

sum(case when flag='负' then 1 else 0 end) fail

from test

group by date;

drop table if exists yj;

create table yj

select '一月份' mon, 01 dep,10 yj union all

select '一月份' mon, 02 dep,10 yj union all

select '一月份' mon, 03 dep, 5 yj union all

select '二月份' mon, 02 dep, 8 yj union all

select '二月份' mon, 04 dep, 9 yj union all

select '三月份' mon, 03 dep, 8 yj;

drop table if exists dep;

create table dep

select '01' dep,'国内业务一部' dname union all

select '02' dep,'国内业务二部' dname union all

select '03' dep,'国内业务三部' dname union all

select '04' dep,'国际业务部' dname;

select a.dep,

max(case when b.mon='一月份' then b.yj else null end) yj1,

max(case when b.mon='二月份' then b.yj else null end) yj2,

max(case when b.mon='三月份' then b.yj else null end) yj3

from dep a

join yj b on a.dep=b.dep

group by a.dep;

drop table if exists A;

create table A

select 1 id, 28 score union all

select 2 id, 33 score union all

select 3 id, 33 score union all

select 4 id, 89 score union all

select 5 id, 99 score union all

select 6 id, 68 score union all

select 7 id, 68 score union all

select 8 id, 78 score union all

select 9 id, 88 score union all

select 10 id, 90 score;

select * from (

select t.*,@rank := @rank+1 rank

from

(select * from A order by score) t,(select @rank := 0) b

) c;

# rownum

drop table if exists emp;

create table emp (empid int ,deptid int ,salary decimal(10,2) );

insert into emp values

(1,10,5500.00),

(2,10,4500.00),

(3,20,1900.00),

(4,20,4800.00),

(5,40,6500.00),

(6,40,14500.00),

(7,40,44500.00),

(8,50,6500.00),

(9,50,7500.00);

# row_number() over (partition by col1 order by col2)

select empid,

deptid,

salary,

rank

from (

select t.empid,

t.deptid,

t.salary,

@rownum:=@rownum+1,

if(@pdept=t.deptid,@rank:=@rank+1,@rank:=1) as rank,

@pdept:=t.deptid

from (

select empid,deptid,salary from emp order by deptid asc ,salary desc

) t ,

(select @rownum :=0 , @pdept := null ,@rank:=0) a

) c ;

SELECT aa.totalAmount,

aa.payAmount,

aa.t_amount,

if(@m=aa.s_month,@amt:=@amt+aa.t_amount,@amt:=aa.t_amount) t_amt,

if(@m=aa.s_month,@pay:=@pay+aa.payAmount,@pay:=aa.payAmount) t_pay,

@m:=DATE_ADD(aa.s_month,interval 1 MONTH) s_month

FROM (

SELECT DATE_FORMAT(dateISO,'%Y-%m-01') s_month,

sum(totalAmount) totalAmount,

sum(payAmount) payAmount,

sum(totalAmount-payAmount) t_amount

FROM test a

GROUP BY DATE_FORMAT(dateISO,'%Y-%m-01')

ORDER BY 1 ASC

) aa,(SELECT @m:=null,@pay:=0,@amt:=0) t

;