交叉自我加入以獲得更多行 - 更好的解決方案？

我有一張桌子employees：

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| persons_id     | int(10) unsigned |
| restaurants_id | int(10) unsigned | 
+----------------+------------------+

和一張桌子bills：

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| customers_id   | int(10) unsigned |
| employees_id   | int(10) unsigned |
| restaurants_id | int(10) unsigned |
| date           | date             |
+----------------+------------------+

我想通過使用交叉連接將 10,000 行與employees_id,restaurant_id和一個日期插入表中。bills該表employees填充了 15 行（員工）。

目前我做以下事情：

insert into bills (employees_id, restaurants_id, date) select e.id, e.restaurants_id, '2012-01-01' from employees e cross join employees e2 cross join employees e3 cross join employees e4 order by RAND() limit 10000;

通過這個查詢，我得到 50,625 行（不限於 10,000 行）（15rows15rows15rows*15rows=50,625rows）。有沒有更優雅的解決方案來獲得 10,000 行？所以我不必寫 X 次cross join employees eX。

我正在使用 MySQL。謝謝。

不，他們不是更好的方法，然後在 MySQL 中交叉加入自己。您可以使用其他 RDBMS 中的 CTE 使 SQL 稍微優雅一些，但您所擁有的是 IMO 的最佳方式。

通過“最佳”，我也包括“高效”：基於集合/無循環，無臨時表

CREATE TABLE emp1 SELECT id,restaurants_id FROM employees WHERE 1=2;
INSERT INTO emp1 SELECT id,restaurants_id FROM employees;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
CREATE TABLE emp2 SELECT id,restaurants_id FROM employees WHERE 1=2;
ALTER TABLE emp2 ADD rnd INT NULL;
INSERT INTO emp2 (id,restaurants_id,rnd)
SELECT id,restaurants_id,RAND()*10000 FROM emp1 LIMIT 10000;
ALTER TABLE emp2 ADD INDEX (rnd);
DROP TABLE emp1;
INSERT INTO bills (employees_id, restaurants_id, date)
SELECT id,restaurants_id FROM emp2 ORDER BY rnd;

引用自：https://dba.stackexchange.com/questions/17553