解决方案 »
- oracle客户端连接服务器正常,查询超时
- 用PL/SQL语言,不能使用GROUP BY 和连接。
- 交叉表问题 哪位大大帮忙解决下。
- oracle 怎样再存过程中调用好几个存储过程
- 分組查詢后排名次
- 初学者:ORACLE 10g有没有象SQL Server 的企业管理器?
- 求各位大侠帮我看一下这个!!!(proc)
- 哪里有下Oracle的FTP?
- 有人用过ERWIN么
- The Network Adapter could not establish the connection 供应商代码 17002(Oracle 12c)
- oracle触发器中如何获取应用系统用户名?
- java调用oracle存储过程报错,求解
在我的数据库执行如下的语句就会报错,你试试:with t as
(
select 1 id,100 amt from dual union all
select 1 id,200 amt from dual union all
select 2 id,100 amt from dual union all
select 3 id,300 amt from dual union all
select 3 id,100 amt from dual union all
select 4 id,200 amt from dual union all
select 4 id,100 amt from dual union all
select 5 id,200 amt from dual union all
select 6 id,100 amt from dual
)
select * from t group by id having amt=max(amt)
在我的数据库执行如下的语句就会报错,你试试:with t as
(
select 1 id,100 amt from dual union all
select 1 id,200 amt from dual union all
select 2 id,100 amt from dual union all
select 3 id,300 amt from dual union all
select 3 id,100 amt from dual union all
select 4 id,200 amt from dual union all
select 4 id,100 amt from dual union all
select 5 id,200 amt from dual union all
select 6 id,100 amt from dual
)
select * from t group by id having amt=max(amt)
好像是个问题,但是确实没报错,我在navicate里运行的
在我的数据库执行如下的语句就会报错,你试试:with t as
(
select 1 id,100 amt from dual union all
select 1 id,200 amt from dual union all
select 2 id,100 amt from dual union all
select 3 id,300 amt from dual union all
select 3 id,100 amt from dual union all
select 4 id,200 amt from dual union all
select 4 id,100 amt from dual union all
select 5 id,200 amt from dual union all
select 6 id,100 amt from dual
)
select * from t group by id having amt=max(amt)命令行里也没错,难道是mysql支持这样写?
看着很奇怪,你还是换种方式吧。
给你个思路,例如:with t as
(select 1 id, 100 amt
from dual
union all
select 1 id, 200 amt
from dual
union all
select 2 id, 100 amt
from dual
union all
select 3 id, 300 amt
from dual
union all
select 3 id, 100 amt
from dual
union all
select 4 id, 200 amt
from dual
union all
select 4 id, 100 amt
from dual
union all
select 5 id, 200 amt
from dual
union all
select 6 id, 100 amt
from dual)
select t.*
from t, (select id, max(amt) max_amt from t group by id) t1
where t.id = t1.id
and t.amt = t1.max_amt
使用关联子查询可以实现,但没闹明白为啥group by和having为啥不行,还有为啥MySQL支持这种写法
这确实是个问题,按理说使用了group by,select后就只能是分组字段或组函数,但MySQL貌似语法比较宽泛,居然支持非分组字段,而且支持*
使用关联子查询可以实现,但没闹明白为啥group by和having为啥不行,还有为啥MySQL支持这种写法
这样写试试,看能成功么?
select ename,job,max(salary) from emp group by job
使用关联子查询可以实现,但没闹明白为啥group by和having为啥不行,还有为啥MySQL支持这种写法
这样写试试,看能成功么?
select ename,job,max(salary) from emp group by job结果居然是对的,但ename为什么没报错?
看来是MySQL的问题了,我用Oracle测试下,多谢