我正在尝试计算包含特定文本的列的数量,这些列仅基于MAX值的多个WHERE LIKE条件。
ID | 名称 | 文本 | >值 | ||
---|---|---|---|---|---|
1 | Al | 新建 | |||
2 | Al | NA | |||
3 | Al | NA | |||
4 | Al | NA | 5 | BK | 建<1>|
9 | BK | 旧 | br/tr>|||
7 | BK | NA |
您需要一个最大值的子查询:
select count(*)
from mytable
where name = 'Al' and text = 'NA' and value = (select max(value) from mytable);
也许是一个具有DENSE_RANK
然后是COUNT
的CTE?
WITH CTE AS(
SELECT ID,
Name,
Text,
Value,
DENSE_RANK() OVER (PARTITION BY Name ORDER BY [Value] DESC) AS DR
FROM(VALUES(1,'Al','New',1),
(2,'Al','NA',2),
(3,'Al','NA',3),
(4,'Al','NA',3),
(5,'BK','New',1),
(9,'BK','Old',2),
(7,'BK','NA',3))V(ID,Name,Text,Value)
WHERE Name = 'Al'
AND Text = 'NA')
SELECT COUNT(*)
FROM CTE
WHERE DR = 1;