UC知道求解这两道SQL题
来源：百度知道 编辑：UC知道 时间：2024/09/28 08:32:06
1、There are three tables:
Table: User Fields: user_id, user_name
Table: Loan Fields: user_id, book_isbn
Table: Book Fields: book_isbn, book_name
Write a simple sql to select the user_id and the sum of books loaned to get a results like below:
User_id Count
S1 1
S2 5
S3 3
2. Bonus: We have one table Service Rating with the following data
Date Service Type Rating
20030401 Gardening 10%
20030501 Gardening 25%
20030101 Fishing 10%
20030801 Maintenance 5%
20030501 Maintenance 15%
Write a simple sql which will retrieve the distinct servicetype with the latest date and rating.
Date Service Type Rating
200305
Table: User Fields: user_id, user_name
Table: Loan Fields: user_id, book_isbn
Table: Book Fields: book_isbn, book_name
Write a simple sql to select the user_id and the sum of books loaned to get a results like below:
User_id Count
S1 1
S2 5
S3 3
2. Bonus: We have one table Service Rating with the following data
Date Service Type Rating
20030401 Gardening 10%
20030501 Gardening 25%
20030101 Fishing 10%
20030801 Maintenance 5%
20030501 Maintenance 15%
Write a simple sql which will retrieve the distinct servicetype with the latest date and rating.
Date Service Type Rating
200305
1. select u.user_id,
count(*) as Count
from user u inner join loan l on u.user_id=l.user_id
group by u.user_id
2. select sr1.*
from [Service Rating] sr1,
(select max(Date) as Date, [Service Type]
from [Service Rating]
group by [Service Type]) as sr2
where sr1.Date=sr2.Date
and sr1.[Service Type]=sr2.[Service Type]
select user_id,sum(book_name)as count from A,B,C where a.user_id=B.user_id and B.book_isbn=C.isbn group by user_id,count
1.主要是用到连表查询，将连表条件设置好就可以了，
select u.user_id,
count(*) as Count
from user u inner join loan l on u.user_id=l.user_id
group by u.user_id
2.用到group by分组关键字，以及分组内的Having条件
select * from ServiceRating
group by ServiceType
having Date=max(Date)
呵呵，希望能有帮助，^_^