sql - 选择按年份分组的 SQL 结果

Question

如何获得每 2 年分组的结果？

表 1:订单

 ORDER_ID       MemberID   OrderDate
+----------+--------------+-----+
 1          a              05-06-2016
 2          b              06-06-2016 
 3          a              06-06-2017
 4          b              07-06-2017
 5          h              08-06-2017
 6          a              02-06-2018
 7          b              03-06-2018
 8          a              05-06-2019
 9          b              09-06-2019

预期输出结果:

  YEAR      ORDERS_AMOUNT   
+----------+--------------+
 2016-2017          5              
 2018-2019          4

我正在使用以下查询，但发现它不起作用...

SELECT year( o.orderDate) as Year, 
    count(o.order_id) as Order_Count
FROM orders  o 
Group by o.orderDate 

Answer 1

使用 Year(orderDate)/2 的Group By

SELECT  (CONVERT(VARCHAR,MIN(year(OrderDate)))+'-' + CONVERT(VARCHAR,MAX(year(OrderDate)))) AS [Year],
        COUNT(*) as [ORDERS_AMOUNT]
FROM Orders
GROUP BY FLOOR(Year(OrderDate) /2)