PHP和Mysql查询，使用PHP将行转换为列 [英] PHP and Mysql query, use PHP to convert row to columns
问题描述
我认为这是超越我的，我想转换我的MYSQL查询，使其显示行和列完全像这样：
这是我用MYSQL查询创建的：
这是创建此查询的实际查询：
SELECT h.workdate AS`Date`
，DAYNAME（h.workdate）AS`Day`
，COALESCE TRUNCATE（SUM（
CASE
WHEN（shift ='AM'AND DriverDock ='Driv'AND'工资代码描述'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） AMPERMDRIV`
，COALESCE（TRUNCATE（SUM（
CASE
WHEN（shift ='AM'AND DriverDock ='Dock'AND'工资代码描述'LIKE'％Perm％'）
THEN
CASE
当paycode IN（'03 Overtime 2.0'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`， '其他费率'）* 2.0）
当paycode IN（'02加班1.5'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`， `Other Rate`）* 1.5）
当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
当paycode IN（'03 Overtime 2.0'）
THEN（employeehours * IF（p.`other rate` = 0，
当paycode IN（'02 Overtime 1.5'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Other Rate`） p``Base Rate`，p.`Other Rate`）* 1.5）
当paycode IN（'01 Ordinary'）
THEN（employeehours * p.`Base Rate`）
END
END
），2），0）AS`AMCASUDRIV`
，COALESCE（TRUNCATE（SUM（
CASE
WHEN（shift ='AM'AND DriverDock = 'b''''''''''''''''''''''''''''''''''''
当paycode IN（'02 Overtime 1.5'）
THEN（employeehours * IF（'0+++++++++++++++++++++++++++++++++++++++++++++++++++++ p``其他费率`= 0，p。基本费率，p.`other rate`）* 1.5）
当paycode IN（'01普通'）
THEN（employeehours * p。基本费率）
END
END
），2），0）AS`AMCASUDOCK`
，COALESCE（TRUNCATE（SUM（
CASE
WHEN （shift ='AM'AND DriverDock ='Driv'AND'工资代码描述'LIKE'％AGENT％'）
THEN
CASE
当paycode IN（'03 Overtime 2.0'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`，p.`other rate`）* 2.0）
当paycode IN（'02 Overtime 1.5'）
THEN（employeehours * IF（p.`other Rate` = 0，p.`Base Rate`，p.`other rate`）* 1.5）
当paycode IN（'01 Ordinary'）
THEN（employeehours * p.`Base Rate`）
END
END
），2），0）AS`AMAGENTDRIV`
，COALESCE（TRUNCATE（SUM b $ b CASE
WHEN（shift ='AM'AND DriverDock ='Dock'AND'工资代码描述'LIKE'％AGENT％'）
THEN
CASE
IN（'03 Overtime 2.0'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`，p.`other rate`）* 2.0）
IN（'02 Overtime 1.5'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`，p.`other rate`）* 1.5）
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
当paycode IN（'03 Overtime 2.0'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`，p.`other rate`） * 2.0）
当paycode IN（'02 Overtime 1.5'）
THEN（employeehours * IF（p.`other rate` = 0，p.`Base Rate`，p.`other rate`） * 1.5）
当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
当paycode IN（'03 Overtime 2.0'）
THEN（employeehours * IF（p.`other rate` = 0，p.` `，p.`other rate`）* 2.0）
当paycode IN（'02 Overtime 1.5'）
THEN（employeehours * IF（p.`other rate` = 0，p.` `，p.`other rate`）* 1.5）
当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`工资代码描述`LIKE'％Cas％'）
THEN
CASE
当paycode IN（'03 Overtime 2.0'）
THEN（employeehours * IF ``= 0，p.`Base Rate`，p.`other Rate`）* 2.0）
当paycode IN（'02 Overtime 1.5'）
THEN（employeehours * IF `= 0，p.`Base Rate`，p.`Other Rate`）* 1.5）
当paycode IN（'01 Ordinary'）
THEN（employeehours * p.`Base Rate`）
END
END
），2），0）AS`PMCASUDOCK`
，COALESCE（TRUNCATE（SUM（
CASE
WHEN（shift = 'AND DriverDock ='Dock'AND'工资代码描述'LIKE'％AGENT％'）
THEN
CASE
当paycode IN（'03 Overtime 2.0'）
THEN employeehours * IF（p.`Other Rate` = 0，p.`Base Rate`，p.`other rate`）* 2.0）
当paycode IN（'02加时1.5'）
THEN employeehours * IF（p.`Other Rate` = 0，p.`Base Rate`，p.`other rate`）* 1.5）
当paycode IN（'01 Ordinary'）
THEN * 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或p.ToDate IS NULL）
CROSS
JOIN otherrates r
WHERE DAYNAME（h.workdate）< 'Saturday'
GROUP BY h.workdate
我有以下html表，我循环查询结果并正确显示？：
<？php
$ querydailyoperations = mysql_query b $ b QUERY ABOVE
）;
while（$ querydailyoperationsshow = mysql_fetch_array（$ querydailyoperations））{echo??????????;};
？>
和：
code>< tr>< td class =greencell> AM< / td>< td class =greencell> PERMANENT< / td>< td class =greencell> DRIVER< ; / td> 5x td这里，从上面输入查询...
< / tr>
< tr>< td class =greencell> AM< / td>< td class =greencell> PERMANENT< / td>< td class =dbluecell> / 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> / td> etc ...
< / tr>
< tr>< td class =greencell> AM< / td>< td nowrap class =redcell> AGENT LABOR< / td>< td class =greencell> DRIVER< / td> etc ...
< / tr>
< tr>< td class =greencell> AM< / td>< td nowrap class =redcell> AGENT LABOR< / 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> / td> etc ...
< / tr>
< tr>< td class =bluecell> PM< / td>< td class =orangecell> CASUAL< / td>< td class =dbluecell> / td> etc ...
< / tr>
< tr>< td class =bluecell> PM< / td>< td nowrap class =redcell> AGENT LABOR< / td>< td class =dbluecell> DH< / td> etc ...
< / tr>
< / tbody>
< / table>
可以使用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
- - --- ---
费用2 11
fi 3 13
fo 5 17
fum 7 19
这不是真的有必要做转换，你可以走原始数组。但是将转换分离为一个单独的步骤可能会使您的代码更容易一些，当你实际生成到页面的输出。 （这似乎是一个常见的问题，有人可能写了一个函数，进行你想要的数组转换。我不认为有一个PHP内置函数。
标题：
array {[0] =>'bar'[1] =& =>'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] =& >'fum'[1] =>'7'[2] => '19'}
}
对于像你这样的一小组行，我选择在PHP中而不是在SQL中执行。
$ b $
但是你要求如何在SQL中执行它。如前所述，SQL要求SELECT语句定义每个
如果我们构建另一个查询（除了原始查询之外），那么返回的列的数量和类型不能是dymanic。定义列，并返回我们期望返回的行的占位符值，我们在中途。所剩下的就是对原始查询返回的行执行外连接，并有条件地返回相应行上的列值。
如果您有一组预定义的行和列，我们需要返回，特别是当原始行源是稀疏的，我们需要生成缺少行。 （例如，获取订购产品的计数，并且有很多行缺少，则没有一种很好的方法来生成缺少的行。
例如：
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，但是一旦我们匹配真实源结果集中的行，我们就需要它。
现在的技巧是匹配源代码中的行，并根据适当的条件从列中返回值。
generate，本质上是一个如下的结果集：
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
然后我们要行，通过从结果集中删除foo列，并在 bar 上执行GROUP BY。我们将使用一个聚合函数（MAX或SUM），利用它们对NULL值的处理，生成如下的结果：
bar foo ABC DEF
--- --- --- ---
费用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'
UNION ALL SELECT'fi'
UNION ALL SELECT'fo'
UNION ALL SELECT'fum'
）r
CROSS
JOIN mysource t
GROUP BY r.bar
在上面的查询中，code> mysource 可以替换为内联视图，在合适的查询周围包围返回我们想要的行的约束。
内嵌式检视的别名为 r 是我们传回要传回的列的来源。
SELECT列表中的表达式正在进行条件测试，以便为每一行中的每个列选择正确的值。
给定CASE语句的常规模式，使用一些SQL来帮助生成查询，但必须作为单独的步骤完成。
在这种情况下，假设 workdate 是你想要用于列标题，这很可能需要动态生成。 （您可以从原始来源查询中删除第二列星期几列，并将其移到外部查询。
如果我不知道 workdate 在运行查询之前的标题的值，然后我将选择创建一个TEMPORARY TABLE并填充原始查询的结果，然后查询临时表，获取 workdate 头和第一列生成行，然后我将对临时表运行实际的查询。
重复一遍，我认为你最好从PHP中的原始查询中转换/转换结果，而不是尝试它在SQL中。
I think this is beyond me, I want to convert my MYSQL query so that it displays rows and columns exactly like this:
This is what I have created with a MYSQL query:
Here is the actual query to create this:
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
I have the following html table, how do I loop through the query results and display correctly?:
<?php
$querydailyoperations=mysql_query("
QUERY ABOVE
");
while($querydailyoperationsshow=mysql_fetch_array($querydailyoperations)){ echo "??????????";};
?>
and:
<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>
It's possible to get the result using a SQL query, but it's not trivial.
But before you go that route, I recommend you consider an different approach.
Since the query is returning a relatively small set of rows, you could instead retrieve the entire result set into PHP as a two dimensional array.
Consider a fairly simple case, as an illustration:
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
We could do a fetchAll, and get a two dimensional array, then loop through the array and retrieve the values column-wise, rather than row-wise. One option is to transform the array we receive into a new arrays that look like this:
bar ABC DEF
--- --- ---
fee 2 11
fi 3 13
fo 5 17
fum 7 19
It's not really necessary to do the transformation, you could walk the original array. But separating the transformation out as a separate step would probably make your code a little easier, when you get to actually generating the output into the page. (It seems like a common enough problem that someone has probably written a function that does the array transformation you want. I don't think there's a PHP builtin that does it.
headings:
array { [0]=>'bar' [1]=>'ABC' [2]=>'DEF' }
rows:
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' }
}
For a small set of rows like you have, I would opt to do this in the PHP rather than in SQL.
But you asked how to do it in SQL. As I said earlier, it's not trivial.
SQL requires that the SELECT statement define every column to be returned; the number and types of the columns can't be dymanic when the statement executes.
If we build another query (apart from the original query) that defines the columns, and returns the rows we expect returned with placeholders for the values, we are halfway there. All that remains is to do an outer join to the rows returned by the original query, and conditionally return the column values on the appropriate rows.
This approach works if you have a pre-defined set of rows and columns we need returned, especially when the original row source is sparse, and we need to generate the "missing" rows. (For example, getting counts of products ordered, and there are a lot of missing rows, there's not a good way to generate the missing rows.
For example:
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
That will return:
bar ABC DEF
--- --- ---
fee
fi
fo
fum
So, that gets us all of the columns defined, and all of the rows we want to return. The first column is populated. That query doesn't really need the GROUP BY yet, but we are going to need it once we match to the rows in from the "real" source result set.
The "trick" now is matching the rows from our source, and returning the value from a column based on the appropriate conditions.
What we are going to generate, essentially is a result set that looks like this:
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
Then we are going to "collapse" the rows, by removing the foo column from the resultset and doing a GROUP BY on bar. We are going to use an aggregate function (either MAX or SUM) taking advantage of the handling they do with NULL values, to produce a result like this this:
bar foo ABC DEF
--- --- --- ---
fee 2 11
fi 3 13
fo 5 15
fum 7 17
Using this rather unwieldy 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
Note that mysource in the query above can be replaced with an inline view, wrapping parens around a suitable query that returns the rows we want.
The inline view aliased as r is our source for returning the rows we want to return.
The expressions in the SELECT list are doing the conditional tests, to pick out the right values for each column in each row.
Given the regular pattern of the CASE statements, it's possible to use some SQL to help generate the query, but that has to be done as a separate step. The output from that SQL can be used to help form the actual query we need.
In your case, given that workdate is what you want to use for the column header, this is likely going to have to be dynamically generated. (You could drop that second column 'day of week' column from the original source query, and move that to the outer query.
If I didn't know the workdate values for the headings before I ran the query, then I'd opt to create a TEMPORARY TABLE and populate it with results from the original query, and then query the temporary table, to get the workdate headers, and the "first column" to generate the rows. Then I'd run the actual query against the temporary table.
To repeat, I think you'd be better off doing the transform/pivot of the results from your original query in PHP, rather than trying to do it in SQL.
这篇关于PHP和Mysql查询，使用PHP将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
