php - 在不同的mysql表中搜索不同的列

Question

我一个一个地执行这两个查询，但是我需要将这两个查询作为一个查询同时执行，并从mysql数据库中获取一次结果

我尝试了 UNION SELECT 但由于从不同的表中选择不同的列（模型，模型编号）它不起作用

有人知道sql查询结构来帮助我解决这个问题吗？

查询 1

$query = "SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM realestate 
    USE INDEX(idx_post_id,idx_name,idx_price,idx_username,idx_poster_folder_id)
    WHERE post_name LIKE ? OR description LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10";

查询 2

$query = "SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM cars 
USE INDEX(idx_post_id,idx_name,idx_price,idx_username,idx_poster_folder_id)
WHERE post_name LIKE ? OR description LIKE ? OR model LIKE ? OR modelsnumber LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10";

我试过了

$query = "SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM realestate 
WHERE post_name LIKE ? OR description LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10
UNION
SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM cars 
WHERE post_name LIKE ? OR description LIKE ? OR model LIKE ? OR modelsnumber LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10";

标签： phpmysqlsql

Answer 1

UNION 中带有 ORDER BY 和/或 LIMIT 的单独子查询必须用括号括起来：

$query = "
   (SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id 
    FROM realestate 
    WHERE post_name LIKE ? OR description LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10
   )
UNION
   (SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id 
    FROM cars 
    WHERE post_name LIKE ? OR description LIKE ? OR model LIKE ? OR modelsnumber LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10
   )";