sql语句编写

[xu82481469]

是这样的，我有个数据库有以下3项
ID
class
AccessTime

我想查询在2000年1月1日入学的学生中哪个班的最多。

[geniuswjt]

--这样？
select class,count(*) from tb 
where AccessTime='2000-1-1'
group by class
order by count(*) desc

[chuanzhang5687]

班级人数在哪个字段？

[chuanzhang5687]

还是id代表着学生id？

[kensouterry]

select a.class, a.number
from
(select class, count(id) as number
from tb
where AccessTime='2000-1-1'
group by class) a
where a.number=max(a.number)

[LMAOhuaNL]

select class,count(*) from tb where AccessTime='2000-1-1' group by class order by count(*) desc

[xmlbh]

同意 LMAOhuaNL

[chuanzhang5687]

create table tb
(
	id	int,
	class varchar(10),
	strtime datetime
)
insert into tb values(1,'1班','2000-01-01')
insert into tb values(2,'1班','2000-01-01')
insert into tb values(3,'1班','2001-01-01')
insert into tb values(4,'1班','2000-01-01')
insert into tb values(5,'1班','2001-01-01')
insert into tb values(6,'1班','2000-01-01')
insert into tb values(7,'1班','2000-01-01')

insert into tb values(1,'2班','2000-01-01')
insert into tb values(2,'2班','2001-01-01')
insert into tb values(3,'2班','2000-01-01')
insert into tb values(4,'2班','2000-01-01')
insert into tb values(5,'2班','2000-01-01')

insert into tb values(1,'3班','2000-01-01')
insert into tb values(2,'3班','2000-01-01')
select class from 
(
	select count(id) num,class from tb 
	where accessTime = '2000-01-01' group by class
) a 
where num =
(
	select max(num) from 
		(
			select count(id) num,class from tb 
			where accessTime = '2000-01-01' group by class
		)a 
)