sql临时表、转换字段、百分比（金桥总报表）

 

 

--select *  from @pt_tball

create proc Asset_QueryMainlistForReport
(
    @pi_Action varchar(100),
    @pi_PropertyCode varchar(100),
    @pi_pageIndex INT,
    @pi_pageSize INT,
    @po_recordCount BIGINT OUTPUT
)

as
    DECLARE @pt_StartRowIndex INT
    DECLARE @pt_EndRowIndex INT
 
 SET @pt_StartRowIndex = (@pi_pageIndex - 1) * @pi_pageSize + 1
 SET @pt_EndRowIndex = @pi_pageIndex * @pi_PageSize
 IF @pi_Action = 'getlist'
 BEGIN

--临时表的使用

--声明临时表
declare  @pt_tball table(totalshop varchar(100) ,shoparea varchar(100),rendshop varchar(100),rendshoparea varchar(100),unrendshop varchar(100),unrendshoparea varchar(100),unrendpercent varchar(100),rendpercent varchar(100),rendshopmoney varchar(100))

--插入临时表
insert into @pt_tball

--（查询出的现实所需的表）

--视图QueryMainList

--SELECT    dbo.Asset_ShopInfo.Square,dbo.Asset_ShopInfo.RentStatus,dbo.Asset_ShopInfo.ShopCode,dbo.Asset_Contract.RendStartDate,

dbo.Asset_Contract.TotalAmount,
                      dbo.Asset_ShopInfo.PropertyCode
--FROM         dbo.Asset_Contract INNER JOIN
                      dbo.Asset_ContractShopInfo ON dbo.Asset_Contract.ContractCode = dbo.Asset_ContractShopInfo.ContractCode RIGHT OUTER JOIN
                      dbo.Asset_ShopInfo ON dbo.Asset_ContractShopInfo.ShopCode = dbo.Asset_ShopInfo.ShopCode
--总商铺数量
select (select count() shopCodefrom QueryMainList) as totalshop,
--总商铺面积

转换字段，两种方法：

cast(num as int )

convert(int,num)
(select sum(cast (SquareNew as float)) as aa
from
(select 'SquareNew'=(
     case
     when ISNUMERIC ([Square])=0 then '0'
     when ISNUMERIC ([Square])>0 then [Square]
     end) from QueryMainList

)tb) as shoparea,
--总已租商铺数量
(select count(shopCode) from QueryMainList where RentStatus=2) as rendshop,
--总已租商铺面积
(select sum(cast (SquareNew as float)) as bb
from
(select 'SquareNew'=(
     case
     when ISNUMERIC ([Square])=0 then '0'
     when ISNUMERIC ([Square])>0 then [Square]
     end) from QueryMainList
where RentStatus=2
)tb) as rendshoparea,
--总未租商铺数量
(select count(shopCode) from QueryMainList where RentStatus=0) as unrendshop,
--总未租商铺面积
(select sum(convert(float,SquareNew)) as cc  from
(select 'SquareNew'=(
     case
     when ISNUMERIC ([Square])=0 then '0'
     when ISNUMERIC ([Square])>0 then [Square]
     end) from QueryMainList
where RentStatus=0
)tb) as unrendshoparea,
--已租商铺数量百分比

--求百分比

--cast(cast(分子 *1.0*100/cast(cast(分母 as decimal(10,2)) as varchar(50)) +'%')
(select cast(cast((select count(shopCode) from QueryMainList where RentStatus=2) *1.0*100/cast(cast((select count(shopCode(select count(shopCode) from QueryMainList) as decimal(10,2)) as varchar(50)) +'%') as unrendpercent,
--已租商铺面积百分比
(select cast(cast((select sum(cast (SquareNew as float)) as bb
from
(select 'SquareNew'=(
     case
     when ISNUMERIC ([Square])=0 then '0'
     when ISNUMERIC ([Square])>0 then [Square]
     end) from QueryMainList
where RentStatus=2
)tb) *1.0*100/(select sum(cast (SquareNew as float)) as aa
from
(select 'SquareNew'=(
     case
     when ISNUMERIC ([Square])=0 then '0'
     when ISNUMERIC ([Square])>0 then [Square]
     end) from QueryMainList

)tb) as decimal(10,2)) as varchar(50)) +'%') as rendpercent,
--视图中总金额重复（因为获取的是shopinfo表中的全部，所以对应的商铺后TotalAmount字段就有合同当中的总值）distinct重复列
sum (distinct TotalAmount) as rendshopmoney from QueryMainList
--租赁开始日期条件
where RendStartDate  between 
--转换得到的日期格式：111:2012/8/27
CONVERT(varchar(100),
--获取本年的第一天日期
DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) , 111
) and getdate()
SELECT * FROM
  (

 SELECT
   *,
   ROW_NUMBER() OVER (ORDER BY totalshop DESC) AS RowIndex
   FROM @pt_tball
  ) tba
  WHERE RowIndex BETWEEN @pt_StartRowIndex AND @pt_EndRowIndex
  

  
  --得到总数

   SELECT @po_recordCount = COUNT(*)
        FROM         @pt_tball
--where Asset_ShopInfo.propertycode = @pi_PropertyCode

 

      END