死锁的日子不好过

[稻草_木偶]

很奇怪问题，这条语句如果把case when 去掉或者改为其他列名就会死锁

[稻草_木偶]

SELECT cast(convert(char(8),snb_jrrq,112)as datetime) as snb_jrrq,
snb_fcrq=(SELECT MAX(SNL_FSRQ) FROM SPSNLIST AS S WITH (NOLOCK) WHERE SNL_SN=A.SN and a.snb_wpzt='在途'),
dwz_dwmc,jgz_nbjc,(select lbz_lbmc=case when lbz_lbmc='电脑3C数码' THEN 'GSM手机' ELSE lbz_lbmc end from dbo.splb WITH (NOLOCK) where left(spk_sptm,2)=lbz_lbbh) as spdl,spk_pinp,lbz_lbmc,spk_sptm,(spk_spmc+spk_spsx) spmc,sn,
snl_ywjg=isnull(snl_ywjg,0),snb_wpzt,rkd=(select max(snl_djbh) from spsnlist WITH (NOLOCK) where snl_ywlx='采购入库'and snl_sn=sn),spz_spbh
into #spsnjl FROM SPSNJL as a WITH (NOLOCK) 
left join jgzl WITH (NOLOCK)  on jgzl.id=snb_jgid 
left join spkz WITH (NOLOCK)  on spkz.id=snb_spid 
left join spzl WITH (NOLOCK)  on spzl.id=spk_zlid 
left join splb WITH (NOLOCK)  on splb.id=spzl.spz_splb
left join DWZL  WITH (NOLOCK) on dwzl.ID=snb_gycs 
left join spsnlist WITH (NOLOCK) on snl_sn=sn and spsnlist.id in(select max(id)  from spsnlist WITH (NOLOCK) where snl_ywlx='采购入库' group by snl_sn )
where snb_wpzt in('在库','在途') 

select snb_jrrq,snb_fcrq,dwz_dwmc,jgz_nbjc,spdl,spk_pinp,spk_sptm,spmc,sn,snl_ywjg=(case when spk_sptm like '18%' then  0 else snl_ywjg end),snb_wpzt,pjj=(case when spmc like '%备用机%' or spmc like '%演示机%' or spk_sptm like '18%' then  0 else pjj end),sl=1,rkd=(case when rkd is null then (select max(snl_djbh) from spsnlist WITH (NOLOCK) where snl_ywlx='换机换入' and snl_sn=sn) else rkd end),spz_spbh from #spsnjl a
left join (select Convert(decimal(10,2),avg(snl_ywjg))pjj,lbz_lbmc from #spsnjl where spk_sptm like '01%' and spmc not like '%备用机%' and spmc not like '%演示机%' group by lbz_lbmc)b 
on  a.lbz_lbmc=b.lbz_lbmc

order by spk_sptm

drop table #spsnjl

查询语句

[稻草_木偶]

[稻草_木偶]

引用 4 楼 shinger126 的回复:

(select lbz_lbmc=case when lbz_lbmc='电脑3C数码' THEN 'GSM手机' ELSE lbz_lbmc end from dbo.splb WITH (NOLOCK) where left(spk_sptm,2)=lbz_lbbh) as spdl
把lbz_lbmc=去掉试试，不过你这SQL写的，反正基本上我看见一句里面有多个子查询就会忍不住开喷，这样要么是SQL写的烂，要么是设计烂

不行的，去掉就死锁了

[稻草_木偶]

引用 5 楼 yenange 的回复:

先确定谁跟谁死锁： https://blog.csdn.net/yenange/article/details/73883915

另外， 你SQL Server 的版本是多少？

SELECT @@VERSION;

之前你帮我解答过问题的，sql sever 2000，语句本身死锁

[稻草_木偶]

引用 3 楼 sinat_28984567 的回复:

SELECT  request_session_id spid ,
        OBJECT_NAME(resource_associated_entity_id) tableName
FROM    sys.dm_tran_locks
WHERE   resource_type = 'OBJECT ' 

查一下哪个表死锁

2000 的没这个表

[吉普赛的歌]

--1 先把这个表的信息提取出来
SELECT lbz_lbmc = CASE 
		WHEN lbz_lbmc = '电脑3C数码' THEN 'GSM手机'
		ELSE lbz_lbmc
	END,
LEFT(spk_sptm, 2) AS spk_sptm_LEFT2
INTO #tmp2
FROM   dbo.splb WITH (NOLOCK)
--2
SELECT cast(convert(char(8),snb_jrrq,112)as datetime) as snb_jrrq,
snb_fcrq=(SELECT MAX(SNL_FSRQ) FROM SPSNLIST AS S WITH (NOLOCK) WHERE SNL_SN=A.SN and a.snb_wpzt='在途'),
dwz_dwmc,jgz_nbjc
,(select lbz_lbmc=case when lbz_lbmc='电脑3C数码' THEN 'GSM手机' ELSE lbz_lbmc end from dbo.splb WITH (NOLOCK) where left(spk_sptm,2)=lbz_lbbh) as spdl
,spk_pinp,lbz_lbmc,spk_sptm,(spk_spmc+spk_spsx) spmc,sn,
snl_ywjg=isnull(snl_ywjg,0),snb_wpzt,rkd=(select max(snl_djbh) from spsnlist WITH (NOLOCK) where snl_ywlx='采购入库'and snl_sn=sn),spz_spbh
into #spsnjl FROM SPSNJL as a WITH (NOLOCK) 
left join jgzl WITH (NOLOCK)  on jgzl.id=snb_jgid 
left join spkz WITH (NOLOCK)  on spkz.id=snb_spid 
left join spzl WITH (NOLOCK)  on spzl.id=spk_zlid 
left join splb WITH (NOLOCK)  on splb.id=spzl.spz_splb
left join DWZL  WITH (NOLOCK) on dwzl.ID=snb_gycs 
left join spsnlist WITH (NOLOCK) on snl_sn=sn and spsnlist.id in(select max(id)  from spsnlist WITH (NOLOCK) where snl_ywlx='采购入库' group by snl_sn )
LEFT JOIN #tmp2 AS tmp2 ON tmp2.spk_sptm_LEFT2=xxxx.lbz_lbbh --xxxx你自己改一下，我不知道lbz_lbbh对应哪个表
where snb_wpzt in('在库','在途') 
--3
select snb_jrrq,snb_fcrq,dwz_dwmc,jgz_nbjc,spdl,spk_pinp,spk_sptm,spmc,sn,snl_ywjg=(case when spk_sptm like '18%' then  0 else snl_ywjg end),snb_wpzt,pjj=(case when spmc like '%备用机%' or spmc like '%演示机%' or spk_sptm like '18%' then  0 else pjj end),sl=1,rkd=(case when rkd is null then (select max(snl_djbh) from spsnlist WITH (NOLOCK) where snl_ywlx='换机换入' and snl_sn=sn) else rkd end),spz_spbh from #spsnjl a
left join (select Convert(decimal(10,2),avg(snl_ywjg))pjj,lbz_lbmc from #spsnjl where spk_sptm like '01%' and spmc not like '%备用机%' and spmc not like '%演示机%' group by lbz_lbmc)b 
on  a.lbz_lbmc=b.lbz_lbmc
order by spk_sptm
--4
DROP TABLE #tmp2
drop table #spsnjl

死马当活马医， 试下吧

[二月十六]

SELECT  request_session_id spid ,
        OBJECT_NAME(resource_associated_entity_id) tableName
FROM    sys.dm_tran_locks
WHERE   resource_type = 'OBJECT ' 

查一下哪个表死锁

[shinger126]

(select lbz_lbmc=case when lbz_lbmc='电脑3C数码' THEN 'GSM手机' ELSE lbz_lbmc end from dbo.splb WITH (NOLOCK) where left(spk_sptm,2)=lbz_lbbh) as spdl
把lbz_lbmc=去掉试试，不过你这SQL写的，反正基本上我看见一句里面有多个子查询就会忍不住开喷，这样要么是SQL写的烂，要么是设计烂

[吉普赛的歌]

先确定谁跟谁死锁： https://blog.csdn.net/yenange/article/details/73883915

另外， 你SQL Server 的版本是多少？

SELECT @@VERSION;