如何 查询到考评统计表里 差评（0）的个数介于3~~4 的所有记录?

在考评统计表里，“0”代表为“差评”
如何 查询到考评统计表里 每一行 差评（0）的个数介于3~~4 的所有记录?

--  考评统计表  
SELECT '1' ID,'1 1 1 1 1 1 2 1 1 0'N10RpNr
UNION ALL SELECT '2','1 2 1 1 1 1 1 1 1 0'
UNION ALL SELECT '3','1 3 0 0 1 1 1 2 1 0'
UNION ALL SELECT '4','1 3 0 1 1 1 1 1 1 0'
UNION ALL SELECT '5','1 3 0 1 1 1 1 1 1 0'
UNION ALL SELECT '6','1 2 0 1 1 1 2 1 1 0'
UNION ALL SELECT '7','2 2 0 0 1 1 1 2 1 0'
UNION ALL SELECT '8','2 2 0 0 1 1 0 2 2 0'

--  查询语句

DROP TABLE IF  EXISTS  #DQhc;

;WITH YY77 AS(
SELECT '1' ID,'1 1 1 1 1 1 2 1 1 0'N10RpNr
UNION ALL SELECT '2','1 2 1 1 1 1 1 1 1 0'
UNION ALL SELECT '3','1 3 0 0 1 1 1 2 1 0'
UNION ALL SELECT '4','1 3 0 1 1 1 1 1 1 0'
UNION ALL SELECT '5','1 3 0 1 1 1 1 1 1 0'
UNION ALL SELECT '6','1 2 0 1 1 1 2 1 1 0'
UNION ALL SELECT '7','2 2 0 0 1 1 1 2 1 0'
UNION ALL SELECT '8','2 2 0 0 1 1 0 2 2 0'
)
,YY88 AS(
SELECT *,
Cast(SUBSTRING(N10RpNr,1,1) AS INT)as K1, 
Cast(SUBSTRING(N10RpNr,3,1) AS INT)as K2,  
Cast(SUBSTRING(N10RpNr,5,1) AS INT)as K3,  
Cast(SUBSTRING(N10RpNr,7,1) AS INT)as K4,  
Cast(SUBSTRING(N10RpNr,9,1) AS INT)as K5,  
Cast(SUBSTRING(N10RpNr,11,1) AS INT)as K6,  
Cast(SUBSTRING(N10RpNr,13,1) AS INT)as K7,  
Cast(SUBSTRING(N10RpNr,15,1) AS INT)as K8,  
Cast(SUBSTRING(N10RpNr,17,1) AS INT)as K9,
Cast(SUBSTRING(N10RpNr,19,1) AS INT)as K10
FROM YY77
)
,KaoPIN AS (
SELECT '0' QTY 
)
 
SELECT *

,((Case when exists (Select QTY from KaoPIN where QTY =t.K1 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K2 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K3 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K4 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K5 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K6 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K7 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K8 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K9 ) then 1 else 0 end)
+ (Case when exists (Select QTY from KaoPIN where QTY =t.K10 ) then 1 else 0 end)) as D0_RpNr 

INTO #DQhc

FROM  YY88 T;
--  SELECT MAX(D0_RpNr),MIN(D0_RpNr)  FROM #DQhc ;

 SELECT * FROM #DQhc WHERE D0_RpNr BETWEEN 3 AND 4 ;

问题: 如何 用更好的方法， 查询到考评统计表里 每一行 N10RpNr 里的 0 的个数？

你可以使用SQL的内置函数CHARINDEX来统计每一行中0的个数。以下是修改后的代码示例：

SELECT ID, N10RpNr, 
    LEN(N10RpNr) - LEN(REPLACE(N10RpNr, '0', '')) AS ZeroCount
FROM YY77
WHERE LEN(N10RpNr) - LEN(REPLACE(N10RpNr, '0', '')) BETWEEN 3 AND 4;

上述代码使用LEN函数计算字符串的长度，然后使用REPLACE函数将所有的'0'替换为空字符串，并计算替换前后长度的差值，即为0的个数。然后通过WHERE子句过滤出0的个数在3和4之间的记录。

这样，你就可以查询到考评统计表中每一行N10RpNr中0的个数，并且筛选出0的个数在3和4之间的记录。

可以使用内置函数LEN和REPLACE来计算每一行中0的个数。

SELECT *,
       (LEN(N10RpNr) - LEN(REPLACE(N10RpNr, '0', ''))) AS ZeroCount
FROM YY77
WHERE ZeroCount BETWEEN 3 AND 4;