问题描述
还有另一个与此类似的问题,但是它似乎没有回答我的问题.
There's another question out there similar to this, but it didn't seem to answer my question.
我的问题是:为什么我从以下SQL找回此错误ERROR 1222 (21000): The used SELECT statements have a different number of columns
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;
这里是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 | |
+------------+---------------+------+-----+---------+----------------+
这里是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.
推荐答案
UNION(UNION
和UNION ALL
)要求所有被UNION的查询都具有:
UNIONs (UNION
and UNION ALL
) require that all the queries being UNION'd have:
- SELECT子句中的列数相同
- 列数据类型必须在每个位置都匹配
您的查询具有:
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;
您已经两次左联接USERS
表,但似乎没有使用该信息.
You've LEFT JOIN'd to the USERS
table twice, but don't appear to be using the information.
这篇关于所使用的SELECT语句具有不同数量的列(REDUX !!)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!