不允许使用组函数( SQL )。
不允许使用组函数( SQL )。
提问于 2016-01-29 05:08:49
我有这样的桌子：
考勤(offering_id，visitor_id，regstr_date，amount_paid)提供(offering_id，teacher_id)教师(teacher_id，teacher_firstname，teacher_lastname，start_date)
一位访客可能会出席两次或两次以上。我想检索的visitor_id，regstr_date，总amount_paid，平均amount_paid从提供id为30,40或50，教师的开始日期少于任何访客的最新regstr_date和平均amount_paid按每名访客少于600。我的代码如下：
select
distinct(a.visitor_id) as v_id ,
max(a.regstr_date) as reg_date,
sum(a.amount_paid) as total_pay,
count(a.regstr_date) as attendance_count,
avg(a.amount_paid) as average_paid
from
attendance a, teacher t, offer o
where
a.offering_id = o.offering_id
and o.teacher_id = t.teacher_id
and a.offering_id in ('30', '40', '50')
and max(a.regstr_date) > t.start_date
group by
a.visitor_id
having
avg (a.amount_paid) <= 600;但这表明这里不允许使用组函数。如果可能的话你能帮我处理一下这个吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-01-29 06:13:25
此查询是为MS-SQL server编写的。
select a.visitor_id as v_id ,
max(a.regstr_date) as reg_date,
sum(a.amount_paid) as total_pay,
count(a.regstr_date) as attendance_count,
avg(a.amount_paid) as average_paid
from attendance a Inner Join offer o on a.offering_id = o.offering_id
inner join teacher t on t.teacher_id = o.teacher_id
where a.offering_id in('30','40','50')
group by a.visitor_id,t.start_date
having avg (a.amount_paid)<=600 and max(a.regstr_date)>t.start_date;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35077302
复制相关文章
相似问题
腾讯云开发者
