为什么一换个查询值就查询不了，真奇怪了。

[zou_he]

SELECT FImei, FJG, FIcmoID, FCountif
FROM (SELECT DISTINCT A.FImei, A.FJG, A.FIcmoID, B.FCountif
FROM ICT AS A INNER JOIN
(SELECT FImei, COUNT(1) AS FCountif
FROM ICT
GROUP BY FImei) AS B ON A.FImei = B.FImei
WHERE (A.FJG = 'PASS') OR
(A.FJG = 'FAIL') AND (B.FCountif = 1)) AS c
WHERE (FIcmoID = 54)


FIMEI是产品序列号
FJG是测试结果
FICMOID是生产任务单内码

有80多个工单，其中查询FICMOID是54号和63号工单时，会出现正在执行查询，等待数据源的响应，最后报错，错误源:.net sqlclient data provider,超时时间已到，在操作完成之前超时或服务器未响应。

这80多个工单，一样的语句，为啥查54号和63号工单会出现这样，真是不解了。54号和63号工单还不是记录最多的，记录最多的工单有2万多条ICT测试记录，也基本上是1秒就查完，这54号和63号也就3千多个测试记录。

版主能否解答一下，谢谢。

[二月十六]

超时的时候检查一下阻塞和死锁情况是什么样的

[zou_he]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_who_lock
AS
BEGIN
DECLARE @spid INT ,
@bl INT ,
@intTransactionCountOnEntry INT ,
@intRowcount INT ,
@intCountProperties INT ,
@intCounter INT
CREATE TABLE #tmp_lock_who
(
id INT IDENTITY(1, 1) ,
spid SMALLINT ,
bl SMALLINT
)
IF @@ERROR <> 0
RETURN @@ERROR
INSERT INTO #tmp_lock_who ( spid, bl )
SELECT 0, blocked
FROM ( SELECT *
FROM sys.sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS ( SELECT *
FROM ( SELECT *
FROM sys.sysprocesses
WHERE blocked > 0
) b
WHERE a.blocked = spid )
UNION
SELECT spid, blocked
FROM sys.sysprocesses
WHERE blocked > 0
IF @@ERROR <> 0
RETURN @@ERROR
-- 找到临时表的记录数
SELECT @intCountProperties = COUNT(*), @intCounter = 1
FROM #tmp_lock_who
IF @@ERROR <> 0
RETURN @@ERROR
IF @intCountProperties = 0
SELECT N'现在没有阻塞和死锁信息' AS message
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid, @bl = bl
FROM #tmp_lock_who
WHERE Id = @intCounter
BEGIN
IF @spid = 0
SELECT N'引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))
+ N'进程号,其执行的SQL语法如下'
ELSE
SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10))
+ N'被进程号SPID：' + CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_lock_who
RETURN 0
END
go
EXEC sp_who_lock
DROP PROC sp_who_lock
GO
SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON

GO



反馈没有死锁和阻塞的信息。

[zou_he]

工单ID为54时无法查询

[zou_he]

工单ID为36时查询很快，不到一秒，并且36的记录比54的多几千条

[weixin_42334999]

把跑不出结果的sql的执行计划贴出来看一下。

[小大飞]

sql结构可以简化下。

[时光深存少年梦。]

你的数据呢？记得换库名试试。

[zou_he]

19楼是要看这个吗?


这个问题主要是怪在，同一个查询，同一个表，只是匹配值不同，就产生这样的不同查询差异。

[Dear SQL(燊)]

是不是数据分布不均，在SQL后面加上 option (RECOMPILE)

SELECT     FImei, FJG, FIcmoID, FCountif
FROM         (SELECT DISTINCT A.FImei, A.FJG, A.FIcmoID, B.FCountif
                       FROM          ICT AS A INNER JOIN
                                                  (SELECT     FImei, COUNT(1) AS FCountif
                                                    FROM          ICT
                                                    GROUP BY FImei) AS B ON A.FImei = B.FImei
                       WHERE      (A.FJG = 'PASS') OR
                                              (A.FJG = 'FAIL') AND (B.FCountif = 1)) AS c
WHERE     (FIcmoID = 54) option (RECOMPILE)

[吉普赛的歌]

--先添加索引，单独执行
CREATE INDEX ix_ICT_FImei_FIcmoID ON ICT(FImei,FIcmoID) INCLUDE(FJG)

--- 以下一并执行
--1.
SELECT FImei,COUNT(1) AS FCountif
INTO #tmp
FROM ICT WITH(NOLOCK)
GROUP BY FImei

--2.
CREATE CLUSTERED INDEX ix_#tmp ON #tmp(FImei)

--3.
SELECT DISTINCT A.FImei,
        A.FJG,
        A.FIcmoID,
        B.FCountif
FROM   ICT     AS A WITH(NOLOCK)
        INNER JOIN #tmp AS B
            ON  A.FImei = B.FImei AND A.FIcmoID = 54
WHERE  (A.FJG = 'PASS')
UNION
SELECT DISTINCT A.FImei,
        A.FJG,
        A.FIcmoID,
        B.FCountif
FROM   ICT     AS A WITH(NOLOCK)
        INNER JOIN #tmp AS B
            ON  A.FImei = B.FImei AND A.FIcmoID = 54
WHERE  (A.FJG = 'FAIL') AND (B.FCountif = 1)

--4. 删除临时表
DROP TABLE #tmp

[Dear SQL(燊)]

if OBJECT_ID('tempdb..#list') is not null drop table #list；

select DISTINCT A.FImei, A.FJG, A.FIcmoID,a.FJG
into #list
from ICT
where FIcmoID = 54

;
with coutlist as(
	select  FImei, COUNT(1) AS FCountif
	from ICT(nolock)
	where FImei in(select FImei from #list)
	group by FImei
)
select DISTINCT  FImei, FJG, FIcmoID, FCountif
from #list a
left join coutlist b on a.FImei=b.FImei
where (A.FJG = 'PASS') OR (A.FJG = 'FAIL') AND (B.FCountif = 1)

[zou_he]

谢谢吉普赛的歌，chenshanliang等热心朋友的解答，加option (RECOMPILE)没有用。
13楼的方法处理后，查询OK了。

昨天晚上我把表先备份，然后删除原表，更名备份表后：

SELECT * INTO ICTBAK FROM ICT
DROP TABLE ICT
EXEC sp_rename 'ictbak', 'ict'


以下查询OK,
SELECT FImei, FJG, FIcmoID, FCountif
FROM (SELECT DISTINCT A.FImei, A.FJG, A.FIcmoID, B.FCountif
FROM ICT AS A INNER JOIN
(SELECT FImei, COUNT(1) AS FCountif
FROM ICT
GROUP BY FImei) AS B ON A.FImei = B.FImei
WHERE (A.FJG = 'PASS') OR
(A.FJG = 'FAIL') AND (B.FCountif = 1)) AS c
WHERE (FIcmoID = 54)

但以下查询就不出结果。
SELECT FIMEI, FICMOID, FPASSQTY, FFAILQTY,
CASE WHEN a.FPASSQTY > 0 THEN 'PASS' WHEN a.fpassqty + a.ffailqty = 0 THEN 'NOTEST' ELSE 'FAIL' END AS FTESTJG
FROM (SELECT FIMEI, FICMOID,
(SELECT COUNT(1) AS Expr1
FROM ICT AS ict_1
WHERE (FJG = 'PASS') AND (FImei = diptb.FIMEI)) AS FPASSQTY,
(SELECT COUNT(1) AS Expr1
FROM ICT AS ICT_2
WHERE (FJG = 'FAIL') AND (FImei = diptb.FIMEI)) AS FFAILQTY
FROM diptb
WHERE (FICMOID = 54)) AS a
ORDER BY FIMEI

经过13楼的处理后，二种查询都OK，谢谢了。

[吉普赛的歌]

能用就好， 没事就结贴吧

[zou_he]

吉普赛的歌你好，索引我有偿试处理过的，但没能解决问题。由于SQL我不太熟悉，您能否解答一下这个问题的故障原因和解决思路？我想学习一下，谢谢。

[吉普赛的歌]

引用 17 楼 zou_he 的回复:

吉普赛的歌你好，索引我有偿试处理过的，但没能解决问题。由于SQL我不太熟悉，您能否解答一下这个问题的故障原因和解决思路？我想学习一下，谢谢。

连接字段、where 条件字段 需要建立索引， select 字段看情况， 不多的话可以 include 进来

[xiaoxiangqing]

是数据出不来，还是数据出来的不对？

[weixin_44148994]

是数据出不来，还是数据出来的不对？

[weixin_44148994]

是数据出不来，还是数据出来的不对？试试

[weixin_42334999]

SELECT a.FIMEI ,
FICMOID,
FPASSQTY= SUM(CASE  FJG   WHEN 'PASS' THEN 1 ELSE 0 END),
FFAILQTY= SUM(CASE  FJG   WHEN 'FAIL' THEN 1 ELSE 0 END)
  FROM diptb a,ICT b
where a.FImei=b.FImei
AND a.FICMOID=54
GROUP BY a.FIMEI ,
FICMOID

试试看吧。还有如果可以吧表结构以及目前存在的索引提供下 看看具体怎么调优 sql或者加hint

[zou_he]

引用 23 楼 xiaoxiangqing的回复:

是数据出不来，还是数据出来的不对？

是数据出不来，现在好了，谢谢各位热心人帮助解答。