无法完成查询(运行时间过长)

Question

我正试图获得一些测试结果的总体通过率。

序列号代表一种仪器。一台仪器要通过的测试有107种。测试分为两部分：

1. Linux测试(21)
2. 其他(总共86，如电压测试，光测试)。

从两方面来看，文书将被视为通行证。

1. 标准方法:它在一个run.the代码中通过107个不同的测试，如下所示： 表组中的SELECt序列号按序列号，条目具有计数(不同的测试条件) >= 107
2. 另一种方式:它在一次运行中通过了107个不同的测试，或者在一轮中通过了86个其他测试，在以后的一轮中通过了21个Linux测试。 表中的"overalltestrunstatus“列只意味着一个仪器通过了列表中的所有测试，但是列表本身可能是不完整的。(比方说，如果一个仪器获得了“总体测试状态”，但是它通过的测试是< 107的，那么它仍然是一个失败)。有时，如果仪器失败或通过测试小于某一数字，它将被重新运行。因此，“条目”代表了这一工具所追求的那一轮。

我在这里的目的是，找到那些通过测试但不是以标准方式通过的仪器。

我的想法是找出所有那些具有“全面变体状态”等于通过，但通过测试少于107的仪器。然后迭代一个仪器的结果和每一个条目。如果它通过了86个其他测试，我将给它一个检查，如果通过其他21个Linux，我将给它另一个检查。稍后，我会数数所有有两张支票的仪器。

我想要的是：

+----------------+------------+---------------+------------+
|  serial number |   entries  | others tests  | Linux tests|
+----------------+------------+---------------+------------+
|  170119904     |     1      |       yes     |    no      |
+----------------+------------+---------------+------------+
|  170119904     |     2      |       no      |    yes     |
+----------------+------------+---------------+------------+
|  180117000     |     1      |       no      |    no      |
+----------------+------------+---------------+------------+
|  180117000     |     2      |       no      |    no      |
+----------------+------------+---------------+------------+

因此，通过检查某个特定序列号是否既通过了其他测试，也通过了Linux测试。我就能知道它通过与否。

我的问题是，每次查询运行大约20个小时，然后出现“断开连接”或“发生某些内部错误”之类的错误。我永远也无法通过它。有什么想法吗？我真的很感激你的帮助。

这张桌子是：

+----------------------+--------------+----------+----------------+------------+---------+
| overalltestrunstatus | serialnumber | passfail |  testcriteria  |  testname  | Entries |
+----------------------+--------------+----------+----------------+------------+---------+
| PASS                 |    170119904 | Pass     | Backlight test | Functional |       1 |
+----------------------+--------------+----------+----------------+------------+---------++----------------------+--------------+----------+----------------+------------+---------+
| PASS                 |    170119904 | Pass     | Linux set test | Functional |       1 |
+----------------------+--------------+----------+----------------+------------+---------++----------------------+--------------+----------+----------------+------------+---------+
| PASS                 |    170119904 | Pass     | Factor    test | Functional |       1 |
+----------------------+--------------+----------+----------------+------------+---------++----------------------+--------------+----------+----------------+------------+---------+
| PASS                 |    170119904 | Pass     | Voltage   test | Functional |       1 |
+----------------------+--------------+----------+----------------+------------+---------++----------------------+--------------+----------+----------------+------------+---------+
| PASS                 |    170119904 | Pass     | Digital   test | Functional |       1 |
+----------------------+--------------+----------+----------------+------------+---------++----------------------+--------------+----------+----------------+------------+---------+
| PASS                 |    170119904 | Pass     | Detactor  test | Functional |       1 |
+----------------------+--------------+----------+----------------+------------+---------+

这是我的密码：

/* -serialnumber |-- entries--|--Pass1 --|--Pass2---|
   --------------|------------|----------|----------|
*/
DECLARE @checklist TABLE
(
  serialnumber varchar(100), 
  entries int,
  passother int default  0,
  passlinux int default  0
)

DECLARE @temp TABLE
(
  serialnumber int, 
  entries int,
  passother int default  0,
  passlinux int default  0
)

-- select serial number into the table

-- PCBA 214 ge to be counted
INSERT INTO @checklist (serialnumber, entries)
    SELECT serialnumber, entries 
    FROM dbo.boardtestresults1_full_view  t2
    WHERE partnumber = 'PCBA-10214-0001' 
        AND t2.serialnumber IN 
        (SELECT DISTINCT serialnumber
        FROM dbo.boardtestresults1_full_view  
        WHERE partnumber = 'PCBA-10214-0001' AND overalltestrunstatus = 'Pass' AND serialnumber NOT IN (
        SELECT DISTINCT serialnumber
        FROM dbo.boardtestresults1_full_view  
        WHERE partnumber = 'PCBA-10214-0001' AND overalltestrunstatus = 'Pass'
        GROUP BY serialnumber, Entries 
        HAVING COUNT(DISTINCT combined) >= 107))



-- check all for PCBA 10214

DECLARE @MyCursor1 CURSOR;
DECLARE @snumber VARCHAR(100); -- serailnumber
DECLARE @entries VARCHAR(100); -- entries
DECLARE @others int;
DECLARE @linux int;
DECLARE @pass1 int;
DECLARE @pass2 int;

BEGIN
    -- get serial numbers for all PCBA 214 from checklist
    SET @MyCursor1 = CURSOR FOR
    SELECT serialnumber, Entries
    FROM @checklist
    WHERE serialnumber like 'PCBA-10214-0001%' 

    -- get the roll
    OPEN @MyCursor1 
    FETCH NEXT FROM @MyCursor1 
    INTO @snumber, @entries

    -- loop through
    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */
    -- if pass the most or if pass linux
    IF ((SELECT COUNT(DISTINCT combined)FROM dbo.boardtestresults1_full_view WHERE  serialnumber = @snumber AND entries = @entries) >= 86)
        UPDATE @checklist SET passother = 1 WHERE serialnumber = @snumber AND entries = @entries
    ELSE IF ((SELECT COUNT(DISTINCT combined)FROM dbo.boardtestresults1_full_view WHERE  serialnumber = @snumber AND entries = @entries AND testname = 'LINUX Test' AND overalltestrunstatus = 'Pass') >= 21) 
        UPDATE @checklist SET passlinux = 1 WHERE serialnumber = @snumber AND entries = @entries
    END; 

    CLOSE @MyCursor1 ; 
    DEALLOCATE @MyCursor1;
END;

Answer 1

下面是一个有用的示例，PassFail列不进行计算，而在那里它没有查询中的任何参数。为了简化示例，我添加了一个名为TestType的列，在您的用例中并不十分清楚如何从示例集(可能是TestCriteria )派生出Linux测试，但这使得它不那么含糊。您应该能够获得测试的二进制文件，如果您需要进一步的帮助，您将需要定义所有数据，我们可以提供帮助。

declare @example as table (
    ExampleID     int identity(1,1) not null primary key clustered
,   OverAllStatus nvarchar(255) not null
,   SN            int not null
,   PassFail      nvarchar(255) not null
,   TestType      bit not null
,   TestCriteria  nvarchar(255) not null
,   Entry_        int not null
);

insert into @example (OverAllStatus, SN, PassFail, TestType, TestCriteria, Entry_)
select 'PASS', 170119904, 'Pass', 0, 'Backlight test', 1 union all
select 'PASS', 170119904, 'Pass', 0, 'Factor test', 1 union all
select 'PASS', 170119904, 'Pass', 0, 'Voltage test', 1 union all
select 'PASS', 170119904, 'Pass', 0, 'Detactor test', 1 union all

select 'PASS', 170119904, 'Pass', 0, 'Backlight test', 2 union all
select 'PASS', 170119904, 'Pass', 1, 'Linux set test', 2 union all
select 'PASS', 170119904, 'Pass', 0, 'Factor test', 2 union all
select 'PASS', 170119904, 'Pass', 0, 'Voltage test', 2 union all
select 'PASS', 170119904, 'Fail', 0, 'Digital test', 2 union all
select 'PASS', 170119904, 'Pass', 0, 'Detactor test', 2 union all

select 'PASS', 180117000, 'Pass', 0, 'Backlight test', 1 union all
select 'PASS', 180117000, 'Pass', 1, 'Linux set test', 1 union all
select 'PASS', 180117000, 'Pass', 0, 'Factor test', 1 union all
select 'PASS', 180117000, 'Pass', 0, 'Digital test', 2 union all
select 'PASS', 180117000, 'Pass', 0, 'Detactor test', 1;


  --| Control Group, only those that pass but does not have 107 records (OverAllStatus)
  ;with cte as (
     select SN
          , Entry_
          , count(*) cnt
       from @example
      where OverAllStatus = 'Pass'
   group by SN, Entry_
   --| Control count - this is your 107 or the numbers of tests the SN has passed
     having count(*) < 7
            )
   --| We are setting the count of records to the Test Type or the grouping
    , cte2 as (
    select b.SN
         , a.Entry_
         , iif(TestType = 1, 'Linux', 'Other') Test
      from cte a
      join @example b
        on a.SN = b.SN
       and a.Entry_ = b.Entry_
            )
    --| Roll up the counts
    , cte3 as ( 
    select distinct a.SN
         , a.Entry_
         , count(*) over(partition by a.SN, a.Test, a.Entry_) cnt
         , a.Test
      from cte2 a
            )

  --| Pivot the result set
    select SN
         , Entry_
         , [Other]
         , [Linux]
      from (
                select SN
                     , Entry_
                     , cnt
                     , test
                  from cte3
                  ) as x
            pivot 
                (
                 max(cnt)
                 for Test in ([Other], [Linux])
                ) as p;

结果集：

SN          Entry_  Other   Linux
170119904   1       4       NULL
180117000   1       3       1
170119904   2       5       1
180117000   2       1       NULL