下面的查询在我的php脚本中工作得很好,但是当我在phpMyAdmin中运行它(针对相同的数据库)时,我没有得到任何结果(甚至不是"0结果" -它只是刷新页面)。
SELECT comment, username, image, post_date, ((us.user_id != 22) AND
((SELECT read_date FROM
bookmarks WHERE
product_id=456029 AND
user_id=22) < post_date)) AS
new_comment FROM
comments AS
co, users AS
us, flair WHERE co.product_id=456029 AND
co.user_id=us.user_id AND
flair_id=us.active_flair_id ORDER BY
co.post_date ASC
我注意到,如果我拿出别名,查询工作在phpMyAdmin
SELECT comment, username, image, post_date, ((users.user_id != 22) AND
((SELECT read_date FROM
bookmarks WHERE
product_id=456029 AND
user_id=22) < post_date)) AS
new_comment FROM
comments, users, flair WHERE comments.product_id=456029 AND
comments.user_id=users.user_id AND
flair_id=users.active_flair_id ORDER BY
comments.post_date ASC
我很好奇为什么会发生这种情况。(运行phpMyAdmin - 2.11.9.5)
编辑:Richard下面的重新表述在phpMyAdmin中工作。所以现在的难题是为什么一个公式可以工作,而另一个不能(当它们在php脚本中执行时都工作得很好)。
Richard建议删除子查询后的查询的最终形式:
SELECT c.comment AS
COMMENT , username, image, DATE_FORMAT( c.post_date, '%b %D, %Y' ) AS post_date, IF( (
(
b.read_date IS NULL
)
OR (
b.read_date < c.post_date
) ) , IF( c.user_id !=1, 1, 0 ) , 0
) AS new_comment
FROM comments c
INNER JOIN users u ON c.user_id = u.user_id
INNER JOIN flair f ON flair_id = u.active_flair_id
LEFT JOIN bookmarks b ON ( b.product_id = c.product_id
AND b.user_id =1 )
WHERE c.product_id =538968
ORDER BY c.post_date ASC
你有一个非常奇怪的查询,我也不会接受…
SELECT comment, username, image, post_date
, (
(users.user_id != 22)
AND (
(
SELECT read_date
FROM bookmarks
WHERE product_id=456029
AND user_id=22
)
< post_date)
) AS new_comment
FROM comments, users, flair
WHERE comments.product_id=456029
AND comments.user_id=users.user_id
AND flair_id=users.active_flair_id
ORDER BY comments.post_date ASC
小修改:
SELECT comment, username, image, post_date
, (
(u.user_id != 22) # What is this for? First you check if it is not user 22
AND (
(
SELECT read_date
FROM bookmarks
WHERE product_id=456029
AND user_id=22 # And now you check if it is
)
< post_date)
) AS new_comment
FROM comments c
INNER JOIN users u ON c.user_id = u.user_id
INNER JOIN flair f ON flair_id = u.active_flair_id
WHERE c.product_id = 456029
ORDER BY c.post_date ASC
这个查询的目的是什么?当然这可以写得更好…
这个怎么样:
SELECT c.comment, username, image, c.post_date
, IF(b.read_date < c.post_date, 1, 0) AS new_comment
FROM comments c
INNER JOIN users u ON c.user_id = u.user_id
INNER JOIN flair f ON flair_id = u.active_flair_id
LEFT JOIN bookmarks b ON (b.product_id = c.product_id AND b.user_id = u.user_id)
WHERE c.product_id = 456029
ORDER BY c.post_date ASC
在您的查询中没有理由使用子查询