时间戳

首页 > 解决方案 > 我使用 UNION 的 mysql 查询出错

mysql - 我使用 UNION 的 mysql 查询出错

问题描述

我的 SQL 查询

SELECT DISTINCT a.*,categories_2625729.title AS category_title,categories_2625729.id AS category,c.id as cid, c.title, '' as `introtext`, 'com_hpj_content.ad' as content_type, '' as banner, '' as `fulltext`, '' as sheettext, c.publish_up, c.publish_down, c.price, c.advertiser_id, c.seller_id, c.sold, c.soldupdated,0 AS is_favorite
FROM `jos_hpj_content_item` AS a
LEFT JOIN jos_categories AS categories_2625729 ON categories_2625729.id = a.category
LEFT JOIN jos_hpj_content_item_ad AS cff ON cff.id = a.ref_id AND a.content_type = 'com_hpj_content.ad'
LEFT JOIN jos_fields_values AS fields_values_min_maxyear ON fields_values_min_maxyear.item_id = a.ref_id AND fields_values_min_maxyear.field_id=8
LEFT JOIN jos_fields_values AS fields_values_min_max_2mileage ON fields_values_min_max_2mileage.item_id = a.ref_id AND fields_values_min_max_2mileage.field_id=10
LEFT JOIN jos_hpj_content_item_ad c ON c.`id` = a.`ref_id` AND a.`content_type`='com_hpj_content.ad'
WHERE a.state = 1 AND (`c`.title LIKE '%22%' OR c.`introtext` LIKE '%22%' OR a.synonym LIKE '%22%') AND a.content_type IN ('com_hpj_content.ad') AND a.ref_id IN (53,307,353,354,572,644,717,934,978,1056,1086,1128,1149,1199,1276,1294,1314,1324,1347,1351,1396,1411,1462,1470,1513,1537,1740,1741,1836,1861,1916,1988,1989,2005,2006,2124,2158,2266,2267,2268,2277,2511,2575,2577,2582,2585,2587,2654,2815,2894,2906,2975,3021,3179,3194,3219,3224,3239,0) AND a.state = 1
UNION (SELECT name FROM jos_users WHERE name LIKE '%22%')

我收到错误

#1222 - The used SELECT statements have a different number of columns

如果我删除 UNION sql查询返回结果但如果我保留 UNION 错误返回我试图在 c.title 或 jos_user 的名称中搜索不同表中的数字并返回结果

标签： mysql

解决方案

正如@danblack 在评论中指出的那样，错误

#1222 - The used SELECT statements have a different number of columns

是说两条SELECT语句的列数不同。在

SELECT DISTINCT a.*,
                categories_2625729.title AS category_title,
                categories_2625729.id AS category,c.id as cid, 
                c.title, 
                '' as `introtext`, 
                'com_hpj_content.ad' as content_type, 
                '' as banner, 
                '' as `fulltext`, 
                '' as sheettext, 
                c.publish_up, 
                c.publish_down, 
                c.price, 
                c.advertiser_id, 
                c.seller_id, 
                c.sold, 
                c.soldupdated,0 AS is_favorite
  FROM ...

..您选择的列比这里多得多

SELECT name
  FROM ...

这两个SQL语句的列数必须匹配，以便UNION能够将第二个查询的结果添加到第一个查询的结果中。

所以这将起作用：

SELECT DISTINCT c.title
  FROM ...
UNION
SELECT name
  FROM ...

因为现在两个单独的SELECT语句都只返回一列。

推荐阅读