求解：sql server2005 子查询 问题！！！

Question

select replace(convert(varchar(7),midt_date,120),'-','') as midt_date,
max(case gds when 'WSP' then new_bill_bookings else 0 end)as new_bill_bookingsS,
max(case gds when 'WSP' then can_bill_bookings else 0 end)as can_bill_bookingsS,
max(case gds when 'WSP' then net_bill_bookings else 0 end)as net_bill_bookingsS
from midt as m LEFT join customer as t on m.sid = t.sid_1p where m.sid='EYA'
and midt_date between '2011-03-01' and '2011-03-01' group by midt_date
selectmax(case gds when 'ABA' then new_bill_bookings else 0 end)as new_bill_bookingsB,
max(case gds when 'ABA' then can_bill_bookings else 0 end)as can_bill_bookingsB,
max(case gds when 'ABA' then net_bill_bookings else 0 end)as net_bill_bookingsB
from midt as m LEFT join customer as t on m.sid = t.sid_1b
where t.sid_1b='4QUD'
and midt_date between '2011-03-01' and '2011-03-01'

这样的两条sql语句怎样可以组合成一个子查询？两条sql查询的结果是：

我的要求是查出来的结果为：

急，感谢！！！

Answer 1

两组结果没有关联条件吗？如果有就将两组查询的结果集作为派生表再关联
如果没有的话你要是想随意关联就把两结果集用row_number()函数关联
如果只有一个结果的话直接两结果集拼在一起就行了追问

他们两条sql语句其实是查询的同一个表里面的值，但是查询条件给的值不同。您觉得我这样的情况该怎样做呢？

追答

第二个查询输出列表多一个midt_date，然后已这个条件将两个结果集inner join

追问

不好意思，能写sql语句给我么？

追答

一粘贴语句百度就吞-_-，试试吧
刚仔细看了一下，拼成一个可以吧，你试试
select replace(convert(varchar(7),midt_date,120),'-','') as midt_date,
max(case when m.sid='EYA' and gds='WSP' then new_bill_bookings else 0 end)as new_bill_bookingsS,
max(case when m.sid='EYA' and gds='WSP' then can_bill_bookings else 0 end)as can_bill_bookingsS,
max(case when m.sid='EYA' and gds='WSP' then net_bill_bookings else 0 end)as net_bill_bookingsS,
max(case when t.sid_1b='4QUD' and gds='ABA' then new_bill_bookings else 0 end)as new_bill_bookingsB,
max(case when t.sid_1b='4QUD' and gds='ABA' then can_bill_bookings else 0 end)as can_bill_bookingsB,
max(case when t.sid_1b='4QUD' and gds='ABA' then net_bill_bookings else 0 end)as net_bill_bookingsB
from midt as m LEFT join customer as t on m.sid = t.sid_1p
where midt_date between '2011-03-01' and '2011-03-01'
group by midt_date

追问

貌似不行，按照您的sql语句查出来的结果中new_bill_bookingsB，can_bill_bookingsB，net_bill_bookingsB是没有值的。我在您写的sql语句中from后加了条件：
from midt as m LEFT join customer as t on m.sid = t.sid_1p or m.sid = t.sid_1b
这样就可以查出来了。

追答

理论上应该可以啊
select a.*,b.new_bill_bookingsB,b.can_bill_bookingsB,b.net_bill_bookingsB from
(select replace(convert(varchar(7),midt_date,120),'-','') as midt_date,
max(case gds when 'WSP' then new_bill_bookings else 0 end)as new_bill_bookingsS,
max(case gds when 'WSP' then can_bill_bookings else 0 end)as can_bill_bookingsS,
max(case gds when 'WSP' then net_bill_bookings else 0 end)as net_bill_bookingsS
from midt as m LEFT join customer as t on m.sid = t.sid_1p
where m.sid='EYA' and midt_date between '2011-03-01' and '2011-03-01'
group by midt_date)a
inner join
(select replace(convert(varchar(7),midt_date,120),'-','') as midt_date,
max(case gds when 'ABA' then new_bill_bookings else 0 end)as new_bill_bookingsB,
max(case gds when 'ABA' then can_bill_bookings else 0 end)as can_bill_bookingsB,
max(case gds when 'ABA' then net_bill_bookings else 0 end)as net_bill_bookingsB
from midt as m LEFT join customer as t on m.sid = t.sid_1b
where t.sid_1b='4QUD' and midt_date between '2011-03-01' and '2011-03-01'
group by midt_date)b
on a.midt_date=b.midt_date