使用CONCAT格式化MySQL [英] Format MySQL using CONCAT

问题描述

感谢一些帮助我有一个MySQL查询开始于2014-10-10和罚款的假期在12个月期间例如2014-10-10到2015-10-09然后2015-10-10到2016 -10-09

  SELECT 
 e.name AS Employee，
 CEIL（DATEDIFF ，e.startdate）/ 365）as Year，
 count（h.date）as Holidays_Taken 
 FROM employees AS e 
 LEFT JOIN holidays_taken AS h ON e.id = h.empid 
 WHERE e.id = 1 
 GROUP BY年份
  

结果

  + ---------- + ------ + ---------- ----- + 
 |员工|年|假期
 + ---------- + ------ + --------------- + 
 | Jon | 1 | 5 | 
 + ---------- + ------ + --------------- + 
 | Jon | 2 | 1 | 
 + ---------- + ------ + --------------- + 
  
 
 
 
可以让2014-10-10到2015-10-09而不是第1年，然后2015-10-10到2016- 10-09 for year 2 
 
 
 
以下是我的 SQL FIDDLE  
 
 
 
感谢
解决方案
  CONCAT 以这种方式：
  SELECT 
。 name AS Employee，
 CONCAT（
 CEIL（DATEDIFF（h.date，e.startdate）/ 365），
'（'，
 DATE_ADD（e.startdate，INTERVAL FLOOR （DATEDIFF（h.date，e.startdate）/ 365）YEAR），'to'，
 DATE_ADD（e.startdate，INTERVAL CEIL（DATEDIFF（h.date，e.startdate）/ 365）YEAR） 
'）'
）as Year，
 COUNT（h.date）AS Holidays_Taken，
 SUM（h.hours）AS小时
 FROM employees AS e 
 LEFT JOIN holidays_taken AS h ON e.id = h.empid 
 WHERE e.id = 1 
 GROUP BY年份
  
 演示： SQL FIDDLE   
 
Thanks to some help  I have a MySQL query that starts on 2014-10-10 and fines the amount of holidays in 12 months period eg 2014-10-10 to 2015-10-09 then 2015-10-10 to 2016-10-09 
SELECT 
    e.name AS Employee,
    CEIL(DATEDIFF(h.date, e.startdate)/365) as Year,
    count(h.date) as Holidays_Taken
FROM employees AS e
LEFT JOIN holidays_taken AS h ON e.id = h.empid
WHERE e.id = 1
GROUP BY Year
With a result
+----------+------+---------------+
| Employee | Year | Holidays_Taken|
+----------+------+---------------+
| Jon      | 1    | 5             |
+----------+------+---------------+
| Jon      | 2    | 1             |
+----------+------+---------------+
Is it possible to have the year show 2014-10-10 to 2015-10-09 instead of year 1 then 2015-10-10 to 2016-10-09 for year 2 

Here's my SQL FIDDLE

Thanks
解决方案
I would create the CONCAT in this manner:
SELECT 
    e.name AS Employee,
    CONCAT(
        CEIL(DATEDIFF(h.date, e.startdate)/365), 
        ' (',
        DATE_ADD(e.startdate, INTERVAL FLOOR(DATEDIFF(h.date, e.startdate)/365) YEAR), ' to ',        
        DATE_ADD(e.startdate, INTERVAL CEIL(DATEDIFF(h.date, e.startdate)/365) YEAR),
        ')'
        ) as Year,
    COUNT(h.date) AS Holidays_Taken,
    SUM(h.hours) AS Hours
FROM employees AS e
LEFT JOIN holidays_taken AS h ON e.id = h.empid
WHERE e.id = 1
GROUP BY Year
DEMO: SQL FIDDLE

                        
这篇关于使用CONCAT格式化MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！