我有100個多點的WordPress*_options即表WPM_101_options來WPM_201_options。
我想查詢選項名稱admin_email，siteurl和blogname。
此代碼適用于 1 個表：
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'admin_email'
UNION
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'blogname'
但是我如何讓它與多個表一起作業？我嘗試了 2 張桌子...
SELECT option_name, option_value
FROM wpm_104_options,wpm_105_options
WHERE option_name = 'admin_email'
UNION
SELECT option_name, option_value
FROM wpm_104_options,wpm_105_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value
FROM wpm_104_options,wpm_105_options
WHERE option_name = 'blogname'
和
SELECT option_name, option_value
FROM wpm_104_options,wpm_101_options
WHERE option_name = 'admin_email'
GROUP BY option_name
UNION
SELECT option_name, option_value
FROM wpm_104_options,wpm_101_options
WHERE option_name = 'siteurl'
GROUP BY option_name
UNION
SELECT option_name, option_value
FROM wpm_104_options,wpm_101_options
WHERE option_name = 'blogname'
GROUP BY option_name
和
SELECT wpm_104_options.option_name as n104, wpm_104_options.option_value as v104, wpm_101_options.option_name as n101, wpm_101_options.option_value as v101
FROM wpm_104_options, wpm_101_options
WHERE option_name = 'admin_email'
...但我收到一個錯誤：
錯誤代碼：1052。欄位串列中的“option_name”列不明確 0.000 秒
uj5u.com熱心網友回復：
該錯誤表示您試圖查詢兩個表，每個表都有一option_name列，而資料庫無法知道您指的是這兩個表中的哪一個。
您通過在FROM子句中指定兩個表來做的是隱式連接。如果您希望將兩個表中的列連接到您的結果中，您需要通過在它們前面加上表名來指定您的意思是哪些列，例如
SELECT wpm_104_options.option_name, wpm_104_options.option_value, wpm_101_options.option_name, wpm_101_options.option_value
FROM wpm_104_options,wpm_101_options
WHERE wpm_104_options.option_name = 'admin_email'
GROUP BY wpm_104_options.option_name
然而，這通常只有在您還指定列應該以何種方式連接時才有意義，即在WHERE子句中定義它們的關系。否則，您只會從兩個表中獲得所有可能的行組合。
我想你真正想要的是別的東西。我相信你只是想要你為一張表顯示的查詢的組合結果，但來自兩個表。這FROM不是通過在子句中與兩個表進行隱式連接來實作的，而是通過使用UNION或UNION ALL像您已經做過的那樣來實作的。例如
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'admin_email'
UNION
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'blogname'
UNION
SELECT option_name, option_value
FROM wpm_101_options
WHERE option_name = 'admin_email'
UNION
SELECT option_name, option_value
FROM wpm_101_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value
FROM wpm_101_options
WHERE option_name = 'blogname'
您還可以通過在WHERE子句中指定不同的條件來大大縮短此時間OR：
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name = 'admin_email'
OR option_name = 'siteurl'
OR option_name = 'blogname'
UNION
SELECT option_name, option_value
FROM wpm_101_options
WHERE option_name = 'admin_email'
OR option_name = 'siteurl'
OR option_name = 'blogname'
甚至
SELECT option_name, option_value
FROM wpm_104_options
WHERE option_name IN ('admin_email', 'siteurl', 'blogname')
UNION
SELECT option_name, option_value
FROM wpm_101_options
WHERE option_name IN ('admin_email', 'siteurl', 'blogname')
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/315216.html