MySQL 使用的 SELECT 语句具有不同的列数（REDUX ！！）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3655708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The used SELECT statements have a different number of columns (REDUX!!)
提问by Josh Smith
There's another question out there similar to this, but it didn't seem to answer myquestion.
还有另一个与此类似的问题，但它似乎没有回答我的问题。
My question is this: why am I getting back this error ERROR 1222 (21000): The used SELECT statements have a different number of columnsfrom the following SQL
我的问题是：为什么我会ERROR 1222 (21000): The used SELECT statements have a different number of columns从以下 SQL 中返回此错误
SELECT * FROM friends
LEFT JOIN users AS u1 ON users.uid = friends.fid1
LEFT JOIN users AS u2 ON users.uid = friends.fid2
WHERE (friends.fid1 = 1) AND (friends.fid2 > 1)
UNION SELECT fid2 FROM friends
WHERE (friends.fid2 = 1) AND (friends.fid1 < 1)
ORDER BY RAND()
LIMIT 6;
Here's users:
这是users：
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| uid | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(50) | NO | | NULL | |
| last_name | varchar(50) | NO | | NULL | |
| email | varchar(128) | NO | UNI | NULL | |
| mid | varchar(40) | NO | | NULL | |
| active | enum('N','Y') | NO | | NULL | |
| password | varchar(64) | NO | | NULL | |
| sex | enum('M','F') | YES | | NULL | |
| created | datetime | YES | | NULL | |
| last_login | datetime | YES | | NULL | |
| pro | enum('N','Y') | NO | | NULL | |
+------------+---------------+------+-----+---------+----------------+
Here's friends:
这是friends：
+---------------+--------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------------------------------+------+-----+---------+----------------+
| friendship_id | int(11) | NO | MUL | NULL | auto_increment |
| fid1 | int(11) | NO | PRI | NULL | |
| fid2 | int(11) | NO | PRI | NULL | |
| status | enum('pending','accepted','ignored') | NO | | NULL | |
+---------------+--------------------------------------+------+-----+---------+----------------+
If you want to give any feedback on anything crazy you see going on here, as well, please feel free to do so. I'll take my lumps.
如果您也想对这里发生的任何疯狂事情提供任何反馈，请随时这样做。我会拿我的肿块。
回答by OMG Ponies
UNIONs (UNIONand UNION ALL) require that all the queries being UNION'd have:
UNIONs ( UNIONand UNION ALL) 要求所有被 UNION 的查询都具有：
- The same number of columns in the SELECT clause
- The column data type has to match at each position
- SELECT 子句中的列数相同
- 列数据类型必须在每个位置匹配
Your query has:
您的查询有：
SELECT f.*, u1.*, u2.* ...
UNION
SELECT fid2 FROM friends
The easiest re-write I have is:
我最简单的重写是：
SELECT f.*, u.*
FROM FRIENDS AS f
JOIN USERS AS u ON u.uid = f.fid2
WHERE f.fid1 = 1
AND f.fid2 > 1
UNION
SELECT f.*, u.*
FROM FRIENDS AS f
JOIN USERS AS u ON u.uid = f.fid1
WHERE f.fid2 = 1
AND f.fid1 < 1
ORDER BY RAND()
LIMIT 6;
You've LEFT JOIN'd to the USERStable twice, but don't appear to be using the information.
您已经USERS两次LEFT JOIN 加入该表，但似乎没有使用这些信息。
