本文介绍了将日期或日期显示为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中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！