August 12 2016

mysql rank

create table songbinsb(p varchar(20),name varchar(20),sal int,sex varchar(20));

insert into songbinsb values(‘dba’,’sb’,5000,’男’);
insert into songbinsb values(‘dba’,’cx’,4000,’男’);
insert into songbinsb values(‘dba’,’fs’,3000,’男’);
insert into songbinsb values(‘dba’,’sg’,2000,’男’);
insert into songbinsb values(‘devops’,’xx’,6600,’男’);
insert into songbinsb values(‘devops’,’wh’,600,’男’);
insert into songbinsb values(‘devops’,’wy’,5600,’男’);
select * from (
SELECT a.sal,a.name,
IF( @pa = a.p,@rank := @rank + 1,@rank :=1 ) AS rank,
(@pa := a.p) p
FROM songbinsb a, ( select @rank :=0,@pa := NULL ) b
ORDER BY a.p, a.sal DESC ) c where rank=1;

select p,count(*) c from songbinsb group by p
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
‘MAX(IF(c.p = ”’,
c.p,
”’, s.c, 0)) AS ”’,
c.p, ””
)
)
FROM wc c;

select MAX(IF(c.p = ‘dba’, s.c, 0)) AS ‘dba’,MAX(IF(c.p = ‘devops’, s.c, 0)) AS ‘devops’ from wc
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
‘MAX(IF(c.p = ”’,
c.p,
”’, c.c, 0)) AS ”’,
c.p, ””
)
) INTO @sql
FROM (select p,count(*) c from songbinsb group by p) c;

SET @sql = CONCAT(‘Select ‘, @sql,
‘ From (select p,count(*) c from songbinsb group by p) c
Left Join (select p from wc) s On c.p = s.p’);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



Copyright 2019. All rights reserved.

Posted 2016年8月12日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *