将多个表合并到一个新表中的一行中

Question

UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_high = (
SELECT D_high
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_low = (
SELECT D_low
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_low_volume = (
SELECT D_low_volume
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_high_volume = (
SELECT D_high_volume
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_Margin = (
SELECT D_Margin
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET D_Volume = (
SELECT D_Volume
FROM 1d_high_today
WHERE 1d_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_high = (
SELECT H_high
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_low = (
SELECT H_low
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_low_volume = (
SELECT H_low_volume
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_high_volume = (
SELECT H_high_volume
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_Margin = (
SELECT H_Margin
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET H_Volume = (
SELECT H_Volume
FROM 1h_high_today
WHERE 1h_high_today.osrs_id = the_main_table.osrs_id
);



UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET l_high = (
SELECT l_high
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET l_low = (
SELECT l_low
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET L_low_Time = (
SELECT L_low_Time
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET L_high_Time = (
SELECT L_high_Time
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET L_Margin = (
SELECT L_Margin
FROM latest_high_today
WHERE latest_high_today.osrs_id = the_main_table.osrs_id
);


UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_high = (
SELECT T_high
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_low = (
SELECT T_low
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_low_volume = (
SELECT T_low_volume
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_high_volume = (
SELECT T_high_volume
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_Margin = (
SELECT T_Margin
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET T_Volume = (
SELECT T_Volume
FROM 30m_high_today
WHERE 30m_high_today.osrs_id = the_main_table.osrs_id
);

UPDATE the_main_table
SET item_Name = (
SELECT item_Name
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_high = (
SELECT F_high
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_low = (
SELECT F_low
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_low_volume = (
SELECT F_low_volume
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_high_volume = (
SELECT F_high_volume
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_Margin = (
SELECT F_Margin
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);
UPDATE the_main_table
SET F_Volume = (
SELECT F_Volume
FROM 5m_high_today
WHERE 5m_high_today.osrs_id = the_main_table.osrs_id
);

我有一个庞大的SQL查询。

这确实预成型了我试图实现的"；将多个表列合并到1行内的新表中；

然而，它的速度非常慢，只有几千行数据。

有没有一种方法可以缩短这个查询并仍然完成同样的事情？

我尝试过JOIN&工会，但未能使他们正常运作。

制作这个非常长的查询是唯一有效的解决方案。

Answer 1

我不会仔细研究您的所有代码。但从前面的几个例子中可以清楚地看出，您可以使用join。例如：

UPDATE the_main_table m JOIN
1d_high_today ht
ON ht.osrs_id = m.osrs_id
SET m.item_Name = ht.item_Name
m.D_high = ht.D_high,
m.D_low =  ht.D_low,
. . .;

为此，您需要1d_high_today(osrs_id)上的索引。