mysql怎么实现统计功能

select * from (

select id,num,rank from (

select heyf_tmp.id,heyf_tmp.num,@rownum:=@rownum+1 ,

if(@pdept=heyf_tmp.id,@rank:=@rank+1,@rank:=1) as rank,

@pdept:=heyf_tmp.id

from (

select id,num from (SELECT

t.OPERATE_LOG_USER_ID AS id,

t.OPERATE_SEARCH_WORD AS word,

COUNT(*)AS num

FROM

skp_operate_log AS t

WHERE

t.OPERATE_LOG_TIME

GROUP BY

id,

word

ORDER BY

num DESC)tt order by id asc ,num desc

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

)ttt

where rank in(1,2)

案例一个　

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

insert into heyf_t10 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)

显示结果预期如下:

+-------+--------+----------+------+

| empid | deptid | salary | rank |

+-------+--------+----------+------+

| 1 | 10 | 5500.00 | 1 |

| 2 | 10 | 4500.00 | 2 |

| 4 | 20 | 4800.00 | 1 |

| 3 | 20 | 1900.00 | 2 |

| 7 | 40 | 44500.00 | 1 |

| 6 | 40 | 14500.00 | 2 |

| 5 | 40 | 6500.00 | 3 |

| 9 | 50 | 7500.00 | 1 |

| 8 | 50 | 6500.00 | 2 |

+-------+--------+----------+------+

select empid,deptid,salary,rank from (

select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 ,

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

@pdept:=heyf_tmp.deptid

from (

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

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

mysql>select empid,deptid,salary,rank from (

->select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 ,

->if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,

->@pdept:=heyf_tmp.deptid

->from (

->select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc

->) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result

->

+-------+--------+----------+------+

| empid | deptid | salary | rank |

+-------+--------+----------+------+

| 1 | 10 | 5500.00 | 1 |

| 2 | 10 | 4500.00 | 2 |

| 4 | 20 | 4800.00 | 1 |

| 3 | 20 | 1900.00 | 2 |

| 7 | 40 | 44500.00 | 1 |

| 6 | 40 | 14500.00 | 2 |

| 5 | 40 | 6500.00 | 3 |

| 9 | 50 | 7500.00 | 1 |

| 8 | 50 | 6500.00 | 2 |

+-------+--------+----------+------+

9 rows in set (0.00 sec)

使用mysqldump工具进行备份：

1）备份所有数据库：

$ mysqldump -uroot -p --all-database >all.sql

（2）备份数据库test

$ mysqldump -uroot -p test >test.sql

（3）备份数据库test下的表emp

$ mysqldump -uroot -p test emp >emp.sql

（4）备份数据库test下的表emp和dept

$ mysqldump -uroot -p test emp dept >emp_dept.sql

---