求解如何将三个查询 结果合并在一个表中

qq_40135950 北方工业大学 2018-08-21 11:26:24
代码如下:
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.硬座数量



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.软卧数量


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.硬卧数量

查询结果如下:



...全文
146 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
二月十六 2018-08-22
结贴结贴
回复
qq_40135950 2018-08-22
引用 6 楼 sinat_28984567 的回复:
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.终点

谢谢,这次搞定了!
回复
二月十六 2018-08-22
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.终点
回复
qq_40135950 2018-08-22
引用 1 楼 RINK_1 的回复:
同车次不同类型座位的是分记录行显示的,那就三个数据集都加一个座位类型字段,然后直接union all。

如果同车次不同类型的座位是在同一行记录显示的,那就在以上union all后的结果集基础上,再用case when或者pivot来行转列。

同车次不同类型的座位是在同一行记录显示的,这样的代码怎么实现呢?
回复
qq_40135950 2018-08-22
引用 3 楼 sinat_28984567 的回复:
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.硬座数量

union all

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.软卧数量

union all

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.硬卧数量

这样就把本应该是软卧、硬卧的价格和余量强行合并到了硬座中了,不能让同车次的三者在同一行显示吗?
回复
RINK_1 2018-08-21
同车次不同类型座位的是分记录行显示的,那就三个数据集都加一个座位类型字段,然后直接union all。 如果同车次不同类型的座位是在同一行记录显示的,那就在以上union all后的结果集基础上,再用case when或者pivot来行转列。
回复
二月十六 2018-08-21
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.硬座数量

union all

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.软卧数量

union all

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.硬卧数量
回复
吉普赛的歌 2018-08-21
不想看你那么复杂的查询了。
根据结果组合一下还是很简单的:

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

回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2018-08-21 11:26
社区公告
暂无公告