所使用的SELECT语句具有不同数量的列(REDUX !!) [英] The used SELECT statements have a different number of columns (REDUX!!)

问题描述

还有另一个与此类似的问题，但是它似乎没有回答我的问题.

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:

1. SELECT子句中的列数相同
2. 列数据类型必须在每个位置都匹配

您的查询具有:

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 !!)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！