Mysql 刷题笔记 1.18 将每个Type按Num进行均等分，结果如下：

-- 1.18 希望将每个Type按Num进行均等分，结果如下：

CREATE TABLE T0118(
	Type VARCHAR(10),
	Num  INT 
);
INSERT INTO T0118 VALUES('A',2);
INSERT INTO T0118 VALUES('B',3);
INSERT INTO T0118 VALUES('C',2);

T0118
+------+------+
| Type | Num  |
+------+------+
| A    |    2 |
| B    |    3 |
| C    |    2 |
+------+------+

结果
+------+-----+
| Type | NUM |
+------+-----+
| A    |   1 |
| A    |   1 |
| B    |   1 |
| B    |   1 |
| B    |   1 |
| C    |   1 |
| C    |   1 |
+------+-----+

1. 构建自己的sqt_values表

CREATE TABLE my_sqt_values(Num INT);

DELIMITER //
CREATE PROCEDURE create_sqt_values(Num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i < Num DO
		INSERT INTO my_sqt_values VALUES(i);
    SET i = i +1;
  END WHILE;
END
//
DELIMITER ;

CALL create_sqt_values(2000);

2. 和T0118构建连接

SELECT t.Type,1 AS NUM FROM T0118 t
LEFT JOIN my_sqt_values msv 
ON t.Num > msv.Num
ORDER BY t.Type