SQL 计算所有列，求和，并按 ASC 顺序显示最后 3 行

Question

我想计算表中所有值 >= 10 的列。这是我的 table :

 Date#####   |  Value1 |  Value2 |   Value3
 23/04/2014  |  1,2    |    12,3 |    10  
 23/04/2014  |  11,2   |    3    |   10,3     
 24/04/2014  |  10,9   |    3    |     1  

我想让它显示:

 Date#####   | Count 
 23/04/2014  |  4  
 24/04/2014  |  1 

假设我有很多组日期，我希望它只显示最后 3 组日期。

这是我的第一个代码:

Dim strCommand As String = "Select Date, count(*) as tcount 
                            from tbBooth 
                            having count(*) >= 10 
                            group by date"

已经根据 Collapsar 的解决方案更改为:

Dim strCommand As String = "Select t.d, sum(t.valcount) cnt 
                            from (select [date] AS d, 
                                         CASE WHEN t1.Value1 >= 10 THEN 1 
                                              ELSE 0 END + 
                                         CASE WHEN t1.Value2 >= 10 THEN 1 
                                              ELSE 0 END + 
                                         CASE WHEN t1.Value3 >= 10 THEN 1 
                                              ELSE 0 END AS valcount 
                                   from tbBooth t1) t 
                            group by t.d"

它可以工作，但我只想根据 ASC 顺序显示最后 3 行。有什么办法吗？

Answer 1

SELECT d, cnt
FROM 
(
  SELECT TOP 3 [date] AS d, 
               SUM(CASE WHEN Value1 >= 10 THEN 1 ELSE 0 END + 
                   CASE WHEN Value2 >= 10 THEN 1 ELSE 0 END + 
                   CASE WHEN Value3 >= 10 THEN 1 ELSE 0 END) AS cnt
  FROM tbBooth
  GROUP BY [date]
  ORDER BY [date] DESC
) x
ORDER BY d ASC