如何从 SQL Server ToCityId 和 FromCityId Bases 获取数据 [英] how to get data from SQL server ToCityId And FromCityId Bases

问题描述

我有以下查询，我想显示数据 fromCityId AND ToCityId .假设乘客旅行 fromCity 伦敦 toCity 曼彻斯特.我如何编写这样的查询，当我使用 where 子句和 in 时，它在 fromcity 和 tocity<中显示相同的值/代码>

I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where clause and in, its show me same values in fromcity and tocity

预期结果如下图

  Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate 
    from VoucharHotel vh  
    inner join City fCity on   vh.City = fCity.CityId  inner join City tCity on
     vh.City = tCity.CityId 
     where vh.InDate  between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'   AND City in (1,2)

CREATE TABLE VoucharHotel (
    ID int IDENTITY(1,1) PRIMARY KEY,
    VoucharId Int ,
    CityId int,
    HotelId  int,
    InDate Datetime,
    OutDate Datetime
);

CREATE TABLE City (
    CityId int IDENTITY(1,1) PRIMARY KEY,
    CityName varchar(200),
);
insert into City Values('London')
insert into City Values('Manchester')
insert into City Values('Birmingham')
insert into City Values('Leeds')

CREATE TABLE HotelMaster (
    HotelId int IDENTITY(1,1) PRIMARY KEY,
    HotelName varchar(200),
);
insert into HotelMaster Values('London Hotel')
insert into HotelMaster Values('Manchester Hotel')
insert into HotelMaster Values('Birmingham Hotel')
insert into HotelMaster Values('Leeds Hotel')

Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')
Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')
Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')

Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')
Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')

推荐答案

以下是否能解决您的问题:

Would the below resolve your problem:

with cte
(VoucherID,FromCity,ToCity,InDate)
as
(
select
    vh.VoucharId
,   fCity.CityName as FromCity
,   tCity.CityName as ToCity
,   InDate
from        VoucharHotel    vh
inner join  City            fCity on    vh.City = fCity.CityId
inner join  City            tCity on    vh.City = tCity.CityId 
where       vh.InDate  between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'
)

select
*
from    cte
where   City in (1,2)

如果您需要进行更改，请告诉我.

Let me know if you need changes made.

这篇关于如何从 SQL Server ToCityId 和 FromCityId Bases 获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！