你这个应该会报错的,group by里面只有一个job字段,显示的时候用到了全部字段,怎么会成功呢? 在我的数据库执行如下的语句就会报错,你试试: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)
你这个应该会报错的,group by里面只有一个job字段,显示的时候用到了全部字段,怎么会成功呢? 在我的数据库执行如下的语句就会报错,你试试: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里运行的
你这个应该会报错的,group by里面只有一个job字段,显示的时候用到了全部字段,怎么会成功呢? 在我的数据库执行如下的语句就会报错,你试试: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
select * from emp e1 where e1.salary=(select MAX(e2.salary) from emp e2 where e2.job=e1.job); 使用关联子查询可以实现,但没闹明白为啥group by和having为啥不行,还有为啥MySQL支持这种写法
select * from emp e1 where e1.salary=(select MAX(e2.salary) from emp e2 where e2.job=e1.job); 使用关联子查询可以实现,但没闹明白为啥group by和having为啥不行,还有为啥MySQL支持这种写法 这样写试试,看能成功么? select ename,job,max(salary) from emp group by job
恩,Mysql支持这样写,不会像oracle一样强行要求
select * from emp e1 where e1.salary=(select MAX(e2.salary) from emp e2 where e2.job=e1.job); 使用关联子查询可以实现,但没闹明白为啥group by和having为啥不行,还有为啥MySQL支持这种写法 这样写试试,看能成功么? select ename,job,max(salary) from emp group by job结果居然是对的,但ename为什么没报错?
在我的数据库执行如下的语句就会报错,你试试: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测试下,多谢