mysql> create table nums(id int); -> insert into nums select 1 union all select 2 union all select 4; -> select * from nums | Query OK, 0 rows affected (0.06 sec)Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0+------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.08 sec)mysql> select * from t a join nums b on a.count>=b.id | +------+-------+------+ | id | count | id | +------+-------+------+ | 2011 | 3 | 1 | | 2011 | 3 | 2 | | 2011 | 3 | 3 | +------+-------+------+ 有一个数字的辐助表可以实现
MYSQL有没有象SQL SERVER中的master..spt_values的东西?
1、辅助表法 SELECT *,CONCAT(A.RQ,'-',B.id) AS NEWRQ FROM TTQ A LEFT JOIN lsb1 B ON A.ID>=B.ID 建立LSB1,字段ID,内容1-10000
2、游标法 DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `yb`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `yb`() BEGIN DECLARE done INT DEFAULT 0; DECLARE z_js INT; DECLARE z_id VARCHAR(10); DECLARE i INT; DECLARE cur1 CURSOR FOR SELECT * FROM ttq; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET i=1; OPEN cur1; FETCH cur1 INTO z_id,z_js; WHILE done=0 DO WHILE i<=z_js DO SELECT CONCAT(z_id,'-',i); SET i=i+1; END WHILE; FETCH cur1 INTO z_id,z_js; END WHILE; END$$DELIMITER ; 假设 TTQ为工作表
mysql> create table nums(id int);
-> insert into nums select 1 union all select 2 union all
select 4;
-> select * from nums |
Query OK, 0 rows affected (0.06 sec)Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.08 sec)mysql> select * from t a join nums b on a.count>=b.id |
+------+-------+------+
| id | count | id |
+------+-------+------+
| 2011 | 3 | 1 |
| 2011 | 3 | 2 |
| 2011 | 3 | 3 |
+------+-------+------+
有一个数字的辐助表可以实现
SELECT *,CONCAT(A.RQ,'-',B.id) AS NEWRQ FROM TTQ A LEFT JOIN lsb1 B ON A.ID>=B.ID
建立LSB1,字段ID,内容1-10000
DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `yb`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `yb`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE z_js INT;
DECLARE z_id VARCHAR(10);
DECLARE i INT;
DECLARE cur1 CURSOR FOR SELECT * FROM ttq;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET i=1;
OPEN cur1;
FETCH cur1 INTO z_id,z_js;
WHILE done=0 DO
WHILE i<=z_js DO
SELECT CONCAT(z_id,'-',i);
SET i=i+1;
END WHILE;
FETCH cur1 INTO z_id,z_js;
END WHILE;
END$$DELIMITER ;
假设 TTQ为工作表