left join 优化
来源:互联网 发布:上传数据 英文 编辑:程序博客网 时间:2024/05/27 14:13
原来的:
SELECT
M.clientid,
M.CardFaceID,
N.NormalBanalce,
D.DateWorth,
T.TimesWorth,
B.BookingWorth,
B.BookingTimesManyBalance
FROM
(
SELECT
clientid,CardFaceID
FROM
cimain
) M
Left Join
(SELECT
clientid,
sum( case when IfGive='是' then Balance * ItemZkl else Balance end) as NormalBanalce
FROM ccNormal
Group By clientid ) N on M.clientid=N.clientid
Left Join
(SELECT
clientid,
sum( ConsumeBalance * ItemZkl ) as DateWorth
FROM ccDate
Group By clientid ) D on M.clientid=D.clientid
Left Join
(SELECT
clientid,
sum( AveragePrice * TimesBalance * ItemZKL ) as TimesWorth
FROM ccTimes
Group By clientid ) T on M.clientid=T.clientid
Left Join
(SELECT
clientid,
sum( PriceDiscount * TimesBalance ) as BookingWorth,
sum(TimesBalance) as BookingTimesManyBalance
FROM ccBooking
Group By clientid ) B on M.clientid=B.clientid
优化后:
SELECT
M.clientid ,
M.CardFaceID,
(SELECT sum(case IfGive when '是' then Balance*ItemZkl else Balance end) FROM ccNormal WHERE clientid=M.clientid) AS NormalBanalce,
(SELECT sum(ConsumeBalance*ItemZkl) FROM ccDate WHERE clientid=M.clientid) AS DateWorth,
(SELECT sum(AveragePrice*TimesBalance*ItemZKL) FROM ccTimes WHERE clientid=M.clientid) AS TimesWorth,
(SELECT sum(PriceDiscount*TimesBalance) FROM ccBooking WHERE clientid=M.clientid) AS BookingWorth,
(SELECT sum(TimesBalance) FROM ccBooking WHERE clientid=M.clientid) AS BookingTimesManyBalance
FROM
cimain M
- left join 优化
- left.join.查询优化
- Left.join优化规则
- oracle left join优化
- left join查询优化
- MySQL left join优化
- mysql :left join语句优化
- mysql 如何优化left join
- SQL join,left join ,right join , inner join , outer join用法解析及HIVE join 优化
- Left join优化规则的研究
- Left.join优化规则的研究
- MYSQL left join 优化与解决办法
- MySQL - MySQL如何优化LEFT JOIN和RIGHT JOIN
- SQL查询优化 LEFT JOIN和INNER JOIN
- SQL查询优化 LEFT JOIN和INNER JOIN
- mysql left join right join区别以及优化收集
- Left Join
- Left Join
- Struts中Jsp中文乱码解决方案
- 如何提高Linux系统安全性的十大招数
- elipse retarget="true"
- linux指令大全
- Eclipse中OFBiz实体引擎剥离机制
- left join 优化
- oracle数据库论坛
- java多线程设计模式
- 一些不错的UNIX学习论坛
- ECS 功能说明
- 今天学习C#遇到的几个问题
- 调试技术
- lwIP――TCP/IP协议栈的一种实现(1)
- 链接错误"unresolved external symbol _main"的解决