php - mysql日期行到列然后以周显示

Question

非常感谢对此的任何帮助。我有一个如下所示的表格，我需要将承包商名称保留在原处，但我需要将开始日期作为列名，然后在该开始日期下的行中显示该区域。

然后我需要能够在周一至周五的周 View 中显示这些数据。如果承包商没有区域工作，那么我仍然需要显示他的名字，但只是一个黑行....请帮忙谢谢李安妮

Contractor      startdate   Area
BARNES Bill     2016-10-28  Area a
BARNES Bill     2016-11-02  Area a
BARNES Bill     2016-11-07  Area b
BARNES Bill     2016-11-01  Area a
BARNES Bill     2017-01-09  Area a
BARNES Bill     2017-02-17  Area b
BAUME Peter     
BAUME Peter     
BILLINGS Roger      
CAMPBELL Lee    2017-07-03  Area b
CAMPBELL Lee    2017-07-26  Area b
CAMPBELL Lee    2017-07-12  Area b
CAMPBELL Lee    2017-07-14  Area b
CARNE Jim       2017-07-05  Area a
CENTRONE Krista     
COMBES Greg     2017-07-05  Area b
COMBES Greg     2017-07-05  Area b
COMBES Greg     2016-09-09  Area c
DEVRIES Phil    2017-07-19  Area c
DEVRIES Phil    2018-03-26  Area A

需要这样查看

Contractor  2016-11-02  2016-11-07  2016-11-01  2017-01-09  2016-11-07
BARNES Bill   Area a                
BARNES Bill   Area a            
BARNES Bill   Area b            
BARNES Bill   Area a            
BARNES Bill   Area a                
BARNES Bill   Area b                
BAUME Peter                 
BAUME Peter                 
BILLINGS Roger                  
CAMPBELL Lee                Area b  
CAMPBELL Lee                Area b  
CAMPBELL Lee                Area b  
CAMPBELL Lee                Area b  
CARNE Jim   Area a              
CENTRONE Krista                 
COMBES Greg Area b              
COMBES Greg Area b              
COMBES Greg                     Area c
DEVRIES Phil                    Area c
DEVRIES Phil                    Area A

Answer 1

你可以像这样查询

SELECT Contractor,GROUP_CONCAT(Area) as comma_area,DATE_SUB(startdate,INTERVAL WEEKDAY(startdate) DAY ) as week, MONTH(startdate) as montn,YEAR(startdate) FROM table1 GROUP BY WEEK(startdate,1);

它不会为您提供准确的结果，但会以逗号分隔的字段为您提供区域以及承包商名称、周、月和年。您可以稍后在代码中修改结果。