将日期或日期显示为mysql中的列 [英] Display day or date as column in mysql

问题描述

是否可以从具有参数 date1 和 date2 的查询中显示日期.

Is it possible to display day from a query that have parameter date1 and date2.

例如:

SELECT e.struk_no, DATE(e.created_at), c.id,  
                                count(a.harga_jual) as counting,
                                sum(a.harga_jual) AS total_item
                    FROM transaction_detail a

                    LEFT JOIN `transaction` e
                    ON a.transaction_id = e.id

                    LEFT JOIN karyawan b
                    ON a.karyawan_id = b.id

                    LEFT JOIN list_harga c
                    ON a.item_id = c.id

                    LEFT JOIN item_layanan d
                    ON c.item_layanan_id = d.id

                    WHERE DATE(e.created_at) BETWEEN DATE('2016-11-13') AND DATE('2016-11-15')

                    GROUP BY  a.karyawan_id, b.nama_karyawan, d.nama_item

我明白了:

+----------+--------------------+----+----------+------------+
| struk_no | DATE(e.created_at) | id | counting | total_item |
+----------+--------------------+----+----------+------------+
|        2 | 2016-11-15         |  5 |        1 | 90000      |
|        1 | 2016-11-14         |  4 |        2 | 200000     |
|        4 | 2016-11-15         | 16 |        1 | 400000     |
|        3 | 2016-11-15         | 19 |        1 | 75000      |
+----------+--------------------+----+----------+------------+
4 rows in set

请参阅:WHERE DATE(e.created_at) BETWEEN DATE('2016-11-13') AND DATE('2016-11-15')

是否可以像这样创建

+----------+----+----+----+----+----------+------------+
| struk_no | 13 | 14 | 15 | id | counting | total_item |
+----------+----+----+----+----+----------+------------+
|        2 |    |    | 1  |  5 |        1 | 90000      |
|        1 |    |  1 | 1  |  4 |        2 | 200000     |
|        4 |    |    | 1  | 16 |        1 | 400000     |
|        3 |    |    | 1  | 19 |        1 | 75000      |
+----------+----+----+----+----+----------+------------+
4 rows in set

唯一的参数是id，这样的命名案例是什么?

The unique parameter is id, What named case like this?

谢谢大家

推荐答案

试试这个

SELECT e.struk_no,
IF(DATE(e.created_at)= str_to_date("2016-11-13","%Y-%m-%d"),1,0) as '13'
IF(DATE(e.created_at)= str_to_date("2016-11-14","%Y-%m-%d"),1,0) as '14',
IF(DATE(e.created_at)= str_to_date("2016-11-15","%Y-%m-%d"),1,0) as '15',
c.id,
count(a.harga_jual) as counting,
sum(a.harga_jual) AS total_item
                                    FROM transaction_detail a ............

希望这有效..

这篇关于将日期或日期显示为mysql中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！