根據查詢加入或替換列值

我有這個查詢：

SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description 
FROM items as i
LEFT JOIN hosts as h
ON i.hostid = h.hostid
AND i.type = 11
AND i.status = 0
WHERE h.name LIKE "ABC%" OR h.name LIKE "123%"
ORDER BY templateid DESC
LIMIT 1;

結果是：

h.name | i.name | i.delay | i.delay_flex | i.status | 43206 | i.params | i.description |

我需要43206用這個查詢替換值：

SELECT name 
FROM hosts 
WHERE hostid = (SELECT hostid 
               FROM items 
               WHERE itemid = (SELECT '43206'));

結果是：Template XPTO

預期結果是：

   h.name | i.name | i.delay | i.delay_flex | i.status | Template XPTO | i.params | i.description |

您可以通過這種方式使用標量子查詢：

SELECT    h.name, 
         i.name, 
         i.delay, 
         i.delay_flex, 
         i.status, 
         (SELECT name 
          FROM hosts 
          WHERE hostid = (SELECT hostid 
                          FROM items 
                          WHERE itemid = i.templateid)), 
         i.params, 
         i.description 
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
WHERE     h.name LIKE "ABC%" 
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;

或者您可以將兩個表都添加hosts到itemsJOIN 部分：

SELECT    h.name, 
         i.name, 
         i.delay, 
         i.delay_flex, 
         i.status, 
         h2.name, 
         i.params, 
         i.description 
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
LEFT JOIN items as it
ON        it.itemid = i.templateid
LEFT JOIN hosts as h2
ON        h2.hostid = it.hostid
WHERE     h.name LIKE "ABC%" 
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;

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