开发者

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 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜