22,209
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='VFlightSpecial' AND type='V')
DROP VIEW VFlightSpecial
GO
CREATE VIEW VFlightSpecial
AS
SELECT re.AIR_AFTER_DISCOUNT_ADULT_PRICE AS FULLPRICE, C.DISCOUNT, C.FROMCITYN, C.TOCITYN, C.FROMCITYC, C.TOCITYC,
SUBSTRING(CONVERT(varchar(10), C.FROMDT, 120), 6, 5) AS FROMDT, CONVERT(varchar(10), C.FROMDT, 120) AS FROMDATETIME
FROM (SELECT DISCOUNT, FROMCITYN, TOCITYN, FROMCITYC, TOCITYC, FROMDT
FROM (SELECT A0.AIR_ADULT_DISCOUNT AS DISCOUNT, A0.AIR_FROM_CITY_NAME AS FROMCITYN, A0.AIR_TO_CITY_NAME AS TOCITYN,
A0.AIR_FROM_CITY_CODE AS FROMCITYC, A0.AIR_TO_CITY_CODE AS TOCITYC, MIN(A0.AIR_FROM_DATETIME)
AS FROMDT
FROM (SELECT
AIR_FROM_AIRPORT_NAME, AIR_TO_AIRPORT_NAME, AIR_FROM_CITY_NAME, AIR_TO_CITY_NAME,
AIR_FROM_DATETIME, AIR_TO_DATETIME, AIR_AIRFARE_PRICE, AIR_ADULT_DISCOUNT,
AIR_FROM_CITY_CODE, AIR_TO_CITY_CODE
FROM dbo.BA_CACHE_AIR_RESULT
WHERE (AIR_FROM_DATETIME BETWEEN GETDATE() AND GETDATE() + 60)) AS A0 INNER JOIN
(SELECT MIN(AIR_ADULT_DISCOUNT) AS AIR_ADULT_DISCOUNT, AIR_FROM_CITY_CODE, AIR_TO_CITY_CODE
FROM dbo.BA_CACHE_AIR_RESULT AS BA_CACHE_AIR_RESULT_1
WHERE (AIR_FROM_DATETIME BETWEEN GETDATE() AND GETDATE() + 60)
GROUP BY AIR_FROM_CITY_CODE, AIR_TO_CITY_CODE) AS A1 ON
A0.AIR_FROM_CITY_CODE = A1.AIR_FROM_CITY_CODE AND A1.AIR_TO_CITY_CODE = A0.AIR_TO_CITY_CODE AND
A1.AIR_ADULT_DISCOUNT = A0.AIR_ADULT_DISCOUNT
GROUP BY A0.AIR_ADULT_DISCOUNT, A0.AIR_FROM_CITY_NAME, A0.AIR_TO_CITY_NAME, A0.AIR_FROM_CITY_CODE,
A0.AIR_TO_CITY_CODE) AS B0) AS C INNER JOIN
dbo.BA_CACHE_AIR_RESULT AS re ON C.DISCOUNT = re.AIR_ADULT_DISCOUNT AND C.FROMCITYC = re.AIR_FROM_CITY_CODE AND
C.TOCITYC = re.AIR_TO_CITY_CODE AND C.FROMDT = re.AIR_FROM_DATETIME
GO
/****** Object: Table [dbo].[asdfsfsf] Script Date: 03/09/2011 16:44:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[asdfsfsf]') AND type in (N'U'))
DROP TABLE [dbo].[asdfsfsf]
GO
/****** Object: Table [dbo].[asdfsfsf] Script Date: 03/09/2011 16:44:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[asdfsfsf]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[asdfsfsf](
[AIR_ID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TRIPINDEX] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_AGENT_ID] [varchar](36) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FROM_AIRPORT_CODE] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TO_AIRPORT_CODE] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FROM_AIRPORT_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TO_AIRPORT_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FROM_CITY_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TO_CITY_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FROM_DATETIME] [datetime] NULL,
[AIR_TO_DATETIME] [datetime] NULL,
[AIR_AIRFARE_PRICE] [decimal](18, 2) NULL,
[AIR_ADULT_DISCOUNT] [decimal](18, 2) NULL,
[AIR_CHILD_DISCOUNT] [decimal](18, 2) NULL,
[AIR_BABY_DISCOUNT] [decimal](18, 2) NULL,
[AIR_ADULT_DISCOUNT_ORDER] [tinyint] NULL,
[AIR_TAX_FEE] [nvarchar](40) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_OIL_FEE] [nvarchar](40) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_AIRLINES_INFO] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FLIGHT_CODE] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FLIGHT_TYPE] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_CABIN_CODE] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_CABIN_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_AIRPLANE_INFO] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TUIPIAO_DESC] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_GENGGAI_DESC] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_QIANZHUAN_DESC] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_INSURANCE_FEE] [decimal](18, 0) NULL,
[AIR_TICKET_COUNT] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_LEG_TYPE] [nvarchar](1) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_AIRPORT_CODE] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_AIRPORT_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_CITY_NAME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_TO_DATETIME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_FROM_DATETIME] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_AIRLINES_INFO] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_FLIGHT_CODE] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_AIRPLANE_INFO] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_TAX_FEE] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TRANSIT_OIL_FEE] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FROM_CITY_CODE] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TO_CITY_CODE] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_AFTER_DISCOUNT_ADULT_PRICE] [decimal](18, 2) NULL,
[AIR_AFTER_DISCOUNT_CHILD_PRICE] [decimal](18, 2) NULL,
[AIR_AFTER_DISCOUNT_BABY_PRICE] [decimal](18, 2) NULL,
[AIR_AIRLINE_RULE_ID] [varchar](5) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TOURCODE_ID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_TJ] [nvarchar](1) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_FAREBASIS] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_CODESHARE] [varchar](5) COLLATE Chinese_PRC_CI_AS NULL,
[AIR_CREATETIME] [datetime] NULL,
[AIR_OFTEN] [int] NULL,
CONSTRAINT [PK_asdfsfsf] PRIMARY KEY NONCLUSTERED
(
[AIR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
select
fromcityn,tocityn,...
from
tb t
where
exists(select 1 from tb where fromcityn<>t.fromcityn and datediff(mm,getdate(),fromdatetime)<=1 and t.discount<4.00)