Mysql 動態(tài)取第N個元素

簡介

基于mysql實現(xiàn)對某個表的某個字段按照順（逆）序取第N個排序?qū)?yīng)的元素，該語句通過存儲過程結(jié)合預(yù)處理實現(xiàn)，也可在程序語言里通過SQL拼接實現(xiàn)。

示例

--1存儲過程實現(xiàn)第N個元素的查詢 DROP PROCEDURE usp_getNth; CREATE PROCEDURE usp_getNth(IN tab VARCHAR(100),IN col VARCHAR(100),IN sort VARCHAR(4),IN seq SMALLINT,OUT num int) BEGINDECLARE dsql VARCHAR(1000);SET @dsql=CONCAT('SELECT ' , col,' FROM ',tab,' ORDER BY ',col,' ',sort,' LIMIT 1 OFFSET ',seq);PREPARE stmt FROM @dsql;EXECUTE stmt;DEALLOCATE PREPARE stmt; END-- 2調(diào)用示例 SET @num=10; call usp_getNth('emp','sal','DESC',3,@num); SELECT @num;

延展閱讀

通過對比我們發(fā)現(xiàn)order by limit 1 OFFSET N這里排序時是按照DENSE_RANK的方式進(jìn)行的，即有重名的視為并列，下個名次延續(xù)并列的（排名不出現(xiàn)斷層）。

表結(jié)構(gòu)及數(shù)據(jù)見：

-- Mysql 版本 DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; DROP TABLE IF EXISTS bonus; DROP TABLE IF EXISTS salgrade; create table dept(deptno int unsigned auto_increment COMMENT '部門編號',dname varchar(15) COMMENT '部門名稱',loc varchar(50) COMMENT '部門所在位置', primary key(deptno) ) COMMENT='部門表';INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); INSERT INTO dept VALUES (30,'SALES','CHICAGO'); INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');create table emp(empno int unsigned auto_increment COMMENT '雇員編號',ename varchar(15) COMMENT '雇員姓名',job varchar(10) COMMENT '雇員職位',mgr int unsigned COMMENT '雇員對應(yīng)的領(lǐng)導(dǎo)的編號',hiredate date COMMENT '雇員的雇傭日期',sal decimal(7,2) COMMENT '雇員的基本工資',comm decimal(7,2) COMMENT '獎金',deptno int unsigned COMMENT '所在部門',primary key(empno),foreign key(deptno) references dept(deptno) ) COMMENT='雇員表';INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);create table salgrade(grade int unsigned COMMENT '工資等級',losal int unsigned COMMENT '此等級的最低工資',hisal int unsigned COMMENT '此等級的最高工資' ) COMMENT='工資等級表';INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999);create table bonus(ename varchar(10) COMMENT '雇員姓名',job varchar(9) COMMENT '雇員職位',sal decimal(7,2) COMMENT '雇員工資',comm decimal(7,2) COMMENT '雇員資金' ) COMMENT='獎金表';

總結(jié)

以上是生活随笔為你收集整理的取第N个排序的元素之Mysql存储过程动态SQL order by offset实现案例的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。