SQL Statement how to retrieve a data which have 2 or more record?
I have a Car table and User table which store car and user particular. and a RentRecord table to store which user have rent certain car and date. I want to retrieve the user who have rent car more than 2 times, how to write the sql statement, i try using count and group by but in the end i only get one row data.
Select count(rr.userId) as T, userName, carType
from rentrecord rr
inner join user u on u.userId = rr.userId
inner join car c on c.carId = rr.carId
group by (rr.userId) havi开发者_运维百科ng T>=2;
how can i modify the sql statement so it return user record who have rent car more than 2 times. Sorry let me cclarify it, it return only one records, i need to list out the records details. I mean for example User A rent CarA and CarB so inside rentrecord table should have 2 row data, i need retrieve this 2 row data. Sorry for ambigous.
Rented any car two or more times:
select userID,COUNT(*)
from rentRecord
group by userID
having COUNT(*) > 2
Rented one particular car two or more times:
select userID,carID,COUNT(*)
from rentRecord
group by userID,carID
having COUNT(*) > 2
Rented one particular car (with an obvious single car type) two or more times, with additional data:
select userID,username,cartype,Cnt
from (select userID,carID,COUNT(*) as Cnt
from rentRecord
group by userID,carID
having COUNT(*) > 2) multi
inner join
user u
on
multi.userID = u.UserID
inner join
car c
on
multi.carID = c.CarID
Based on edit - return all rental info for a user who has rented multiple cars:
SELECT
* /* TODO - Specify columns */
from
[User] u
inner join
rentRecord rr
on
u.UserID = rr.UserID
inner join
Car c
on
rr.CarID = c.CarID
where
u.UserID in (select UserID from (select userID,COUNT(*) Cnt
from rentRecord
group by userID
having COUNT(*) >= 2) t)
I've used the following tables, since we don't currently have a schema from the OP:
create table [User] (
UserID int not null
)
go
insert into [User](UserID)
select 1 union all
select 2
go
create table Car (
CarID int not null
)
go
insert into Car(CarID)
select 1 union all
select 2
go
create table rentRecord (
UserID int not null,
CarID int not null
)
go
insert into rentRecord(UserID,CarID)
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 2,2
go
in mysql syntax
SELECT
A.id, COUNT(B.id)
FROM userTable A
LEFT JOIN rentRecordTable B on B.user_id_of_rent = A.id
GROUP BY A.id
HAVING COUNT(B.id) > 2
精彩评论