mysql - 获取行特定情况下的 COUNT()(计数将取决于列)

Question

我想获取行数，但“counts”是一种特殊情况。

表A

   id   |  category  |         time=============================================      1 |          1 |  2015-02-09 12:00:00      2 |          3 |  2015-02-09 13:00:00      3 |          2 |  2015-02-09 14:00:00      4 |          3 |  2015-02-09 14:00:00      5 |          3 |  2015-02-09 15:00:00      6 |          1 |  2015-02-09 16:00:00      7 |          1 |  2015-02-09 16:00:00      8 |          1 |  2015-02-09 17:00:00

---

“计数”与“类别”总计数相同。

'order' 是 'counts' 的序数 ORDER BY ASC

'order' 含义如下。

 order | counts | id | category |         time========================================================     1 |      4 |  1 |        1 |  2015-02-09 12:00:00     2 |      4 |  6 |        1 |  2015-02-09 16:00:00     3 |      4 |  7 |        1 |  2015-02-09 16:00:00     4 |      4 |  8 |        1 |  2015-02-09 17:00:00     1 |      1 |  3 |        2 |  2015-02-09 14:00:00     1 |      3 |  2 |        3 |  2015-02-09 13:00:00     2 |      3 |  4 |        3 |  2015-02-09 14:00:00     3 |      3 |  5 |        3 |  2015-02-09 15:00:00

---

如何编写查询以获得以下结果？ (添加两列)

结果

 order | counts | id | category |         time========================================================     1 |      4 |  1 |        1 |  2015-02-09 12:00:00     1 |      3 |  2 |        3 |  2015-02-09 13:00:00     1 |      1 |  3 |        2 |  2015-02-09 14:00:00     2 |      3 |  4 |        3 |  2015-02-09 14:00:00     3 |      3 |  5 |        3 |  2015-02-09 15:00:00     2 |      4 |  6 |        1 |  2015-02-09 16:00:00     3 |      4 |  7 |        1 |  2015-02-09 16:00:00     4 |      4 |  8 |        1 |  2015-02-09 17:00:00

---

Answer 1

使用以下内容

Select id,category,(select count(*) from catt as ct where ct.category = catt.category)as counts from catt

工作 DEMO

根据第二个要求 ->

set @category := '', @num := 1;


Select id,category,(select count(*) from catt as ct where ct.category = 

catt.category)as counts,@num := if(@category = catt.category, @num + 1, 1) as 

row_number , @category := category as dummy from catt order by category DESC

工作 SQLFiddle