我试图做的是一个查询,每天给我带来所有记录的计数。见下表中的例子:
<table>
<tr>
<th>Day/Hour</th>
<th>0</th>
<th>1</th>
<th>2</th>
<th>4</th>
<th>5</th>
<th>6</th>
<th>7</th>
<th>8</th>
<th>9</th>
<th>10</th>
<th>11</th>
<th>12</th>
<th>13</th>
<th>14</th>
<th>15</th>
<th>16</th>
<th>17</th>
<th>18</th>
<th>19</th>
<th>20</th>
<th>21</th>
<th>22</th>
<th>23</th>
</tr>
<tr>
<td>2015-06-02</td>
<td>12</td>
<td>22</td>
<td>198</td>
<td>234</td>
<td>12</td>
<td>22</td>
<td>198</td>
<td>234</td>
<td>12</td>
<td>22</td>
<td>198</td>
<td>234</td>
<td>12</td>
<td>22</td>
<td>198</td>
<td>234</td>
<td>12</td>
<td>22</td>
<td>198</td>
<td>234</td>
<td>12</td>
<td>22</td>
<td>198</td>
</tr>
<tr>
<td>2015-06-01</td>
<td>3</td>
<td>342</td>
<td>1348</td>
<td>4</td>
<td>3</td>
<td>342</td>
<td>1348</td>
<td>4</td>
<td>3</td>
<td>342</td>
<td>1348</td>
<td>4</td>
<td>3</td>
<td>342</td>
<td>1348</td>
<td>4</td>
<td>3</td>
<td>342</td>
<td>1348</td>
<td>4</td>
<td>3</td>
<td>342</td>
<td>1348</td>
<tr>
</table>实际上,这正是我应该显示数据(表)的方式,也是我需要结果集的方式。
发布于 2015-06-02 16:37:57
由于一天中的小时是一个常数集,在可预见的将来不会在任何时间改变,而且您可能也希望显示没有行的日时组合,所以最简单的事情可能是在查询中硬编码24列:
SELECT DATE(date_col),
COUNT (CASE HOUR(date_col) WHEN 0 THEN 1 ELSE NULL END) AS "0",
COUNT (CASE HOUR(date_col) WHEN 1 THEN 1 ELSE NULL END) AS "1",
-- 22 more of these
FROM my_table
GROUP BY DATE(date_col)https://stackoverflow.com/questions/30601528
复制相似问题