怎么使内容不重复

[伟明]

CREATE TABLE Employee (
irdNumber char(11),
lastName varchar(30),
firstName varchar(30),
primary key (irdNumber)
)

CREATE TABLE Customer (
accountId int,
lastName varchar(30),
firstName varchar(30),
street varchar(100), 
city varchar(32),
postcode char(4),
balance real,
primary key (accountId)
)

CREATE TABLE Store (
storeId int,
street varchar(100), 
city varchar(32),
postcode char(4),
manager char(11),
primary key (storeId),
foreign key (manager) references Employee(irdNumber)
)

CREATE TABLE TimeSheet (
irdNumber char(11),
date datetime,
startTime time,
endTime time,
storeId int,
primary key (irdNumber, date, startTime),
foreign key (storeId) references Store(storeId)
)

CREATE TABLE Movie (
movieId varchar(10),
title varchar(100),
genre varchar(32),
rating varchar(10),
primary key (movieId),
)

CREATE TABLE Dvd (
dvdId int,
dateAcquired datetime,
movieId varchar(10),
storeId int,
primary key (dvdId),
foreign key (movieId) references Movie,
foreign key (storeId) references Store
)

CREATE TABLE Rental (
accountId int,
dvdId int,
dateRented datetime,
dateDue datetime,
cost real,
primary key (accountId, dvdId),
foreign key (accountId) references Customer (accountId),
foreign key (dvdId) references Dvd (dvdId),
)

insert into Employee values ('111-222-333', 'Smith', 'Linda')
insert into Employee values ('111-222-444', 'Brown', 'Mark')
insert into Employee values ('111-222-555', 'Jones', 'Julia')
insert into Employee values ('111-222-666', 'Green', 'Peter')
insert into Employee values ('111-222-777', 'Ash', 'Marian')

insert into Customer values (101, 'McCaw', 'Roberta', '34 Vine St.', 'Christchurch', '1234', 0.0)
insert into Customer values (102, 'Woodcock', 'Luke', '342 Hall St.', 'Auckland', '1278', 3.0)
insert into Customer values (103, 'Umaga', 'Wilma', '12 Kowhai Av.', 'Hamilton', '9876', 31.0)
insert into Customer values (104, 'Carter', 'David', '56 Vinegar St.', 'Christchurch', '1338', 35.0)
insert into Customer values (105, 'Cowan', 'Tina', '78 Poplar Rd.', 'Dunedin', '1235', 0.0)
insert into Customer values (106, 'Nonu', 'Brad', '49 Pine Close.', 'Wellington', '1123', 0.0)
insert into Customer values (107, 'Kahui', 'Julie', '60 Kina St.', 'Hamilton', '1222', 0.0)
insert into Customer values (108, 'Toeava', 'Robert', '124 Holly St.', 'Auckland', '1334', 10.55)

insert into Store values (1, 'Buggy St.', 'Christchurch', '3456', '111-222-777')
insert into Store values (2, 'Bee Lane', 'Auckland', '3377', '111-222-666')
insert into Store values (3, 'Carmen St.', 'Hamilton', '3443', '111-222-666')
insert into Store values (4, 'Freezing St.', 'Dunedin', '6556', '111-222-777')

insert into TimeSheet values ('111-222-333', '2011-01-01 00:00:00' , '2011-01-01 08:15:00' , '2011-01-01 12:00:00' , 3)
insert into TimeSheet values ('111-222-444', '2011-01-01 00:00:00' , '2011-01-01 08:15:00' , '2011-01-01 12:00:00' , 3)
insert into TimeSheet values ('111-222-555', '2011-02-02 00:00:00' , '2011-02-02 14:00:00' , '2011-02-02 22:00:00' , 4)
insert into TimeSheet values ('111-222-333', '2011-03-03 00:00:00' , '2011-03-03 08:15:00' , '2011-01-01 12:00:00' , 3)
insert into TimeSheet values ('111-222-555', '2011-04-04 00:00:00' , '2011-04-04 10:00:00' , '2011-04-04 14:00:00' , 4)
insert into TimeSheet values ('111-222-555', '2011-04-04 00:00:00' , '2011-04-04 15:00:00' , '2011-04-04 19:00:00' , 4)

insert into Movie values ('1001', 'The Three Amigos', 'mystery', 'R')
insert into Movie values ('1231', 'Annie Hall', 'romantic comedy', 'R')
insert into Movie values ('1451', 'Lady and the Tramp', 'animation', 'PG')
insert into Movie values ('1891', 'Animal House', 'comedy', 'PG-13')
insert into Movie values ('4501', 'Elizabeth', 'costume drama', 'PG-13')
insert into Movie values ('5531', 'True Grit', 'western', 'R')
insert into Movie values ('9871', 'RV', 'comedy', 'PG-13')

insert into Dvd values (2, '2010-01-25 00:00:00', '1001', 3)
insert into Dvd values (3, '2001-02-05 00:00:00', '1231', 3)
insert into Dvd values (4, '2003-12-31 00:00:00', '1231', 4)
insert into Dvd values (5, '2010-04-05 00:00:00', '1231', 4)
insert into Dvd values (6, '2010-04-05 00:00:00', '1891', 4)
insert into Dvd values (12, '2000-03-25 00:00:00', '1231', 3)
insert into Dvd values (14, '2003-05-12 00:00:00', '1451', 4)
insert into Dvd values (77, '2005-04-29 00:00:00', '1891', 3)
insert into Dvd values (90, '2007-03-25 00:00:00', '4501', 3)
insert into Dvd values (99, '2008-10-10 00:00:00', '9871', 4)

insert into Rental values (103, 2, '2011-01-03 00:00:00', '2011-01-04 00:00:00', 15.90)
insert into Rental values (101, 5, '2011-02-22 00:00:00', '2011-02-25 00:00:00', 30.00)
insert into Rental values (101, 6, '2011-02-22 00:00:00', '2011-02-25 00:00:00', 30.00)
insert into Rental values (103, 12, '2010-12-01 00:00:00', '2010-12-31 00:00:00', 11.99)
insert into Rental values (101, 14, '2011-02-14 00:00:00', '2011-02-16 00:00:00', 5.90)
insert into Rental values (101, 90, '2011-01-01 00:00:00', '2011-01-08 00:00:00', 20.90)
insert into Rental values (101, 99, '2011-01-01 00:00:00', '2011-01-04 00:00:00', 30.90)

select Employee.irdNumber,Employee.firstName,Employee.lastName,TimeSheet.storeId
from Employee
left join TimeSheet
on Employee.irdNumber=TimeSheet.irdNumber

group by 用不了？怎么得到以下的结果，不重复
--irdNumber firstName lastName storeId
--111-222-333 Linda Smith 3
--111-222-444 Mark Brown 3
--111-222-555 Julia Jones 4
--111-222-666 Peter Green NULL
--111-222-777 Marian Ash NULL

[SQL777]

怎么不能GROUP BY ？

select DISTINCT Employee.irdNumber,Employee.firstName,Employee.lastName,TimeSheet.storeId
from Employee
left join TimeSheet
on Employee.irdNumber=TimeSheet.irdNumber

[伟明]

谢谢楼上的， 为什么group by 不能用啊？ 还有没有其他的方法 得出相同的结果

[SQL777]

[Quote=引用 2 楼 的回复:]
谢谢楼上的， 为什么group by 不能用啊？ 还有没有其他的方法 得出相同的结果
[/Quote]
select Employee.irdNumber,Employee.firstName,Employee.lastName,TimeSheet.storeId
from Employee
left join TimeSheet
on Employee.irdNumber=TimeSheet.irdNumber
gourp by Employee.irdNumber,Employee.firstName,Employee.lastName,TimeSheet.storeId


也一样

[伟明]

哦~~~~~ 明白了，我之前用单单group by Employee.irdNumber 所以不行，学习了 谢谢