临时表 存储过程解决复杂数据的查询

USE [BLT]
GO
/****** Object:  StoredProcedure [dbo].[getLiveCustomerAccount]    Script Date: 04/15/2013 09:24:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
获取入住酒店宾客消费明细账单信息
*/
ALTER proc [dbo].[getLiveCustomerAccount]
(@zdh varchar(13))
as

create  table #AccountTemp
(AccountId varchar(13),
moneyOfEating float,
moneyOfKtv float,
moneyOfBall float,
moneyOfSwim float,
moneyOfShower float,
other float
)

insert into #AccountTemp  values(@zdh,0,0,0,0,0,0);

declare @liveId varchar(13);
declare @point varchar(10);
declare @moneyOfBall float;
declare @moneyOfKtv float;
declare @moneyOfEating float;
declare @moneyOfSwim float;
declare @moneyOfShower float;
declare @moneyOfOther float;

select top 1 @liveId = t.ClientM_liveId  ,
@point = t.ClientM_point,
@moneyOfBall = sum(t.ClientM_money)

 from BLT.dbo.Client_mix t
where  t.ClientM_point='保龄球' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point

select top 1 @liveId = t.ClientM_liveId  ,
@point = t.ClientM_point,
@moneyOfKtv = sum(t.ClientM_money)
 from BLT.dbo.Client_mix t
where  t.ClientM_point='KTV' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point

select top 1 @liveId = t.ClientM_liveId  ,
@point = t.ClientM_point,
@moneyOfEating = sum(t.ClientM_money)
 from BLT.dbo.Client_mix t
where  t.ClientM_point='餐饮' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point

select top 1 @liveId = t.ClientM_liveId  ,
@point = t.ClientM_point,
@moneyOfSwim = sum(t.ClientM_money)
 from BLT.dbo.Client_mix t
where  t.ClientM_point='游泳' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point

select top 1 @liveId = t.ClientM_liveId  ,
@point = t.ClientM_point,
@moneyOfShower = sum(t.ClientM_money)
 from BLT.dbo.Client_mix t
where  t.ClientM_point='洗浴' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point

select top 1 @liveId = t.ClientM_liveId  ,
@point = t.ClientM_point,
@moneyOfOther = sum(t.ClientM_money)
 from BLT.dbo.Client_mix t
where  t.ClientM_point='其他' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point

update #AccountTemp
set AccountId = @liveId,
moneyOfBall = @moneyOfBall,
moneyOfEating = @moneyOfEating,
moneyOfKtv = @moneyOfKtv,
moneyOfSwim  = @moneyOfSwim,
moneyOfShower = @moneyOfShower,
other = @moneyOfOther
where AccountId = @zdh;

select * from #AccountTemp