利用case when 减少表扫描次数
2024-10-18 21:16:11
数据库环境:SQL SERVER 2008R2
有网友希望有人帮他优化一下他的SQL,SQL语句如下:
WITH T AS (
SELECT B.O_Money MON,B.O_States STATES FROM M_Basket A
JOIN M_OrderInfo B ON A.OrderID=B.ID WHERE A.GoodID=@GOODSID
),
B AS (
SELECT (SELECT SUM(MON) FROM T)SumMoney,
(SELECT SUM(MON) FROM T WHERE STATES IN (2,3,4))ComfirmMoney,
(SELECT COUNT(*) FROM T WHERE STATES=2)AleadyDrive,
(SELECT COUNT(*) FROM T WHERE STATES=3)AleadyPay,
(SELECT COUNT(*) FROM T WHERE STATES=4)AleadyComfirm
)
INSERT @BIAO
SELECT * FROM B
看了一下语句,有可能出问题的地方,是with B 里面的内容,T表被访问了4次,
稍微转换一下思路,用case when来改写这段,就可以让T表只访问1次。
改写的SQL如下:
WITH T AS ( SELECT B.O_Money MON ,
B.O_States STATES
FROM M_Basket A
JOIN M_OrderInfo B ON A.OrderID = B.ID
WHERE A.GoodID = @GOODSID
),
B AS ( SELECT SUM(MON) AS SumMoney ,
SUM(CASE WHEN STATES IN ( 2, 3, 4 ) THEN MON
END) AS ComfirmMoney ,
COUNT(CASE WHEN STATES = 2 THEN 1
END) AS AleadyDrive ,
COUNT(CASE WHEN STATES = 3 THEN 1
END) AS AleadyPay ,
COUNT(CASE WHEN STATES = 4 THEN 1
END) AS AleadyComfirm
FROM T
)
INSERT @BIAO
SELECT *
FROM B
(本文完)
最新文章
- 进程间通信--pipe
- 使用后台服务数据更新UI
- 使用CSS和jQuery实现对话框
- Part 14 Mathematical functions in sql server
- Hyper-V网络配置
- 【NetOffice Excel】Excel合并单元格【原】
- 父Prefab与子prefab问题
- 设置int、float型数据的输出格式
- 不错的JQuery屏幕居中提示信息封装,使用方便,可集成到项目
- Configuring WS-Security UsernameToken and WS-SecureConversation (Symmetric Connection Creation)
- 【linux之find及awk】
- SnackbarUtilDemo【Snackbar的封装类】
- st表(poj3264)
- 面试----你可以手写一个promise吗
- Java Socket基本使用
- 『TensorFlow』批处理类
- ReactJS实用技巧(2):从新人大坑——表单组件来看State
- es5.0 安装ik中文分词器 mac
- iOS 9: UIStackView入门
- 网卡虚拟化技术:VMDq和SR-IOV