怎么优化?
ALTER procedure sp_house_source
as
BEGIN
SELECT I_BUILD_INFOR.BUILD_ID ,
TOWER_INFOR.TOWER_NAME,I_BUILD_TYPE.BLDTP_NAME,
(SELECT COUNT(HOUSE_ID)
FROM HOUSE_INFOR
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID) as HOUSE_COUNT,
(SELECT SUM(TOTAL_PROPORSION)
FROM HOUSE_INFOR
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID) as PROPORSION,
(SELECT count(HOUSE_FLAG)
FROM HOUSE_INFOR
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID and HOUSE_FLAG >= 20 ) as SALES_COUNT,
(SELECT SUM(BARGAIN_AMOUNT)
FROM BARGAIN
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID) as SALES_AMOUNT,
(SELECT SUM(Bargain_Area)
FROM BARGAIN
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID)as SALES_PROPORSION,
(SELECT SUM(PAY_ACCOUNT)
FROM CUS_PAYMENT
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID)as PAYMENT
INTO #t
FROM I_BUILD_INFOR INNER JOIN
TOWER_INFOR ON
I_BUILD_INFOR.TOWER_ID = TOWER_INFOR.TOWER_ID INNER JOIN
I_BUILD_TYPE ON
I_BUILD_INFOR.BUILDTP_ID = I_BUILD_TYPE.BLDTP_ID
ORDER BY TOWER_NAME
SELECT COUNT(BUILD_ID) AS 楼数 , TOWER_NAME AS 区域 , BLDTP_NAME AS 类型,
SUM(HOUSE_COUNT) AS 房间总数 , SUM(SALES_COUNT) AS 销售套数,
CASE SUM(HOUSE_COUNT)
WHEN 0 THEN 0
ELSE (100* SUM(SALES_COUNT) / SUM(HOUSE_COUNT))
END AS 销售比例,
SUM(SALES_AMOUNT) AS 销售金额 ,SUM(SALES_PROPORSION) AS 销售面积,
CASE SUM(SALES_COUNT)
WHEN 0 THEN 0
ELSE SUM(SALES_AMOUNT) / SUM(SALES_COUNT)
END AS 销售均价,
SUM(PAYMENT) AS 销售回款
FROM #t
GROUP BY BLDTP_NAME ,TOWER_NAME
DROP TABLE #t
END
ALTER procedure sp_house_source
as
BEGIN
SELECT I_BUILD_INFOR.BUILD_ID ,
TOWER_INFOR.TOWER_NAME,I_BUILD_TYPE.BLDTP_NAME,
(SELECT COUNT(HOUSE_ID)
FROM HOUSE_INFOR
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID) as HOUSE_COUNT,
(SELECT SUM(TOTAL_PROPORSION)
FROM HOUSE_INFOR
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID) as PROPORSION,
(SELECT count(HOUSE_FLAG)
FROM HOUSE_INFOR
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID and HOUSE_FLAG >= 20 ) as SALES_COUNT,
(SELECT SUM(BARGAIN_AMOUNT)
FROM BARGAIN
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID) as SALES_AMOUNT,
(SELECT SUM(Bargain_Area)
FROM BARGAIN
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID)as SALES_PROPORSION,
(SELECT SUM(PAY_ACCOUNT)
FROM CUS_PAYMENT
WHERE LEFT(HOUSE_ID,4)= I_BUILD_INFOR.BUILD_ID)as PAYMENT
INTO #t
FROM I_BUILD_INFOR INNER JOIN
TOWER_INFOR ON
I_BUILD_INFOR.TOWER_ID = TOWER_INFOR.TOWER_ID INNER JOIN
I_BUILD_TYPE ON
I_BUILD_INFOR.BUILDTP_ID = I_BUILD_TYPE.BLDTP_ID
ORDER BY TOWER_NAME
SELECT COUNT(BUILD_ID) AS 楼数 , TOWER_NAME AS 区域 , BLDTP_NAME AS 类型,
SUM(HOUSE_COUNT) AS 房间总数 , SUM(SALES_COUNT) AS 销售套数,
CASE SUM(HOUSE_COUNT)
WHEN 0 THEN 0
ELSE (100* SUM(SALES_COUNT) / SUM(HOUSE_COUNT))
END AS 销售比例,
SUM(SALES_AMOUNT) AS 销售金额 ,SUM(SALES_PROPORSION) AS 销售面积,
CASE SUM(SALES_COUNT)
WHEN 0 THEN 0
ELSE SUM(SALES_AMOUNT) / SUM(SALES_COUNT)
END AS 销售均价,
SUM(PAYMENT) AS 销售回款
FROM #t
GROUP BY BLDTP_NAME ,TOWER_NAME
DROP TABLE #t
END
解决方案 »
- 【求助】SQL的时间比较问题
- |zyciis| 求一句SQL查询语句 谢谢
- 请教一个存储过程的写法。急
- 请问5年进销存、财务会计、生产等资料,如何备份数据库与删掉表里的数据?
- 如何查询数据库里的最后一条记录????在线等待
- 取每条数据的最后一条记录!!急!!!!!!!!!
- 紧急!如何用一条SQL语句同时更新两张表中关联记录的字段
- sqlserver触发器往mysql插入数据错误问题
- 请赐一SQL!
- 新手求教!老鸟请进来(关于SQL和ACCESS)
- 跨数据库查询将结果联接起来怎么做?
- 在存储过程中,执行分步查询时发生如下错误:错误7405:异类查询要求为连接设置ANSI_NULLS和ANSI_WARNINGS选项。这将确保一致的查询语义
你为什么要用临时表呢,好像用不着
ALTER procedure sp_house_source
as
set nocount on
BEGIN
.......
end
set nocount off