还有一个与此类似的问题,但它似乎没有回答我的问题.
There's another question out there similar to this, but it didn't seem to answer my question.
我的问题是:为什么我会返回这个错误 ERROR 1222 (21000): The used SELECT statements have a different number of columns
来自以下 SQL
My question is this: why am I getting back this error ERROR 1222 (21000): The used SELECT statements have a different number of columns
from the following 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;
这是用户
:
+------------+---------------+------+-----+---------+----------------+
| 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 | |
+------------+---------------+------+-----+---------+----------------+
这里是朋友
:
+---------------+--------------------------------------+------+-----+---------+----------------+
| 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.
UNION(UNION
和 UNION ALL
)要求所有被 UNION 连接的查询:
UNIONs (UNION
and UNION ALL
) require that all the queries being UNION'd have:
您的查询有:
SELECT f.*, u1.*, u2.* ...
UNION
SELECT fid2 FROM friends
我最简单的重写是:
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;
您已 LEFT JOIN 到 USERS
表两次,但似乎没有使用这些信息.
You've LEFT JOIN'd to the USERS
table twice, but don't appear to be using the information.
这篇关于使用的 SELECT 语句具有不同的列数(REDUX !!)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!