MySQL 使用别名

Question

我有以下带有别名 in_Degree 和 out_degree 的语法错误查询:

insert into userData
select user_name, 
      (select COUNT(*) from tweets where rt_user_name = u.USER_NAME)in_degree,
      (select COUNT(*) from tweets where source_user_name = u.user_name)out_degree, 
      in_degree + out_degree(freq)
   from users u

查询中的问题是选择列表中别名为 freq 的第 4 个项目。我希望第 4 项的值为 in_degree + out_degree。蛮力极其缓慢的解决方案是复制并通过两个子查询并添加它们。

1. 我怎样才能像 in_degree + out_degree 一样快速和简单？

Answer 1

您可以使用子查询:

 insert into userData
 select user_name,
   in_degree,
   out_degree,
   in_degree + out_degree
 from
 (
   select user_name, 
    (select COUNT(*) from tweets where rt_user_name = u.USER_NAME)in_degree,
    (select COUNT(*) from tweets where source_user_name = u.user_name)out_degree
   from users u
 ) src

或者你可以使用:

insert into userData
select user_name,
  count(distinct in_t.*) in_degree,
  count(distinct out_t.*) out_degree,
  count(distinct in_t.*) + count(distinct out_t.*)
from users u
left join tweets in_t
  on u.USER_NAME = in_t.rt_user_name
left join tweets out_t
  on u.USER_NAME = out_t.source_user_name
 group by u.user_name