case when 是不是不能利用索引的?

[mywisdom88]

-- 表结构(iNo int, cName varchar(20), iLock bit, dLockTime datetime, cLockUser varchar(10) )
-- 下面查询,要建立iLock,就好像可以利用索引
-- CREATE INDEX [f_test_iLock] ON [dbo].[f_test]([iLock]) ON [PRIMARY]

declare @cLockUser varchar(20)
set @cLockUser = 'C001'

SELECT [iNo], [cName], [iLock], [dLockTime], [cLockUser] FROM [test].[dbo].[f_test]
where iLock = 0
union all --超时锁的
SELECT [iNo], [cName], [iLock], [dLockTime], [cLockUser] FROM [test].[dbo].[f_test]
where iLock = 1 and datediff(mi,dLockTime,getdate()) > 5
union all --自己锁的
SELECT [iNo], [cName], [iLock], [dLockTime], [cLockUser] FROM [test].[dbo].[f_test]
where iLock = 1 and cLockUser = @cLockUser
union all --没时间的
SELECT [iNo], [cName], [iLock], [dLockTime], [cLockUser] FROM [test].[dbo].[f_test]
where iLock = 1 and dLockTime is null

-- 但改为带 case when的就不能用索引了
declare @cLockUser varchar(20)
set @cLockUser = 'C001'

SELECT [iNo], [cName], [iLock], [dLockTime], [cLockUser],
case when iLock =0 then '闲'
else case when iLock = 1 and cLockUser = @cLockUser then '闲'
else case when iLock = 1 and dLockTime is null then '闲'
else case when iLock = 1 and datediff(mi,dLockTime,getdate()) > 5 then '闲' else '锁' end end end end
FROM [test].[dbo].[f_test]

-- 哪位大神,怎么做才能利用索引呢

[卖水果的net]

FROM [test].[dbo].[f_test] 查询了整张表的数据，不会用到索引的

[mywisdom88]

引用 4 楼 wmxcn2000 的回复:

FROM [test].[dbo].[f_test] 查询了整张表的数据，不会用到索引的

那就上面1楼的问题,怎么做,才效率最高... 其中1个就是 dLockTime < DATEADD(mi, -5, GETDATE()) ,函数不在字段中,

[mywisdom88]

加索引后 create index ix_f_test_dLockTime_iLock on f_test( dLockTime, iLock ); DATEADD(mi, -5, GETDATE())

[mywisdom88]

建立3个索引 --create index ix_f_test_iLock on f_test( iLock ) --create index ix_f_test_dLockTime_iLock on f_test( dLockTime, iLock ) --create index ix_f_test_cLockUser_iLock on f_test( cLockUser, iLock ) --好像只查没锁的,不用CASE,好像最优化了?

[吉普赛的歌]

查询1和查询2结果都不一样。 查询1没有查询2 中 ELSE ‘锁’ 这一个分支的结果。

[mywisdom88]

我现在才发现,你的CASE CASE WHEN iLock = 0 THEN '闲' WHEN iLock = 1 AND cLockUser = @cLockUser THEN '闲' WHEN iLock = 1 AND dLockTime IS NULL THEN '闲' WHEN iLock = 1 AND dLockTime < DATEADD(mi, -5, GETDATE()) THEN '闲' ELSE '锁' END 我以前只知道, case when then else case ... end end 还可以省略中间的 case 的?

[吉普赛的歌]

你在原来学习的时候， 可能没有注意这个方面， 最原始的就是这样的——每个 when 都是平等关系。 你多个 else ， 相当于下一级关系了。 不过在你这个问题上， 两者的逻辑是相同的， 没有必要写到你这么复杂。

[吉普赛的歌]

iLock 总共有几种值？

[mywisdom88]

iLock bit,0和1

[吉普赛的歌]

引用 2 楼 mywisdom88 的回复:

iLock bit,0和1

这种选择性不高的列， 建立了索引效率也不高， 没必要折腾了。

--先加一个索引：
create index ix_f_test_dLockTime_iLock  on f_test( dLockTime, iLock );

再执行下面的SQL， 贴一下结果， 不需要搞什么执行计划， 贴消息出来看下就可以了

SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @cLockUser VARCHAR(20)
SET @cLockUser = 'C001'

SELECT [iNo],
       [cName],
       [iLock],
       [dLockTime],
       [cLockUser]
FROM   [test].[dbo].[f_test]
WHERE  iLock = 0
 union ALL --超时锁的
SELECT [iNo],
       [cName],
       [iLock],
       [dLockTime],
       [cLockUser]
FROM   [test].[dbo].[f_test]
WHERE  iLock = 1
	   --这里有修改，datediff 换 dateadd
       AND dLockTime < DATEADD(mi, -5, GETDATE())
 union ALL --自己锁的
SELECT [iNo],
       [cName],
       [iLock],
       [dLockTime],
       [cLockUser]
FROM   [test].[dbo].[f_test]
WHERE  iLock = 1
       AND cLockUser = @cLockUser
 union ALL --没时间的
SELECT [iNo],
       [cName],
       [iLock],
       [dLockTime],
       [cLockUser]
FROM   [test].[dbo].[f_test]
WHERE  iLock = 1
       AND dLockTime IS NULL
 

SELECT [iNo],
       [cName],
       [iLock],
       [dLockTime],
       [cLockUser],
       CASE WHEN iLock = 0 THEN '闲'
		   WHEN iLock = 1 AND cLockUser = @cLockUser THEN '闲'
		   WHEN iLock = 1 AND dLockTime IS NULL THEN '闲'
		   --这里有修改，datediff 换 dateadd
		   WHEN iLock = 1 AND dLockTime < DATEADD(mi, -5, GETDATE()) THEN '闲'
	   ELSE '锁' END
FROM   [test].[dbo].[f_test]