Brug af TimeDiff i Mysql.... Hjælp
Har oprettet følgende database og vil gerne udvælge de studerende som følger kurser "class" som undervises på det samme tidspunkt...Har prøver følgende:
SELECT StudName, CTime AS `CTime.1`, CTime AS `CTime.2`, TIMEDIFF(CTime.1 - CTime.2) AS `timediff`
FROM ( SELECT StudName
FROM Student AS s, Faculty AS f, Enrolled AS e, Class AS c
WHERE s.StudId = e.StudId
AND e.CName = c.CName
AND c.FacultyId = f.FacultyId
AND timediff = '0')
Men det virker ikke?
CREATE TABLE Student(
StudId INT PRIMARY KEY AUTO_INCREMENT,
StudName VARCHAR(50) NOT NULL UNIQUE,
Major VARCHAR(10) NOT NULL,
Level VARCHAR(10) NOT NULL,
age INT (3)
) ENGINE=InnoDB;
CREATE TABLE Faculty (
FacultyId VARCHAR(2) PRIMARY KEY NOT NULL,
FName VARCHAR(50) NOT NULL,
DepartmentId INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE Class(
CName VARCHAR(50) NOT NULL UNIQUE,
CTime Time NOT NULL,
Room VARCHAR(50),
FacultyId VARCHAR (2) NOT NULL,
FOREIGN KEY (FacultyId) REFERENCES Faculty (FacultyId)ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE Enrolled(
StudID INT NOT NULL,
CName VARCHAR(50) NOT NULL,
FOREIGN KEY (StudID) REFERENCES Student(StudID)ON DELETE CASCADE,
FOREIGN KEY (CName) REFERENCES Class (Cname)ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO Student (StudName, Major, Level, age)
VALUES (\'Morten Aggerholm\',\'Computer Science\',\'SN\',\'17\'),
(\'Søren Hansen\',\'Computer Science\',\'SN\',\'17\'),
(\'Bo Nielsen\',\'Databases\',\'SO\',\'21\'),
(\'Michael Laudrup\',\'Databases\',\'SO\',\'21\'),
(\'Lars Hansen\',\'Programming\',\'SR\',\'30\'),
(\'Ib Olsen\',\'History\',\'JR\',\'29\'),
(\'Lars Jørgensen\',\'Webdesign\',\'FR\',\'18\'),
(\'Ib Larsen\',\'Computer Science\',\'FR\',\'18\'),
(\'Hanne Ibsen\',\'Webdesign\',\'SR\',\'16\'),
(\'Ib Ibsen\',\'Computer Science\',\'SR\',\'16\');
INSERT INTO Faculty
VALUES (\'L1\', \'Ali\',\'SS01\'),
(\'B1\', \'Hans Nielsen\',\'BA02\'),
(\'B2\', \'James Hansen\',\'BA01\'),
(\'L2\', \'Mohan Wang\',\'SS02\'),
(\'E1\', \'Lis Hansen\',\'EE01\'),
(\'E2\', \'Kenny Olsen\',\'EE02\'),
(\'S1\', \'Camilla Olsen\',\'SP01\'),
(\'A1\', \'Lasse Nielsen\',\'HA01\'),
(\'A2\', \'Henrik Gade\',\'HA02\'),
(\'P1\', \'Peter Snus\',\'PP01\'),
(\'P2\', \'Lars Jacobsen\',\'PP02\'),
(\'B3\', \'Harry Potter\',\'BA03\');
INSERT INTO Class VALUES
(\'Data Mining\', \'13:00:00\',\'4A03\',\'L1\'),
(\'Database Design\', \'13:00:00\',\'R128\',\'B1\'),
(\'Programming\', \'09:00:00\',\'4B09\',\'B2\'),
(\'Interactive Multimedia\', \'09:00:00\',\'2A03\',\'L2\'),
(\'Project Management\', \'16:00:00\',\'4A01\',\'E1\'),
(\'Python Programming\',\'16:00:00\',\'2A09\',\'E2\'),
(\'Website Design\', \'17:00:00\',\'1A03\',\'S1\'),
(\'Learning Theory\', \'17:00:00\',\'3A01\',\'A1\');
INSERT INTO Enrolled
VALUES (\'1\',\'Data Mining\'),
(\'1\',\'Learning Theory\'),
(\'1\',\'Interactive Multimedia\'),
(\'2\',\'Interactive Multimedia\'),
(\'2\',\'Learning Theory\'),
(\'2\',\'Python Programming\'),
(\'3\',\'Database Design\'),
(\'3\',\'Python Programming\'),
(\'3\',\'Project Management\'),
(\'4\',\'Data Mining\'),
(\'4\',\'Website Design\'),
(\'4\',\'Database Design\'),
(\'5\',\'Programming\'),
(\'5\',\'Data Mining\'),
(\'5\',\'Interactive Multimedia\'),
(\'6\',\'Programming\'),
(\'7\',\'Website Design\'),
(\'8\',\'Website Design\'),
(\'2\',\'Data Mining\'),
(\'7\',\'Data Mining\');