显示本月上机时间最长的前三名用户信息的查询语句怎么写？ - 编程论坛

#2

purana2007-09-09 09:59

不太清楚你的Card表和Computer表跟Record表有什么关系.如果没有关系.请问你又为什么把他说出来呢.扰乱别人?如果有关系.那请问你为什么又不说这两个表都有啥列来约束关系呢?

select top 3 Record.id from Record join (select id,(EndTime-BeginTime) AS Time from Record) AS t
on Record.id=t.id
order by t.time desc

#3

gxl11272007-09-09 12:07

哦，是这样的：
1）Card表用来存储上网卡信息，包括卡号(ID)、密码、卡上的余额和持卡人的姓名；
2）Computer表用来存储电脑及状态信息，包括电脑的编号(ID)、是否在使用、备注说明；
3）Record表用来存储每次上机信息，有两个外键CardID和ComputerID，分别引用Card表的ID字段和Computer表的ID字段；
另外Record表中还有两个列名BeginTime（开机时间）和EndTime（下机时间）
4）题目就是编写查询显示本月上机时间最长的前三名用户卡号

希望得到再次指导！谢谢!

#4

shuzai19852007-09-09 13:43

select top 3 ID ,t.time from card ,(select card.ID,(EndTime-BeginTime) AS Time from record ) AS t where
card.ID=t.card.ID
order by t.time desc
不知道对不对 ..............................

#5

purana2007-09-09 14:15

drop table if exists `Card`;
drop table if exists `Computer`;
drop table if exists `Record`;

create table `Card`(
`ID` int not null auto_increment,
`CardID` char(8) not null,
`pwd` varchar(16) not null,
`balance` float not null,
`name` varchar(16) not null,
primary key(`ID`)
)engine=myisam;

create table `Computer`(
`ID` int not null auto_increment,
`ComputerID` char(3) not null,
`CardID` char(8) not null,
`isUse` bit(1) not null default 0,
`remark` varchar(300),
primary key(`ID`),
foreign key(`CardID`) references `Card`(`CardID`)
)engine=myisam;

create table `Record`(
`ID` int not null auto_increment,
`CardID` char(8) not null,
`ComputerID` char(8) not null,
`BeginTime` datetime ,
`EndTime` datetime,
primary key(`ID`),
foreign key(`ComputerID`) references `Computer`(`ComputerID`),
foreign key(`CardID`) references `Card`(`CardID`)
)engine=myisam;

insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000001','123456',15.3,'purana');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000002','756321',15.3,'jiajia');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000003','12345',15.3,'jiadf');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000004','75145',15.3,'mysql');

insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('001','123456',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('002','756321',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('003','12345',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('015','75145',1);

insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000001','001','2007-09-09 10:15:12','2007-09-09 11:01:02');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000002','002','2007-09-09 15:35:33','2007-09-09 16:22:33');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000003','003','2007-09-09 20:14:01','2007-09-10 01:33:02');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000004','015','2007-09-10 01:42:10','2007-09-10 22:01:02');

#6

purana2007-09-09 14:15

select Card.CardID,name,t.BeginTime,t.EndTime
from `Card` join (select CardID,BeginTime,EndTime,(EndTime-BeginTime) as `time`
from Record
where BeginTime between '2007-09-09 00:00:00' and '2007-09-09 23:55:55') As t
on Card.CardID=t.CardID
order by t.`time` desc
limit 3;

#7

purana2007-09-09 14:16

在mysql测试的.

#8

thllilac2007-09-10 15:03

select top 3 ID ,t.time from card ,(select cardID,datediff(ss,convert(datetime,BeginTime,120),convert(datetime,EndTime,120)) AS Time from record ) AS t where
card.ID=t.cardID
order by t.time desc

#9

hushimiao2007-09-10 19:27

頂樓上上。。

#10

taotao70802007-09-14 15:56

大家都很厉害啊！