用于查找最小值的SQL查询，在该值之上，另一列中的所有值都满足某些条件

Say I have a table which contains two columns, an int and a boolean. I need to find the minimum value in the int column (a threshold) above which ALL values in the Boolean column are TRUE. How can I construct this query?

假设我有一个包含两列的表，一个是int和一个布尔值。我需要在int列（阈值）中找到最小值，超过该值，布尔列中的所有值都为TRUE。我该如何构造这个查询？

For example:

例如：

level  | detection
-----------------
5      | False
6      | True
7      | False
8      | True
9      | True
10     | True

Should return a level of 8 for this particular scenerio.

应为此特定场景返回8级。

2 个解决方案

#1

Try like this,

试试这样，

Declare @Table table (level int,detection varchar(25))
insert into @Table values
(5,'False')
,(6,'True')
,(7,'False')
,(8,'True')
,(9,'True')
,(10,'True')

SELECT min(LEVEL) AS Threshold
FROM @Table
WHERE LEVEL > (
        SELECT max(LEVEL)
        FROM @Table
        WHERE detection = 'False'
        )

#2

One possible way (online demo).

一种可能的方式（在线演示）。

WITH T
     AS (SELECT *,
                Prev = LEAD([detection]) OVER (ORDER BY [level] DESC),
                CumeRowCount = SUM(1) OVER (ORDER BY [level] DESC 
                                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
                CumeTrueCount = SUM(CASE WHEN [detection] = 'True' THEN 1 END) OVER 
                                           (ORDER BY [level] DESC 
                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         FROM   YourTable)
SELECT TOP 1 [level]
FROM   T
WHERE  CumeRowCount = CumeTrueCount /*All preceding and current rows must be true*/
       AND Prev <> [detection]      /* Next row not true*/
ORDER  BY [level] DESC

#1