示例数据如下，需要统计每10件为一个区间的次品率（一个CHECK表示意见，NG表示这件是次品）。目前我能想到的办法就是用触发器和临时表，但效率太低了。请教是否可以直接写SQL？
要求结果如下：
区间 检查数 次品数 次品率
1 10 2 0.2
2 12 1 0.08
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:01' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:35:21' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:35:51' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:01' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:36:21' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:36:59' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:38:19' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:39:56' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:39:44' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:40:33' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:41:22' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:42:11' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:43:23' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:44:10' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:45:11' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:46:56' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:47:46' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:48:34' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:49:26' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:50:19' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:51:06' AS dDateTime
UNION ALL
Select 'Check' AS 'Check','NG' AS NG,'2015-10-27 15:52:24' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:53:33' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:54:12' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:55:02' AS dDateTime
UNION ALL
Select 'Check' AS 'Check',NULL AS NG,'2015-10-27 15:56:11' AS dDateTime
------解决思路----------------------
額， 添加了条件了啊...
每次检查的数量区间都是变化的么？
从你给的测试数据中， 貌似找不出连续的 12条纪录， 只有一条纪录标记为 ‘NG’ 的額
;with tb02 as(
select
ROW_NUMBER() OVER(order by dDateTime desc) as tid,
case [NG] when 'NG' then 1 else 0 end as NG1,
[Check], NG, dDateTime
from tb01
)
--select * from tb02
select
1 as 区间, 10 as 检查数,
convert(decimal(4,2), 1.0*sum(NG1)/10) as [次品率]
from tb02 where tid <=10
union
select
2 as 区间, 12 as 检查数,
convert(decimal(4,2), 1.0*sum(NG1)/12) as [次品率]
from tb02 where tid > 10 and tid <= 22