下面是一段输出
array(7) {["type"]=> string(16)"new post comment" ["book_id"]=> string(1)"1" ["name"]=> string(9)"whatever" ["author_id"]=> string(4)"test" ["content"]=> string(19)"2011-07-16 03:20:01"["create_time"]=>字符串(1)"3" ["id"]=> string(1)" 1 "}
这是我查询的一部分
SELECT 'bookcomment' AS type
,b.book_id
,b.name
,c.book_id
,c.author_id
,c.content
,c.create_time AS create_time
,u.id
,u.name
FROM tbl_book AS b,
tbl_book_comment AS c,
tbl_user AS u
WHERE u.id=c.author_id in (1,2) AND b.book_id=c.book_id
UNION ALL
SELECT 'new post comment' AS type
,po.post_id
,po.title
,pc.author_id
,pc.content
,pc.create_time AS create_time
,pc.post_id
,u.id
,u.name
FROM tbl_post as po,
tbl_post_comment as pc,
tbl_user as u
WHERE u.id=pc.author_id in(1,2) AND po.post_id=pc.post_id
UNION ALL
SELECT 'bookrating' AS type
,b.book_id as one
,b.name
,ra.book_id
,ra.user_id
,ra.rating
,ra.create_time AS create_time
,u.id
,u.name
FROM tbl_book AS b,
tbl_user_book_rating AS ra,
tbl_user AS u
WHERE u.id=ra.user_id in (1,2) AND b.book_id=ra.book_id
UNION ALL...
ORDER BY create_time DESC
如结果所示,'author_id'对应的数据应该与'content'对应,'content'应该是'create_time'。我的问题有什么问题?
看起来您在第二个SELECT中混淆了列的顺序。列是这样的(第一个查询在左边,第二个在中间,第三个在右边):
type type type
b.book_id po.post_id one
b.name po.title b.name
c.book_id pc.author_id ra.book_id
c.author_id pc.content ra.user_id
c.content create_time ra.rating
create_time pc.post_id create_time
u.id u.id u.id
u.name u.name u.name
从外观上看,pc.author_id
的顺序混淆了。