比如：
SELECT *FROM table WHERE cid IN ( 1, 3 );
得到结果
id cid name nian
1 1 名称1 2020
2 1 名称2 2019
3 1 名称3 2018
4 1 名称4 2017
5 2 名称5 2020
6 2 名称6 2019
7 2 名称7 2018
想要的结果
id nian name name1
1 2020 名称1 名称5
2 2019 名称2 名称6
3 2018 名称3 名称7
4 2017 名称4
当然也可以用PHP处理查询结果
目的是可以使得前端可以用一张表显示数据
如下：
<table>
<tr><td>2020</td><td>名称1</td><td>名称5</td></tr>
<tr><td>2019</td><td>名称2</td><td>名称6</td></tr>
<tr><td>2018</td><td>名称3</td><td>名称7</td></tr>
<tr><td>2017</td><td>名称4</td><td></td></tr>
</table>
试试下边的代码：
$dbCon = mysqli_connect("localhost","root","123456","test");
$sql ="SELECT id, nian,GROUP_CONCAT(name) AS `name` FROM test0219 WHERE cid IN(1,2) GROUP BY nian ORDER BY id ASC";
$result = mysqli_query($dbCon,$sql);
echo "<table>
<tr>
<th>nian</th>
<th>name</th>
<th>name1</th>
</tr>";
$nameStrAll = '';
while($row=mysqli_fetch_array($result)){
if(strpos($row['name'],',') !== false){
$nameStr = '';
$nameArr = explode(',',$row['name']);
foreach($nameArr as $key => $value){
$nameStr .= "<td>".$value."</td>";
}
$nameStrAll .= "<tr><td>".$row['nian']."</td>".$nameStr;
}else{
$nameStrAll .= "<tr><td>".$row['nian']."</td>"."<td>".$row['name']."</td>";
}
}
echo $nameStrAll;
echo "</table>";
mysqli_close($dbCon);
//数据库代码省略
//按年份分组
$ret = [];
while($row=mysqli_fetch_array($result)){
if(key_exists($row['year'],$ret)){
$ret[$row['year']][]=$row['name'];
}else{
$ret[$row['year']]=[]; //建立以年份为键的子数组
$ret[$row['year']][]=$row['name'];
}
}
//求最大列
$max = 0;
foreach($ret as $val){
$tmp = count($val);
$max = $tmp>$max ? $tmp : $max;
}
$html = "";
foreach($ret as $k=>$v) {
$html .= "<td>{$i}</td>"; //序号
$html .= "<td>{$k}</td>"; //年份
for ($i=0; $i < $max; $i++) { //以最大列建立td数量
if(isset($ret[$i])){ //还有值
$html .= "<td>{$ret[$i]}</td>";
}else{ //没有值了
$html .= "<td></td>";
}
}
}
// 最后再把html输出。
楼上得foreach解决了么
SELECT *FROM table WHERE cid IN ( 1, 3 );
改成
SELECT id, nian,GROUP_CONCAT(name) AS name
FROM table WHERE cid IN ( 1, 3 );
name是一个list
然后再写一个循环依次遍历出name中的所有元素即可
function sortByDate($a, $b) {
return strtotime($a) > strtotime($b);
}
$cont_history = Array (
'20-02-2015' => Array ( 'activity' => 'GATE IN', 'by' => '', 'location' => 'COLOMBO', 'depot' => 'CNLS' ),
'15-03-2015' => Array ( 'activity' => 'GATE OUT', 'by' => '', 'location' => 'COLOMBO', 'depot' => 'CNLS' ),
'18-03-2015' => Array ( 'activity' => 'GATE IN', 'by' => '', 'location' => 'CHENNAI', 'depot' => 'GOBAL'),
'05-04-2015' => Array ( 'activity' => 'GATE OUT', 'by' => '', 'location' => 'CHENNAI', 'depot' => 'GOBAL'),
'10-04-2015' => Array ( 'activity' => 'GATE IN', 'by' => '', 'location' => 'MUMBAI', 'depot' => 'CONS' ),
'13-05-2015' => Array ( 'activity' => 'GATE OUT', 'by' => '', 'location' => 'MUMBAI', 'depot' => 'CONS' ),
'10-02-2015' => Array ( 'activity' => 'Container Bought', 'by' => 'CARU', 'location' => 'COLOMBO', 'depot' => 'CNLS' ),
'07-05-2015' => Array ( 'activity' => 'Container Sold', 'by' => 'TCPL', 'location' => 'MUMBAI', 'depot' => 'CONS' )
);
uksort($cont_history, 'sortByDate');
print_r($cont_history);
<?php
$conn=mysqli_connect("localhost","php","");
mysqli_select_db($conn,"php");
$result = mysqli_query($conn,"SELECT *FROM table WHERE cid IN ( 1, 3 )");
echo "<table border='1'>
<tr>
<th>id</th>
<th>name</th>
<th>score</th>
<th>grade</th>
</tr>";
while($row=mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>".$row['id']."</td>";
echo "<td>".$row['name']."</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($conn);
?>
你这个是按照cid来分类的吧，PHP使用foreach循环，判断cid，cid相同的放在一起就行了