错误:使用的SELECT语句有不同的列数
错误:使用的SELECT语句有不同的列数
提问于 2010-09-07 04:10:24
为什么我要从下面得到ERROR 1222 (21000): The used SELECT statements have a different number of columns？
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 | |
+---------------+--------------------------------------+------+-----+---------+----------------+回答 1
Stack Overflow用户
回答已采纳
发布于 2010-09-07 04:13:17
联合(UNION和UNION ALL)要求所有属于UNION的查询都有：
- 在SELECT子句
- 中的相同列数列数据类型必须在每个位置
匹配
您的查询有：
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;您已经在USERS表上留下了两次JOIN，但似乎没有使用这些信息。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3655708
复制相关文章
相似问题
腾讯云开发者
