MySQL效率/组合

Question

CREATE TABLE VanNaar_4P

SELECT

a2.ID as Van,a2.lat as v_lat ,a2.lon as v_lon,

a1.ID as Naar,a1.lat as n_lat ,a1.lon as n_lon

FROM

adressen a1 

JOIN adressen a2; 

这个查询可以写得更有效率吗？我有种感觉它太想要它的大小了.基本上我有一个这样的列表：

1 a b
2 d e
3 i w

我需要所有的组合(每行一些字段，基于唯一的ID)

Answer 1

首先，你的问题是对称的，所以你可以这样做

CREATE TABLE VanNaar_temp
SELECT
  a2.ID as Van,a2.lat as v_lat ,a2.lon as v_lon,
  a1.ID as Naar,a1.lat as n_lat ,a1.lon as n_lon
FROM adressen a1 
INNER JOIN adressen a2 ON a2.id<a1.id;

CREATE TABLE VanNaar_4P
SELECT * FROM VanNaar_temp;

INSERT INTO VanNaar_4P
SELECT Narr, n_lat, n_lon, Van, v_lat, v_long
FROM VanNaar_temp;

DROP TABLE VanNaar_temp;

这就省略了零方法，即Van==Nar。如果您确实需要它们，请添加

INSERT INTO VanNaar_4P
SELECT
  a2.ID as Van,a2.lat as v_lat ,a2.lon as v_lon,
  a1.ID as Naar,a1.lat as n_lat ,a1.lon as n_lon
FROM adressen a1 
INNER JOIN adressen a2 ON a2.id=a1.id;

Answer 2

一些连接条件怎么样？

Answer 3

查询每个可能的组合：

SELECT
*
FROM tbl t1, tbl t2

您的查询包含所有可能的组合：

SELECT
a2.ID as Van,a2.lat as v_lat ,a2.lon as v_lon,
a1.ID as Naar,a1.lat as n_lat ,a1.lon as n_lon
FROM adressen a1, adressen a2

下面是一些示例数据：

INSERT INTO tbl
(type, details)
VALUES
('a', 'b'),
('g', 'z'),
('k', 'l'),
('c', 'a');

结果：

| TYPE | DETAILS |
------------------
|    a |       b |
|    g |       z |
|    k |       l |
|    c |       a |
|    a |       b |
|    g |       z |
|    k |       l |
|    c |       a |
|    a |       b |
|    g |       z |
|    k |       l |
|    c |       a |
|    a |       b |
|    g |       z |
|    k |       l |
|    c |       a |