sql复杂的子查询,横向合并结果集

第一个查询的结果集

select * from(
select c.msName,a.msId,c.msPrice, c.msPrice*COUNT(a.msId) as totalMoney,sum(a.msAmount) as totalCount    from BillConsume a right join 
 (select top 1 msTime as startTime,GETDATE() as endTime from BillConsume where DateDiff(dd,msTime,getdate())=0  order by msTime asc) b on a.msTime between b.startTime and b.endTime and a.msId!=0
left join Menus c on c.msId=a.msId   group by a.msId ,c.msName,c.msPrice)s

连接两个结果集的语句

 full join 

第二个查询的结果集

(select sum(t.totalMoney) as allTotalMoney   from (
 select c.msName,a.msId,c.msPrice, c.msPrice*COUNT(a.msId) as totalMoney,sum(a.msAmount) as totalCount  from BillConsume a right join 
 (select top 1 msTime as startTime,GETDATE() as endTime from BillConsume where DateDiff(dd,msTime,getdate())=0  order by msTime asc) b on a.msTime between b.startTime and b.endTime and a.msId!=0
left join Menus c on c.msId=a.msId   group by a.msId ,c.msName,c.msPrice)t)t

连表的条件

on 1=1

 

posted @ 2017-07-29 13:01  科技改变未来☆  阅读(...)  评论(... 编辑 收藏