interbase里group by 的使用问题，请大家帮俺看看

[metro]

问题1：
在interbase里数据库里我有一个measure_his表，
其中有lzx,measure_his,shongji_date,period等列。
现在我想查出不同lzx列对应shongji_date的最新日期 并且period
>0 并且youxiao_date在2003年8月份的内容
因为lzx列里的数据有的是重复的，所以我用group by 对其分组， 但interbase调试时
要报如下错误：
“SQL error code = -104
invalid column reference
Statement: select max(shongji_date),lzx,mc,youxiao_date
from measure_his group by lzx
having period>0 and youxiao_date<='2003-8-30' and youxiao_date>='2003-8-1' ”

我试着将查询语句改为：select max(shongji_date),lzx,mc,youxiao_date
from measure_his group by lzx,mc,youxiao_date
having period>0 and youxiao_date<='2003-8-30' and youxiao_date>='2003-8-1'
错误没有了，但是查出来的结果不是我想得到的值。(多出几项，没有过滤完，而且多出记录
的max(youxiao_date)列的值不对)

请问group by 后可以跟几个列？ 如果要解决问题1,还有其他的查询方法吗？

[erickleung]

SQL语法不对.

select max(shongji_date)
,lzx
,mc
,youxiao_date
from measure_his
where period>0
and youxiao_date between '2003-8-1' and '2003-8-31'
group by lzx,mc,youxiao_date

还是你要求那样的 max值??

[qingbai]

带有GROUP BY的SELECT后只能包含分组列（此处即LAX）和聚合函数，不能包含非分组列，这是SQL常识！

[metro]

但是我照qingbai(一松傲雪) 的做法： select max(shongji_date),lzx,mc,youxiao_date
from measure_his group by lzx
having period>0 and youxiao_date<='2003-8-30' and youxiao_date>='2003-8-1' 还是要报错误呀.

[metro]

错了，select max(shongji_date)
,lzx
,mc
,youxiao_date
from measure_his
where period>0
and youxiao_date between '2003-8-1' and '2003-8-31'
group by lzx,mc,youxiao_date 语法也不对， 应该用having ,但是这样分类好象也得不到我要得值