mysql - 行号

1. 初始化数据 - 列唯一

DROP TABLE IF EXISTS `sales`;

CREATE TABLE `sales` (
  `empid` VARCHAR(10) NOT NULL,
  `mgrid` VARCHAR(10) NOT NULL,
  `qty` INT(11) NOT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Data for the table `sales` */

INSERT  INTO `sales`(`empid`,`mgrid`,`qty`) VALUES ('A','Z',300),('B','X',100),('C','X',200),('D','Y',200),('E','Z',250),('F','Z',300),('G','X',100),('H','Y',150),('I','X',250),('J','Z',100),('K','Y',200);

 

 

2. 查询 - 列唯一

SELECT empid,(
    SELECT COUNT(*) FROM sales T1 WHERE T1.empid <= T2.empid
) AS rownum
 FROM sales T2;

 

运行结果：

 

3. 按照qty和empid顺序生成行号 - 列唯一

 SELECT empid,qty,(
    SELECT COUNT(*) FROM sales T1 WHERE T1.qty < T2.qty OR (T1.qty=T2.qty AND T1.empid <= T2.empid)
 ) AS rownum
 FROM sales T2
 ORDER BY qty,empid;

运行结果：

 

4. 初始化数据 - 列重复

CREATE TABLE `t` (
    `a` CHAR (3)
); 
INSERT INTO `t` (`a`) VALUES('X');
INSERT INTO `t` (`a`) VALUES('X');
INSERT INTO `t` (`a`) VALUES('X');
INSERT INTO `t` (`a`) VALUES('Y');
INSERT INTO `t` (`a`) VALUES('Y');
INSERT INTO `t` (`a`) VALUES('Z');

 

5. 查询 - 列重复

SELECT n.a,n.a+smaller AS rownum, C.a FROM(
    SELECT a, COUNT(*) AS countt, (
     SELECT COUNT(*) FROM t AS B WHERE B.a < A.a
    ) AS smaller
    FROM t AS A
    GROUP BY a
) AS C, nums AS n
WHERE n.a <= countt

运行结果：