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
3
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
1
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
3
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
1
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