我不确定这是否可能，但我真的很想知道SQL是否可以解决这个问题，或者我应该继续使用PHP来处理它。
我有一个包含表单信息的表。设置是这样的，submission列标识表单条目，field列表示输入字段的name attribute，然后data是发布的信息。
它看起来像这样:
+---------------------------------------------------------------+
|id |submission|ref |field |data |
+---------------------------------------------------------------+
|1 |1 |hox23 |name |John Doe |
+---------------------------------------------------------------+
|2 |1 |hox23 |address |Sesame Street 12 |
+---------------------------------------------------------------+
|3 |1 |hox23 |phone |5555-1234 |
+---------------------------------------------------------------+
|4 |1 |hox23 |email |john@doe.ex |
+---------------------------------------------------------------+
|5 |2 |hox23 |name |Josh Smith |
+---------------------------------------------------------------+
|6 |2 |hox23 |address |Any Street 34 |
+---------------------------------------------------------------+
|7 |2 |hox23 |phone |5555-5678 |
+---------------------------------------------------------------+
|8 |2 |hox23 |email |josh@smith.ex |
+---------------------------------------------------------------+
|9 |3 |hox23 |name |Jane Summer |
+---------------------------------------------------------------+
|10 |3 |hox23 |address |Last Street 4 |
+---------------------------------------------------------------+
|11 |3 |hox23 |phone |5555-9012 |
+---------------------------------------------------------------+
|12 |3 |hox23 |email |jane@summer.ex |
+---------------------------------------------------------------+
|13 |4 |hox23 |name |Patrick Thom |
+---------------------------------------------------------------+
|14 |4 |hox23 |website |www.thom.ex |
+---------------------------------------------------------------+
|15 |4 |hox23 |phone |555-1235 |
+---------------------------------------------------------------+
|16 |4 |hox23 |email |patrick@thom.ex |
+---------------------------------------------------------------+
|17 |5 |hox23 |name |Hillary Good |
+---------------------------------------------------------------+
|18 |5 |hox23 |website |www.good.ex |
+---------------------------------------------------------------+
|19 |5 |hox23 |phone |5555-8365 |
+---------------------------------------------------------------+
|20 |5 |hox23 |email |hillary@good.ex |
+---------------------------------------------------------------+
|21 |6 |hox23 |name |Toby Chalk |
+---------------------------------------------------------------+
|22 |6 |hox23 |email |toby@chalk.ex |
+---------------------------------------------------------------+
|23 |6 |hox23 |website |www.chalk.ex |
+---------------------------------------------------------------+
|24 |7 |hox23 |name |Kat Buo |
+---------------------------------------------------------------+
|25 |7 |hox23 |email |kat@buo.ex |
+---------------------------------------------------------------+
|26 |7 |hox23 |website |www.buo.ex |
+---------------------------------------------------------------+
|27 |8 |hox23 |name |Mill Green |
+---------------------------------------------------------------+
|28 |8 |hox23 |email |mill@green.ex |
+---------------------------------------------------------------+
|29 |8 |hox23 |website |www.green.ex |
+---------------------------------------------------------------+
|30 |9 |hox23 |phone |555-6123 |
+---------------------------------------------------------------+
|31 |9 |hox23 |address |Some other place 7 |
+---------------------------------------------------------------+
|32 |9 |hox23 |name |Carl Stuff |
+---------------------------------------------------------------+
正如您所看到的，每个条目的行数不同，也没有相同的顺序或相同的字段。目前，我的PHP脚本获取表内容，根据不同的field变量创建一个KEY数组。然后我再次循环遍历所有条目，将它们合并到另一个数组中，最终得到一个数组，该数组包含每个submission的一行，每个字段都有数据。
我想知道的是，如果SQL(目前我正在使用MySQL)可以为我做到这一点。是否有一种方法可以使一个select语句如此动态，以至于它可以输出这样的表:
+---------------------------------------------------------------------------------+
|submission|name |address |phone |email |website |
+---------------------------------------------------------------------------------+
|1 |John Doe |Sesame Street 12 |5555-1234|john@doe.ex |- |
+---------------------------------------------------------------------------------+
|2 |Josh Smith |Any Street 34 |5555-5678|josh@smith.ex |- |
+---------------------------------------------------------------------------------+
|3 |Jane Summer |Last Street 4 |5555-9012|jane@summer.ex |- |
+---------------------------------------------------------------------------------+
|4 |Patrick Thom|- |555-1235 |patrick@thom.ex|www.thom.ex |
+---------------------------------------------------------------------------------+
|5 |Hillary Good|- |5555-8365|hillary@good.ex|www.good.ex |
+---------------------------------------------------------------------------------+
|6 |Toby Chalk |- |- |toby@chalk.ex |www.chalk.ex|
+---------------------------------------------------------------------------------+
|7 |Kat Buo |- |- |kat@buo.ex |www.buo.ex |
+---------------------------------------------------------------------------------+
|8 |Mill Green |- |- |mill@green.ex |www.green.ex|
+---------------------------------------------------------------------------------+
|9 |Carl Stuff |Some other place 7|555-6123 |- |- |
+---------------------------------------------------------------------------------+
我当前的选择语句正在寻找ref作为它的标识符。我只想获取与这个id相连的所有条目。注意，field列是不一致的，您不知道每个提交id是有3行还是4行。您不知道name字段是每个条目的第一行还是最后一行，或者name字段是否存在。我自己发现很难弄清楚这一点，但我真的很想知道，如果有一种方法为SQL来管理这个设置?
编辑
我至少减少了我的PHP脚本很多。目前看起来是这样的:
<?php
$conn = new PDO(
'mysql:host=localhost;dbname=data_loop',
"username",
"password",
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
// Reference variable. Can be a posted variable to look up.
$ref = 'hox23';
$sql = $conn->prepare('SELECT * FROM data_loop WHERE ref = :ref GROUP BY field');
$sql->bindParam(':ref', $ref, PDO::PARAM_STR);
$sql->execute();
$res = $sql->fetchAll(PDO::FETCH_ASSOC);
$que = 'SELECT A.submission';
$ry = 'FROM data_loop AS A ';
$i = 'B';
foreach ($res as $key => $value) {
$que .= ', '.$i.'.Data AS '.$value['field'].' ';
$ry .= 'LEFT JOIN data_loop AS '.$i . ' ON A.submission = '.$i.'.submission '. 'AND '.$i.".field = '".$value['field']."' ";
$i++;
}
$query = $que.$ry."WHERE A.ref = '".$ref."'";
$stmt = $conn->prepare($query);
$stmt->execute();
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($row as $key => $value) {
echo $value['name'] . '<br>' . $value['address'] . '<br>' . $value['email'] . '<br>' . $value['phone'] . '<br>' . $value['website'] . '<br>';
}
?>
希望它能帮助到其他人。
我能想到的最简单的方法是连接表本身。
所以…
Select A.Submission,
B.Data AS Name,
C.Data as Address,
D.Data as Phone,
E.Data as Email,
F.Data as Website
FROM TableData AS A
LEFT JOIN TableData AS B
ON A.Submission = B.Submission
AND B.Field = "Name"
LEFT JOIN TableData AS C
ON A.Submission = C.Submission
AND C.Field = "Address"
LEFT JOIN TableData AS D
ON A.Submission = D.Submission
AND D.Field = "Phone"
LEFT JOIN TableData AS E
ON A.Submission = E.Submission
AND E.Field = "Email"
LEFT JOIN TableData AS F
ON A.Submission = F.Submission
AND F.Field = "Website"
根据kristof的建议更改为左连接，您还可以在select语句周围放置一些isnull，以"-"代替NULL(isnull (e.))。数据，"-"))或Coalese(感谢kristof)
编辑:添加在网站上，我没有足够的评论点(50!)所以是的，你可以动态创建它，我要做的方式需要更多的工作。您需要创建一个文本字符串，该字符串根据您拥有的变量数量构建一个选择语句，然后在构建后执行该字符串。这样做有点麻烦，但当它工作时，它很可爱。我怀疑这可能有点太复杂了基于问题的水平，所以我建议只是添加额外的字段时，他们发生和生产"-"时，他们不存在
我不打算写出您需要的所有代码，但我会引导您完成一个可能的解决方案。
用例语句计算'field'列，然后为其他列赋值。像这样:
SELECT
CASE
WHEN table.field = 'name'
THEN table.data
ELSE null
END
AS name,
对要添加的每个新列重复此操作，将'name'替换为相关列。(地址、电话、电邮、网址)
SELECT SUBMISSION,
[NAME],
[address],
[phone],
[email]
FROM (SELECT SUBMISSION,
FIELD,
DATA
FROM rec) P
PIVOT
(
MAX(DATA) FOR FIELD IN ([NAME],[address],[phone],[email])
) AS PVT