php - PHP和Mysql查询，使用PHP将行转换为列

Question

我想这超出了我的能力范围，我想转换MYSQL查询，使其显示的行和列与下面的完全一样：
这是我用MYSQL查询创建的：
下面是创建此项的实际查询：

SELECT h.workdate AS `Date`
 , DAYNAME(h.workdate) AS `Day`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='AM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%Perm%')
       THEN
         CASE
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `AMPERMDRIV`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='AM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Perm%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `AMPERMDOCK`
  , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='AM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%Cas%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `AMCASUDRIV`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='AM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Cas%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `AMCASUDOCK`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='AM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%AGENT%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `AMAGENTDRIV`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='AM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%AGENT%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `AMAGENTDOCK`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='PM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%Perm%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `PMPERMDRIV`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='PM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Perm%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `PMPERMDOCK`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='PM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Cas%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `PMCASUDOCK`
 , COALESCE(TRUNCATE(SUM(
     CASE
     WHEN (shift='PM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%AGENT%')
       THEN
         CASE 
         WHEN paycode IN ('03 Overtime 2.0')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
         WHEN paycode IN ('02 Overtime 1.5')
           THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
         WHEN paycode IN ('01 Ordinary')
           THEN (employeehours * p.`Base Rate`)
         END
     END
   ),2),0) AS `PMAGENTDOCK`
FROM employeedata d
JOIN employeehours h
ON d.`ID Number` = h.employeeid
JOIN historyemployeepay p
ON (h.employeeid = p.EmployeeID)
AND (h.workdate >= p.FromDate)
AND (h.workdate <= p.ToDate OR p.ToDate IS NULL)
CROSS  
JOIN otherrates r
WHERE DAYNAME(h.workdate) <> 'Saturday'
GROUP BY h.workdate

我有下面的html表，如何循环查询结果并正确显示？:

<?php
  $querydailyoperations=mysql_query("
                                     QUERY ABOVE
  ");
while($querydailyoperationsshow=mysql_fetch_array($querydailyoperations)){ echo "??????????";};
?>

以及：

<tr><td class="greencell">AM</td><td class="greencell">PERMANENT</td><td class="greencell">DRIVER</td>5x td here, input from above query...
</tr>
<tr><td class="greencell">AM</td><td class="greencell">PERMANENT</td><td class="dbluecell">DH</td> etc...
</tr>
<tr><td class="greencell">AM</td><td class="orangecell">CASUAL</td><td class="greencell">DRIVER</td> etc...
</tr>
<tr><td class="greencell">AM</td><td class="orangecell">CASUAL</td><td class="dbluecell">DH</td> etc...
</tr>
<tr><td class="greencell">AM</td><td nowrap class="redcell">AGENT LABOUR</td><td class="greencell">DRIVER</td> etc...
</tr>
<tr><td class="greencell">AM</td><td nowrap class="redcell">AGENT LABOUR</td><td class="dbluecell">DH</td> etc...
</tr>
<tr><td class="bluecell">PM</td><td class="greencell">PERMANENT</td><td class="greencell">DRIVER</td> etc...
</tr>
<tr><td class="bluecell">PM</td><td class="greencell">PERMANENT</td><td class="dbluecell">DH</td> etc...
</tr>
<tr><td class="bluecell">PM</td><td class="orangecell">CASUAL</td><td class="dbluecell">DH</td> etc...
</tr>
<tr><td class="bluecell">PM</td><td nowrap class="redcell">AGENT LABOUR</td><td class="dbluecell">DH</td> etc...
</tr>
</tbody>
</table>

Answer 1

使用SQL查询可以得到结果，但这并不简单。
但在你走这条路之前，我建议你考虑另一种方法。
由于查询返回的行相对较少，因此可以将整个结果集作为二维数组检索到PHP中。
以一个相当简单的例子为例：

SELECT foo, fee, fi, fo, fum
  FROM mytable 
 ORDER BY foo

foo fee fi  fo  fum
--- --- --- --- ---
ABC   2   3   5   7
DEF  11  13  17  19

我们可以执行一个fetchAll，得到一个二维数组，然后循环遍历该数组并按列而不是按行检索值。一种方法是将接收到的阵列转换为如下所示的新阵列：

bar  ABC  DEF
---  ---  ---
fee    2   11
fi     3   13
fo     5   17
fum    7   19

其实不需要进行转换，可以遍历原始数组。但是，将转换分离为单独的步骤可能会使您的代码更容易一些，当您真正将输出生成到页面时。（这似乎是一个很常见的问题，有人可能编写了一个函数来完成您想要的数组转换。我不认为有PHP内置程序可以做到这一点。
标题：

array { [0]=>'bar'  [1]=>'ABC'  [2]=>'DEF' }

排：

array {
  [0]=>array { [0]=>'fee'   [1]=>'2'  [2]=>'11' }
  [1]=>array { [0]=>'fi'    [1]=>'3'  [2]=>'13' }
  [2]=>array { [0]=>'fo'    [1]=>'5'  [2]=>'17' }
  [3]=>array { [0]=>'fum'   [1]=>'7'  [2]=>'19' }
}

对于像您这样的一小部分行，我将选择在PHP而不是SQL中执行此操作。
但你问过如何用SQL来实现。正如我之前所说，这不是小事。
SQL要求SELECT语句定义要返回的每一列；当语句执行时，列的数量和类型不能是动态的。
如果我们构建另一个定义列的查询（除了原始查询），并返回我们期望返回的带有值占位符的行，那么我们就完成了一半。剩下的就是对原始查询返回的行进行外部联接，并有条件地返回相应行上的列值。
如果您有一组预定义的行和列，我们需要返回这些行和列，特别是当原始行源是稀疏的，并且我们需要生成“缺少的”行时，这种方法就可以工作。（例如，获取所订购产品的计数，并且有很多缺少的行，没有一种好的方法来生成缺少的行。
例如：

SELECT r.bar
     , '' AS `ABC`
     , '' AS `DEF`
  FROM ( SELECT 'fee' AS bar
          UNION ALL SELECT 'fi'
          UNION ALL SELECT 'fo'
          UNION ALL SELECT 'fum'
       ) r
 GROUP BY r.bar

将返回：

 bar  ABC  DEF
 ---  ---  ---
 fee
 fi
 fo
 fum

这样，我们就可以定义所有的列，以及要返回的所有行。第一列已填充。这个查询还不需要GROUP BY，但是一旦与“real”源结果集中的行匹配，我们就需要它了。
现在的“技巧”是匹配来自源代码的行，并根据适当的条件从列返回值。
我们要生成的结果集基本上是这样的：

bar  foo  ABC  DEF
---  ---  ---  ---
fee  ABC    2
fee  DEF        11
fi   ABC    3
fi   DEF        13
fo   ABC    5
fo   DEF        15
fum  ABC    7
fum  DEF        17

然后我们将“折叠”这些行，方法是从resultset中移除foo列并在 bar上执行GROUP by。我们将使用聚合函数（MAX或SUM），利用它们对空值的处理，生成如下结果：

bar  foo  ABC  DEF
---  ---  ---  ---
fee         2   11
fi          3   13
fo          5   15
fum         7   17

使用这个相当笨拙的SQL：

SELECT r.bar
     , MAX(CASE WHEN t.foo = 'ABC' THEN CASE r.bar 
         WHEN 'fee' THEN t.fee 
         WHEN 'fi'  THEN t.fi
         WHEN 'fo'  THEN t.fo
         WHEN 'fum' THEN t.fum
       END END) AS 'ABC'
     , MAX(CASE WHEN t.foo = 'DEF' THEN CASE r.bar 
         WHEN 'fee' THEN t.fee 
         WHEN 'fi'  THEN t.fi
         WHEN 'fo'  THEN t.fo
         WHEN 'fum' THEN t.fum
       END END) AS 'DEF'
  FROM ( SELECT 'foo' AS col
          UNION ALL SELECT 'fee'
          UNION ALL SELECT 'fi'
          UNION ALL SELECT 'fo'
          UNION ALL SELECT 'fum'
       ) r
 CROSS
  JOIN mysource t
 GROUP BY r.bar

请注意，上面查询中的 mysource可以替换为一个内联视图，在返回所需行的适当查询周围包装parens。
别名为 r的内联视图是返回要返回的行的源代码。
“选择”列表中的表达式正在执行条件测试，以便为每行中的每列选择正确的值。
考虑到CASE语句的常规模式，可以使用一些SQL来帮助生成查询，但这必须作为单独的步骤来完成。该SQL的输出可用于帮助形成我们需要的实际查询。
在您的例子中，考虑到 workdate是您想要用于列标题的，这可能需要动态生成。（您可以从原始源查询中删除第二列“星期几”列，并将其移动到外部查询。
如果在运行查询之前我不知道标题的 workdate值，那么我会选择创建一个临时表，并用原始查询的结果填充它，然后查询临时表，以获取 workdate标题和生成行的“第一列”。然后对临时表运行实际查询。
重复一下，我认为最好用PHP对原始查询的结果进行转换/透视，而不是用SQL。