22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT t1.*,t2.软卧价格,t2.软卧余量,t3.硬卧价格,t3.硬卧余量 FROM (select TN.车次,日期,类别,始发站,终点站,硬座价格*t1.序号-硬座价格*t2.序号 as '硬座价格',TN.硬座数量-count(c1.ticketid) as'硬座余量'
from TN,stop t1,stop t2,ticket c1
where TN.车次 in
(select 车次 from stop where getdate='1945/8/8' and TN.车次 in
(select a.车次
from stop a,stop b where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 ))
and t1.sname='哈尔滨' and t2.sname='莫斯科'and t2.车次 in
(select a.车次
from stop a,stop b
where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )
and t1.车次 in
(select a.车次
from stop a,stop b
where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )
and t1.车次=TN.车次 and t2.车次 = TN.车次 and c1.车次=TN.车次
and c1.ticketid in
(select ticketid from ticket,stop where ticket.座位类型='硬座' and stop.sname='莫斯科' and ticket.序号 > stop.序号 and ticket.车次 in (select 车次 from stop where getdate='1945/8/8' and TN.车次 in
(select a.车次
from stop a,stop b where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )))
group by TN.车次,日期,类别,始发站,终点站,硬座价格*t1.序号-硬座价格*t2.序号,TN.硬座数量)t1 JOIN (select TN.车次,日期,类别,始发站,终点站,软卧价格*t1.序号-软卧价格*t2.序号 as '软卧价格',TN.软卧数量-count(c2.ticketid) as'软卧余量'
from TN,stop t1,stop t2,ticket c2
where TN.车次 in
(select 车次 from stop where getdate='1945/8/8' and TN.车次 in
(select a.车次
from stop a,stop b where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 ))
and t1.sname='哈尔滨' and t2.sname='莫斯科'and t2.车次 in
(select a.车次
from stop a,stop b
where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )
and t1.车次 in
(select a.车次
from stop a,stop b
where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )
and t1.车次=TN.车次 and t2.车次 = TN.车次 and c2.车次=TN.车次
and c2.ticketid in
(select ticketid from ticket,stop where ticket.座位类型='软卧' and stop.sname='莫斯科' and ticket.序号 > stop.序号 and ticket.车次 in (select 车次 from stop where getdate='1945/8/8' and TN.车次 in
(select a.车次
from stop a,stop b where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )))
group by TN.车次,日期,类别,始发站,终点站,软卧价格*t1.序号-软卧价格*t2.序号,TN.软卧数量)t2 ON t1.车次 = t2.车次 AND t1.日期 = t2.日期 AND t1.始发站 = t2.始发站 AND t1.终点 = t2.终点
JOIN (select TN.车次,日期,类别,始发站,终点站,硬卧价格*t1.序号-硬卧价格*t2.序号 as '硬卧价格',TN.硬卧数量-count(c3.ticketid) as'硬卧余量'
from TN,stop t1,stop t2,ticket c3
where TN.车次 in
(select 车次 from stop where getdate='1945/8/8' and TN.车次 in
(select a.车次
from stop a,stop b where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 ))
and t1.sname='哈尔滨' and t2.sname='莫斯科'and t2.车次 in
(select a.车次
from stop a,stop b
where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )
and t1.车次 in
(select a.车次
from stop a,stop b
where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )
and t1.车次=TN.车次 and t2.车次 = TN.车次 and c3.车次=TN.车次
and c3.ticketid in
(select ticketid from ticket,stop where ticket.座位类型='硬卧' and stop.sname='莫斯科' and ticket.序号 > stop.序号 and ticket.车次 in (select 车次 from stop where getdate='1945/8/8' and TN.车次 in
(select a.车次
from stop a,stop b where a.车次=b.车次 and a.sname = '哈尔滨' and b.sname = '莫斯科' and a.序号>b.序号 )))
group by TN.车次,日期,类别,始发站,终点站,硬卧价格*t1.序号-硬卧价格*t2.序号,TN.硬卧数量)t3 ON t1.车次 = t3.车次 AND t1.日期 = t3.日期 AND t1.始发站 = t3.始发站 AND t1.终点 = t3.终点
DECLARE @t1 TABLE (
Num VARCHAR(10),
price1 INT,
blance1 INT
)
DECLARE @t2 TABLE (
Num VARCHAR(10),
price2 INT,
blance2 INT
)
DECLARE @t3 TABLE (
Num VARCHAR(10),
price3 INT,
blance3 INT
)
INSERT INTO @t1 VALUES ('k-25',300,98)
INSERT INTO @t1 VALUES ('t-26',15,27)
INSERT INTO @t2 VALUES ('k-25',75,27)
INSERT INTO @t2 VALUES ('t-26',10,19)
INSERT INTO @t3 VALUES ('k-25',60,29)
INSERT INTO @t3 VALUES ('t-26',5,9)
------ 以上为测试数据 -----------
------ 把你的查询出来的 3 个替换为 cte 中的内容 -------
;WITH cte1 AS (
SELECT * FROM @t1
)
,cte2 AS (
SELECT * FROM @t2
)
,cte3 AS (
SELECT * FROM @t3
)
SELECT
cte1.num AS [车次]
,cte1.price1 [硬座价格]
,cte1.blance1 [硬座余量]
,cte2.price2 [软卧价格]
,cte2.blance2 [软卧余量]
,cte3.price3 [硬卧价格]
,cte3.blance3 [硬卧余量]
FROM cte1
INNER JOIN cte2 ON cte1.num=cte2.num
INNER JOIN cte3 ON cte1.num=cte3.num