[code="java"]
select a1.a1_num,a1.a1_field2,a1.a1_field11,a7.a7_lot,a7.a7_date,sum(a7_count)
from a7,a1
where a1.a1_id=a7.a1_id and a1.a1_id in (select a1.a1_id,a1.a1_field11
//问题在这里 我要返回id结果集， select 后面如果不写"a1.a1_field11"这个字段的话 having语句又无法执行
from a1,a7
where a1.a1_id=a7.a1_id
group by a7.a1_id
having a1.a1_field11>sum(a7_count))
group by a7.a7_lot,a7.a1_id
order by a7.a1_Id;
//实在对不起 没分了 非常感谢您的回答
[/code]
看再嵌一层是否可以：
[code="java"]
select a1.a1_num,
a1.a1_field2,
a1.a1_field11,
a7.a7_lot,
a7.a7_date,
sum(a7_count)
from a7, a1
where a1.a1_id = a7.a1_id
and a1.a1_id in
(select kk.a1_id
from (select a1.a1_id, a1.a1_field11
from a1, a7
where a1.a1_id = a7.a1_id
group by a7.a1_id
having a1.a1_field11 > sum(a7_count)) kk)
group by a7.a7_lot, a7.a1_id
order by a7.a1_Id;
[/code]
New query.
select a1.a1_num,
a1.a1_field2,
a1.a1_field11,
t1.a7_lot,
t1.a7_date,
t1.a7_count_sum
from a1, (select a1_id, a7_lot, a7_date sum(a7_count) as a7_count_sum from a7 group by a1_id) t1
where a1.a1_id = t1.a1_id
group by t1.a7_lot,t1.a1_id
order by t1.a1_id
Have a try.