问题显示SQL查询 [英] Problem to display sql query

问题描述

在我的总线详细信息"的Sql表中，具有以下字段

In my Sql table for Bus Details has following fields

Bus_id Bus_name  TotalSeats 
1       KPN       44
2        SPS      45
3        KRCTC     30

我还有一张桌子用来预订座位

I have another table used for booking seats

Bus_id   Date       Total Seats   BookingSeats
1       27/3/2011     44             3
1       27/3/2011      44            15
1       27/3/2011      44            26
3       28/3/2011      30             10

我想在提供日期时获得按日期排列的记录，它将显示每辆巴士在该特定日期的可用座位

如果我给的日期是28/3/2011

它会给出以下

I want get the records for datewise when i give the date It will display the available seats for that particular date for every buses

If I give the date is 28/3/2011

It will give following

Bus_id  Bus_name   SeatsAvailable
3         KRCTC       20
2         SPS         45
1         KPN         44

如果我指定日期27/3/2011

If I give the date 27/3/2011

Bus_id Bus_name  seatsAvailable
2        SPS        45
3         KRCTC      30

推荐答案

在这里..

Here you go..

SELECT bd.Bus_Id, bd.Bus_Name, TotalSeats - ISNULL(BookingSeats, 0) AS SeatsAvailable
FROM Bus_Details bd
LEFT JOIN Booked_Seats bs ON bd.Bus_Id = bs.Bus_Id
AND Date = '3/28/2011'

这篇关于问题显示SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！