mysql - 我可以对父查询中的嵌入式查询的结果进行数学运算吗？

Question

考虑查询:

SELECT n.nid, 
    (select count(*) as count from view_log where id = n.nid) AS the_count,
    (the_count + 1) as the_bumped_count
   FROM node n

当我运行它时，我得到 Unknown column 'the_count' in 'field list'。有没有办法解决？看起来 the_count 应该在查询中可见并且可供使用，但显然不是。顺便说一句，我也试过 SUM(the_count, 1)，但也失败了。谢谢!

Answer 1

我想这也行，而且只需要你数一次:

SELECT nid, the_count, the_count+1 as the_bumped_count
FROM (
    SELECT n.nid,
        COUNT(v.*) the_count, 
    FROM node n
       LEFT JOIN view_log v on n.nid = v.id
    GROUP BY n.nid
) t

或者回到你的语法:

SELECT nid, the_count, the_count+1 as the_bumped_count
FROM (
    SELECT n.nid,
        (select count(*) as count from view_log where id = n.nid) AS the_count
    FROM node n
) t

祝你好运。