165
社区成员
发帖
与我相关
我的任务
分享一条sql可以单独查询，为什么作为子查询，就查询不了了呢？
sql如下：
select * from (
select sum(a.operate_num) total, c.name as province from (
select * from table1 where ipadress ~ '^((?:(?:25[0-5]|2[0-4]\\\\d|((1\\\\d{2})|([1-9]?\\\\d)))(?:\\\\.)){3}(?:25[0-5]|2[0-4]\\\\d|((1\\\\d{2})|([1-9]?\\\\d))))$'
) a left join table2 c on SUBSTRING(a.area_code,1,4) || '00' = c.code where c.name is not null
and a.app_code = '530900000002' and a.operate_time > '20160410' and a.operate_time <= '20170410235959'
group by province
) tmp
order by tmp.total desc
运行之后就报错了：line 3 ERROR: aggregates not allowed in WHERE clause 说是不允许在WHERE子句中聚集，但是好像并没有吧
数据库是greenplum，有没有了解的，指点下
select * from (
select sum(a.operate_num) total, c.name as province from (
select * from table1 where ipadress ~ '^((?:(?:25[0-5]|2[0-4]\\\\d|((1\\\\d{2})|([1-9]?\\\\d)))(?:\\\\.)){3}(?:25[0-5]|2[0-4]\\\\d|((1\\\\d{2})|([1-9]?\\\\d))))$'
) a left join table2 c on SUBSTRING(a.area_code,1,4) || '00' = c.code where c.name is not null
and a.app_code = '530900000002' and a.operate_time > '20160410' and a.operate_time <= '20170410235959'
group by province
) tmp
order by tmp.total desc
运行之后就报错了：line 3 ERROR: aggregates not allowed in WHERE clause 说是不允许在WHERE子句中聚集，但是好像并没有吧
数据库是greenplum，有没有了解的，指点下
...全文
请发表友善的回复…
发表回复
esena 2017-09-25
- 打赏
- 举报
aggregation结果的过滤用having，不是where，请叫我雷锋
我说假如 2017-04-11
- 打赏
- 举报
还有就是where条件里面不能写sum（）>0的条件
我说假如 2017-04-11
- 打赏
- 举报
噢。正则斜杠多写了。。。因为是从代码里面拿出来的，忘了去掉。。。。
我说假如 2017-04-11
- 打赏
- 举报
select * from (
select sum(a.operate_num) total, c.name as province from (
select * from table1 where ipadress ~ '^((?:(?:25[0-5]|2[0-4]\\\\d|((1\\\\d{2})|([1-9]?\\\\d)))(?:\\\\.)){3}(?:25[0-5]|2[0-4]\\\\d|((1\\\\d{2})|([1-9]?\\\\d))))$'
and app_code = '530900000002' and operate_time > '20160410' and operate_time <= '20170410235959'
) a left join table2 c on SUBSTRING(a.area_code,1,4) || '00' = c.code
group by province
) tmp where tmp.province is not null
order by tmp.total desc
改成这样又可以了
