查询SELECT语句有不同数量的列 - query SELECT statements have a different number of columns

query SELECT statements have a different number of columns

本文关键字：SELECT 语句查询 | 更新日期: 2023-09-27

我用下面的代码得到这个:

使用的SELECT语句具有不同的列数。我仍然从这个网站搜索解决方案，但我找不到什么是问题在我的查询。任何人都可以帮助我这里为什么我得到= "使用的SELECT语句有不同数量的列"和我怎么能修复它?

SELECT DISTINCT 
            M.msg_id, 
            M.uid_fk, 
            M.message,
            S.created, 
            M.share_count, 
            U.username,
            U.last_login,
            M.uploads, 
            S.uid_fk AS 
            share_uid,
            S.ouid_fk AS share_ouid FROM 
            messages M, 
            users U, 
            friends F,
            message_share S 
            WHERE 
            F.friend_one='$uid' AND 
            U.uid = F.friend_one AND
            U.status='1' AND 
            F.friend_two != S.ouid_fk AND 
            M.uid_fk = S.ouid_fk AND F.role='fri' AND 
            S.msg_id_fk = M.msg_id group by msg_id)
            UNION
            (SELECT DISTINCT 
            M.msg_id, 
            M.uid_fk, 
            M.message,
            M.share_count,
            U.username,
            U.last_login,
            M.uploads, '0' AS share_uid, '0' AS share_ouid 
            FROM messages M, users U, friends F WHERE F.friend_one='$uid' AND U.status='1' AND M.uid_fk=U.uid AND M.uid_fk = F.friend_two GROUP by msg_id ) ORDER BY created DESC

那是因为您正在做UNION，在这种情况下，SELECT的两个部分应该包含完全相同数量的列，这对您的场景不满意。您的第一个SELECT部分包含10个选定列，而第二个SELECT语句仅包含9列，因此出现错误。

SELECT DISTINCT 
            M.msg_id, 
            M.uid_fk, 
            M.message,
            S.created, 
            M.share_count, 
            U.username,
            U.last_login,
            M.uploads, 
            S.uid_fk AS 
            share_uid,
            S.ouid_fk AS share_ouid 
            FROM 
            messages M, 
            users U, 
            .........
            UNION
            (SELECT DISTINCT 
            M.msg_id, 
            M.uid_fk, 
            M.message,
            NOW() as created //Add some default value
            M.share_count,
            U.username,
            U.last_login,
            M.uploads, 
            '0' AS share_uid, 
            '0' AS share_ouid

两个SELECT语句必须包含相同的列数(在第二个SELECT语句中没有创建):

SELECT DISTINCT 
            M.msg_id, 
            M.uid_fk, 
            M.message,
            S.created, 
            M.share_count, 
            U.username,
            U.last_login,
            M.uploads, 
            S.uid_fk AS 
            share_uid,
            S.ouid_fk AS share_ouid
 FROM messages M, users U, friends F, message_share S 
 WHERE F.friend_one='$uid'
 AND U.uid = F.friend_one
 AND U.status='1'
 AND F.friend_two != S.ouid_fk
 AND M.uid_fk = S.ouid_fk
 AND F.role='fri'
 AND S.msg_id_fk = M.msg_id
 GROUP BY msg_id
UNION
SELECT DISTINCT 
            M.msg_id, 
            M.uid_fk, 
            M.message,
            '0000-00-00' as created
            M.share_count,
            U.username,
            U.last_login,
            M.uploads,
            '0' AS share_uid,
            '0' AS share_ouid 
 FROM messages M, users U, friends F
 WHERE F.friend_one='$uid'
 AND U.status='1'
 AND M.uid_fk=U.uid
 AND M.uid_fk = F.friend_two
 GROUP BY msg_id
 ORDER BY created DESC