SQL 在查询子句中使用总和结果

Question

select acc.user_id
     , member.nickname
     , sum(credit) as ttl 
     , member.balance
  from account_history as acc
  left 
  join member
    ON acc.user_id = member.user_id
 where balance > 0 
   and balance <50
 group 
    by user_id 

我想显示哪一行ttl ( sum(credit) ) > 0，

所以我改成

select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50 and ttl > 0
group by user_id 

但它变成了错误. 未找到列 TTL

Answer 1

使用having子句中的值应该有效：

select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc 
    left join member ON acc.user_id=member.user_id 
where balance > 0 and balance <50 
group by user_id 
having  ttl > 0

在where子句中不能使用别名

另请参阅 https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

Answer 2

您应该使用 HAVING 语句按聚合字段进行筛选

SELECT 
  acc.user_id,
  member.nickname, 
  sum(credit) as ttl,
  member.balance
FROM account_history AS acc left join member ON acc.user_id = member.user_id 
WHERE balance > 0 and balance <50
GROUP BY user_id 
HAVING ttl > 0 

Answer 3

我认为它会起作用，

Select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50 and sum(credit) > 0
group by user_id 

或者您也可以使用 having 子句获得相同的结果。

喜欢

Select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50
group by user_id 
having sum(credit) > 0

Answer 4

使用 having 和 acc.user_id,member.nickname ，member.balance 在组中由

SELECT 
  acc.user_id,
  member.nickname, 
  sum(credit) as ttl,
  member.balance
FROM account_history AS acc left join member ON acc.user_id = member.user_id 
WHERE balance > 0 and balance <50
GROUP BY acc.user_id, member.nickname,member.balance
HAVING ttl > 0 

Answer 5

你需要使用子查询来获取 ttl 列，因为它是派生列

select * from
(select acc.user_id,member.nickname, sum(credit) as ttl ,member.balance
from account_history as acc left join member 
ON acc.user_id=member.user_id 
where balance > 0 and balance <50
group by user_id 
)bal
Where ttl>0