来自前6位的SQL计数值

Question

我尝试创建一个SQL查询来计算Top 6选择中超过=‘Y’的次数，但在合并查询中收到一个错误。

查询以获取前6名：

 SELECT TOP 6 [Number]
  ,[Over]
  ,[Goal]
 FROM [Test_Hermes].[dbo].[Test]
 ORDER BY Number desc

要计数的查询：

SELECT COUNT([Over]) 
FROM [dbo].[Test] 
WHERE [OVER] = 'Y';

合并这两个查询：

SELECT COUNT([Over]) 
FROM (SELECT TOP 6 [Number]
  ,[Over]
  ,[Goal]
FROM [Test_Hermes].[dbo].[Test]
ORDER BY Number desc) 
WHERE [OVER] = 'Y';

任何帮助都将不胜感激！谢谢

Answer 1

请给我看一下这个样品。

 SELECT
   [Number]
  ,[Over]
  ,[Goal]
  , SUM( If(OVER= 'Y',1,0)) AS CNT
 FROM [Test_Hermes].[dbo].[Test]
 ORDER BY Number desc
LIMIT 1,6;

Answer 2

不能在子查询中使用order by。

SELECT COUNT([t1].[Over]) 
FROM (SELECT TOP 6 [Number]
    ,[Over] 
    ,[Goal]
  FROM [Test_Hermes].[dbo].[Test]) as t1
WHERE [OVER] = 'Y' ;

如果您需要确定前6名的顺序，请使用ROW_NUMBER()

SELECT COUNT([t1].[Over]) 
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY [Number] ORDER BY [Number] DESC) as 'Rank'
    ,[Number]
    ,[Over] 
    ,[Goal]
  FROM [Test_Hermes].[dbo].[Test]) as t1 

WHERE [t1].[OVER] = 'Y' AND [t1].[Rank] <= 6;