我有一个问题是我无法从每一行的表结果中获得每天的id。我在phpMyAdmin中的表是这样的:
------------------------------------
| Date_id | Date |
------------------------------------
| 1 | 2014-05-13 |
| 2 | 2014-06-04 |
| 3 | 2014-07-09 |
| 4 | 2014-08-13 |
| 5 | 2014-09-12 |
| 6 | 2014-10-15 |
| 7 | 2014-11-19 |
| 8 | 2014-12-10 |
| 9 | 2015-01-14 |
| 10 | 2015-02-11 |
| 11 | 2015-03-10 |
| 12 | 2015-04-15 |
| 13 | 2015-05-12 |
| 14 | 2015-06-12 |
------------------------------------
当我写代码php获得Date_id
编辑,但它仍然显示第一行只有一个id为每列相同的第二,第三行…也只显示一个id。My Tables like this:
------------------------------------------------------------------------------------------
| Year | Jan | Feb | Mar | April | May | June | July | Aug | Sept | Oct | Nov | Dec |
------------------------------------------------------------------------------------------
| 2014 | | | | | 13 | 04 | 07 | 13 | 12 | 15 | 19 | 10 |
----------------------------------------------------------------------------------------
| 2015 | 14 | 11 | 10 | 15 | 12 | 12 | | | | | | |
----------------------------------------------------------------------------------------
这是我使用过的查询代码:
select year(`Date`) as `year`,Date_id,
max(case when month(date) = 1 then day(`date`) end) as Jan,
max(case when month(date) = 2 then day(`date`) end) as Feb,
max(case when month(date) = 3 then day(`date`) end) as Mar,
max(case when month(date) = 4 then day(`date`) end) as Apr,
max(case when month(date) = 5 then day(`date`) end) as May,
max(case when month(date) = 6 then day(`date`) end) as Jun,
max(case when month(date) = 7 then day(`date`) end) as Jul,
max(case when month(date) = 8 then day(`date`) end) as Aug,
max(case when month(date) = 9 then day(`date`) end) as Sep,
max(case when month(date) = 10 then day(`date`) end) as Oct,
max(case when month(date) = 11 then day(`date`) end) as Nov,
max(case when month(date) = 12 then day(`date`) end) as Dec
from table t
group by year(date)
order by year(date)
我的预期结果是它将显示为我的表,它将从每天获得id。
我如何编写查询?谢谢你。
您的查询看起来很好,但有一个小问题。您正在使用一个保留关键字dec
,这是打破查询。您需要将其打勾,这是修改后的查询
select year(`Date`) as `year`,Date_id,
max(case when month(date) = 1 then day(`date`) end) as Jan,
max(case when month(date) = 2 then day(`date`) end) as Feb,
max(case when month(date) = 3 then day(`date`) end) as Mar,
max(case when month(date) = 4 then day(`date`) end) as Apr,
max(case when month(date) = 5 then day(`date`) end) as May,
max(case when month(date) = 6 then day(`date`) end) as Jun,
max(case when month(date) = 7 then day(`date`) end) as Jul,
max(case when month(date) = 8 then day(`date`) end) as Aug,
max(case when month(date) = 9 then day(`date`) end) as Sep,
max(case when month(date) = 10 then day(`date`) end) as Oct,
max(case when month(date) = 11 then day(`date`) end) as Nov,
max(case when month(date) = 12 then day(`date`) end) as `Dec`
from test t
group by `year`
order by `year`
如果您不愿意,可以将Date_id
从选择列表中删除。