#1222-使用的SELECT语句的列数不同 [英] #1222 - The used SELECT statements have a different number of columns

问题描述

我为什么要#1222-使用的SELECT语句的列数不同 ?我正在尝试从此用户的朋友和他自己中加载墙贴.

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.

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

wall_posts表结构看起来像id date privacy pid uid message

The wall_posts table structure looks like id date privacy pid uid message

Friends表结构类似于Fid id buddy_id invite_up_date status

The Friends table structure looks like Fid id buddy_id invite_up_date status

pid代表配置文件ID.我不太确定怎么回事.

pid stands for profile id. I am not really sure whats going on.

推荐答案

UNION中的第一条语句返回四列:

The first statement in the UNION returns four columns:

SELECT b.id AS id, 
       b.pid AS pid, 
       b.message AS message, 
       b.date AS date 
  FROM wall_posts AS b 

第二个返回 6 ，因为*扩展为包括WALL_POSTS中的所有列:

The second one returns six, because the * expands to include all the columns from WALL_POSTS:

SELECT b.id, 
       b.date, 
       b.privacy,
       b.pid. 
       b.uid message
  FROM wall_posts AS b 

UNION和UNION ALL运算符要求:

1. 组成UNION查询的所有语句中存在相同数量的列
2. 数据类型必须在每个位置/列都匹配

使用:

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))

这篇关于#1222-使用的SELECT语句的列数不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！