HOWTO：包括不属于SQL SERVER中的聚合函数或Group by子句的列

I have below recursive CTE:

我有下面的递归CTE:

DECLARE @T AS TABLE
(
    PARENT_TEST_ID int,
    TEST_ID  int,
    VALIDATED int,
    ERR int
)

INSERT INTO @T VALUES
(NULL, 1, 0, 0),
(NULL, 2, 0, 0),
(1,3,0, 0),
(1,4,0, 0),
(2,5,0, 0),
(2,6,0, 0),
(2,7,0, 0),
(7,8,0, 1)

;with C as
(
  select TEST_ID, PARENT_TEST_ID, (CASE WHEN ERR=1 THEN 0 ELSE 1 END) AS VALIDATED, ERR
  from @T
  where TEST_ID not in (select PARENT_TEST_ID 
                   from @T 
                   where PARENT_TEST_ID is not null) AND PARENT_TEST_ID IS NOT NULL
  union all
  select 
  T.TEST_ID, 
  T.PARENT_TEST_ID, 
  (case when t.TEST_ID=c.PARENT_TEST_ID and c.VALIDATED=1 AND T.ERR=0 THEN 1 ELSE 0 END) as VALIDATED,
  T.ERR
  from @T as T
    inner join C
      on T.TEST_ID = C.PARENT_TEST_ID 
)
SELECT DISTINCT PARENT_TEST_ID, TEST_ID, MIN(VALIDATED) FROM C
GROUP BY TEST_ID

But I cannot include PARENT_TEST_ID column in the result SELECT as it is not part of the group by clause, so I have found this link:

但我不能在结果SELECT中包含PARENT_TEST_ID列,因为它不是group by子句的一部分,所以我找到了这个链接:

Including column that is not part of the group by

包括不属于该组的列

So now I am trying to do the same in my case, I am trying to apply John Woo solution but I do not know how. Any help? Or any other best solution?

所以现在我试图在我的情况下做同样的事情,我正在尝试应用John Woo解决方案,但我不知道如何。有帮助吗?或任何其他最佳解决方案?

2 个解决方案

#1

iamdave is right, but if you want to implement John Woo's solution from that linked answer, it would look like this:

iamdave是对的,但是如果你想从这个链接的答案中实现John Woo的解决方案,它看起来像这样:

rextester: http://rextester.com/QQQGM79701

;with C as (
  select 
     test_id
   , parent_test_id
   , validated=(case when err = 1 then 0 else 1 end) 
   , err
  from @T as t
  where t.test_id not in (
    select i.parent_test_id
    from @T as i
    where i.parent_test_id is not null
    )
   and t.parent_test_id is not null
  union all
  select 
     t.test_id
   , t.parent_test_id
   , validated = case 
      when t.test_id = c.parent_test_id
        and c.validated = 1
        and t.err = 0 
      then 1 
      else 0 
      end
    , t.err
  from @T as T
    inner join c on t.test_id = c.parent_test_id
)
, r as (
  select 
     parent_test_id
   , test_id
   , Validated
   , rn = row_number() over (
        partition by test_id 
        order by Validated
    )
  from C
)
select 
     parent_test_id
   , test_id
   , Validated
  from r 
  where rn=1

#2

Just change your last line to GROUP BY PARENT_TEST_ID, TEST_ID

只需将最后一行更改为GROUP BY PARENT_TEST_ID,TEST_ID即可

The error you are getting is telling you that you can't add columns to the output if you don't either aggregate on it or group your other aggregates by it. By adding the column to the group by, you are telling SQL Server that you want to do your min by both the parent and the test ID values.

您收到的错误告诉您,如果您不在其上聚合或按其分组其他聚合,则无法将列添加到输出中。通过将列添加到组中,您告诉SQL Server您希望通过父ID和测试ID值执行min。

rextester: http://rextester.com/JRF55398

#1