现有一张pet表,有name,owner,birht,death字段,想用MAX函数来查询出pet表中年龄最大的宠物
select name,MAX(year(death)-year(birth)) as age from pet;
为什么这样写报错呢?MAX(只能列名),呢这样该怎么写呢?
注意:(还有别的方法能查出想要的结果,在这里只想知道MAX这个函数查询的)
select name,MAX(year(death)-year(birth)) as age from pet;
为什么这样写报错呢?MAX(只能列名),呢这样该怎么写呢?
注意:(还有别的方法能查出想要的结果,在这里只想知道MAX这个函数查询的)
select name,year(death)-year(birth) as age from pet
order by age desc limit 1;
帖子下还有这句哦!!
这样写也可以,为什么不能先求出差再取最大呢?
insert into pet values('1',1,'2010-10-10','2011-10-11');
insert into pet values('2',1,'2010-10-12','2012-10-14');
insert into pet values('3',1,'2010-10-13','2014-10-18');
select b.name,max(b.age) from (select name, year(death)-year(birth) as age from pet order by age desc) b
这句话。
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
---创建表pet----
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
---插入测试数据--
insert into pet values ('fluffy','Harold','cat','f','1993-02-04','2001-09-15'),
('Claws','Gwen','cat','m','1994-01-12','2001-09-15'),
('Buffy','Harold','dog','m','1989-01-12','1999-02-21'),
('Fang','Benny','dog','m','1990-12-12','2001-09-15'),
('Bowser','Diane','dog','m','1991-05-04','2001-09-15'),
('Chirpy','Gwen','bird','f','1981-05-04','2001-09-15');
-----想要的结果是求年龄最大的-----
select name,year(death)-year(birth) as age from pet order by age desc limit 1;
+--------+------+
| name | age |
+--------+------+
| Chirpy | 20 |
+--------+------+
1 row in set (0.01 sec)
---在这里想利用Max()函数求得这样的结果--
Database changed
mysql> select b.name,max(b.age) from (select name, year(death)-year(birth) as ag
e from pet1 order by age desc) b ;
+--------+------------+
| name | max(b.age) |
+--------+------------+
| Chirpy | 20 |
+--------+------------+
1 row in set (0.00 sec)mysql>
mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| fluffy | Harold | cat | f | 1993-02-04 | 2001-09-15 |
| Claws | Gwen | cat | m | 1994-01-12 | 2001-09-15 |
| Buffy | Harold | dog | m | 1989-01-12 | 1999-02-21 |
| Fang | Benny | dog | m | 1990-12-12 | 2001-09-15 |
| Bowser | Diane | dog | m | 1991-05-04 | 2001-09-15 |
| Chirpy | Gwen | bird | f | 1981-05-04 | 2001-09-15 |
+--------+--------+---------+------+------------+------------+
6 rows in set (0.02 sec)mysql> select b.name,max(b.age) from (select name,year(death)-year(birth) as age
from pet order by age desc) b;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause
mysql>
为什么用15楼的sql,他的是对的,我的报错呢?版本问题还是???
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)mysql>
要求年龄最大的,要用min函数啊,不是max因为出生日期越大,年龄越小哦。因为可能会有出生日期一样且都是最大年龄的小狗狗,如果只要一个就这样:
SELECT name FROM pet WHERE birth=(SELECT MIN(birth) FROM pet LIMIT 1);如果都想显示那就这样:
SELECT name FROM pet WHERE birth IN (SELECT MIN(birth) FROM pet);
只是想弄清楚问题,只想弄清楚max和group by的用法
如果这句不能max(),呢我也才能回头了
SELECT *
FROM pet
WHERE datediff(
death, birth) IN (SELECT max(datediff(
death, birth))
FROM pet)
LIMIT 1;先选出最长的存活日期,然后再在这些存活日期最长的宠物中,选择一个。