MySQL #1222 - 使用的 SELECT 语句具有不同的列数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3946709/
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
#1222 - The used SELECT statements have a different number of columns
提问by Keverw
Why am i getting a #1222 - The used SELECT statements have a different number of columns ? i am trying to load wall posts from this users friends and his self.
为什么我得到 #1222 - 使用的 SELECT 语句具有不同的列数？我正在尝试加载来自该用户朋友和他自己的墙贴。
SELECT u.id AS pid, b2.id AS id, b2.message AS message, b2.date AS date FROM
(
(
SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM
wall_posts AS b
JOIN Friends AS f ON f.id = b.pid
WHERE f.buddy_id = '1' AND f.status = 'b'
ORDER BY date DESC
LIMIT 0, 10
)
UNION
(
SELECT * FROM
wall_posts
WHERE pid = '1'
ORDER BY date DESC
LIMIT 0, 10
)
ORDER BY date DESC
LIMIT 0, 10
) AS b2
JOIN Users AS u
ON b2.pid = u.id
WHERE u.banned='0' AND u.email_activated='1'
ORDER BY date DESC
LIMIT 0, 10
The wall_posts table structure looks like iddateprivacypiduidmessage
wall_posts 表结构看起来像 iddateprivacypiduidmessage
The Friends table structure looks like Fididbuddy_idinvite_up_datestatus
Friends 表结构看起来像 Fididbuddy_idinvite_up_datestatus
pid stands for profile id. I am not really sure whats going on.
pid 代表配置文件 ID。我不太确定发生了什么。
回答by OMG Ponies
The first statement in the UNION returns four columns:
UNION 中的第一条语句返回四列：
SELECT b.id AS id,
b.pid AS pid,
b.message AS message,
b.date AS date
FROM wall_posts AS b
The second one returns six, because the * expands to include all the columns from WALL_POSTS:
第二个返回6，因为 * 扩展为包括来自 的所有列WALL_POSTS：
SELECT b.id,
b.date,
b.privacy,
b.pid.
b.uid message
FROM wall_posts AS b
The UNIONand UNION ALLoperators require that:
在UNION与UNION ALL运营商要求：
- The same number of columns exist in all the statements that make up the UNION'd query
- The data types have to match at each position/column
- 构成 UNION 查询的所有语句中存在相同数量的列
- 数据类型必须在每个位置/列匹配
Use:
用：
FROM ((SELECT b.id AS id,
b.pid AS pid,
b.message AS message,
b.date AS date
FROM wall_posts AS b
JOIN Friends AS f ON f.id = b.pid
WHERE f.buddy_id = '1' AND f.status = 'b'
ORDER BY date DESC
LIMIT 0, 10)
UNION
(SELECT id,
pid,
message,
date
FROM wall_posts
WHERE pid = '1'
ORDER BY date DESC
LIMIT 0, 10))
回答by dan04
You're taking the UNIONof a 4-column relation (id, pid, message, and date) with a 6-column relation (*= the 6 columns of wall_posts). SQL doesn't let you do that.
您将UNION4 列关系（id、pid、message、 和date）与 6 列关系（*= 的 6 列wall_posts）相结合。SQL 不允许您这样做。
回答by Sabeen Malik
(
SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM
wall_posts AS b
JOIN Friends AS f ON f.id = b.pid
WHERE f.buddy_id = '1' AND f.status = 'b'
ORDER BY date DESC
LIMIT 0, 10
)
UNION
(
SELECT id, pid , message , date
FROM
wall_posts
WHERE pid = '1'
ORDER BY date DESC
LIMIT 0, 10
)
You were selecting 4 in the first query and 6 in the second, so match them up.
您在第一个查询中选择了 4 个，在第二个中选择了 6 个，因此将它们匹配起来。
回答by Moshiur Rahman
You are using MySQL Union.
您正在使用 MySQL 联盟。
UNION is used to combine the result from multiple SELECT statements into a single result set.
The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)
Reference: MySQL Union
参考：MySQL 联盟
Your first select statement has 4 columns and second statement has 6 as you said wall_post has 6 column. You should have same number of column and also in same order in both statement. otherwise it shows error or wrong data.
您的第一个 select 语句有 4 列，第二个语句有 6 列，正如您所说的 wall_post 有 6 列。您应该在两个语句中具有相同的列数和相同的顺序。否则显示错误或错误数据。
