我想删除表中的重复输出，表看起来像这个
Order No Customer Name Order
1001 John Milk
1001 John Egg
1002 Mark Milk
1002 Mark Bread
我希望它像这个
Order No Customer Name Order
1001 John Milk
Egg
1002 Mark Milk
Bread
订单号和客户名称使用行span=2
客户和订单来自我的数据库中的不同表格
该表的查询来自内部联接查询联接两个
$query = mysql_query("select customer.customerName, order.orderNo, order.item
from customer inner join orderInfo on orderInfo.customerId = customer.customerId");
while($order = mysql_fetch_assoc($query))
{
echo '<tr>
<td>'.$order['orderNo'].'</td>
<td>'.$order['customerName'].'</td>
<td>'.$order['item'].'</td>
</tr>';
}
并且上述代码的输出是第一个图示
我将如何实现第二个示例，我的表关系是否有问题，或者我应该使用正确的查询？
正如jarlh所说，这不是dbms的事情，按订单号编辑查询，并使用下面的代码进行第二张插图
$previous=-1000;
while ($row = mysql_fetch_assoc($query)
{
if($previous!=$row['orderNo'])
{
echo "<tr><td>".$row['orderNo']."</td>";
echo "<td>".$row['customerName']."</td>";
echo "<td>".$row['item']."</td>";
}
else
{
echo "<tr><td></td><td></td><td>".$row['item']."</td>";
}
$previous=$row['orderNo'];
}
您最好使用group_concat:
$query = mysql_query("
SELECT
customer.customerId, customer.customerName, order.orderNo, group_concat(order.item SEPARATOR ',') as order_items
FROM
customer
INNER JOIN
orderInfo on orderInfo.customerId = customer.customerId
GROUP BY customer.customerId
");
在您的代码中，只需替换分隔符：
while($order = mysql_fetch_assoc($query))
{
echo '<tr>
<td>'.$order['orderNo'].'</td>
<td>'.$order['customerName'].'</td>
<td>'.str_replace(',','<br/>',$order['item']).'</td>
</tr>';
}
在这种情况下，您不需要span=2行。
如果您的数据不是动态的，您可以使用以下内容：
DECLARE @varTable TABLE (OrderNo NVARCHAR(10), CustomerName NVARCHAR(60), Order NVARCHAR(60))
INSERT INTO @varTable VALUES( CAST(1001 AS NVARCHAR(10)), 'John', 'Milk')
INSERT INTO @varTable VALUES( '', '', 'Egg')
INSERT INTO @varTable VALUES( CAST(1002 AS NVARCHAR(10)), 'Mark', 'Milk')
INSERT INTO @varTable VALUES( '', '', 'Bread')
SELECT * FROM @varTable
检查此查询：
Select Dep, Case
When rn = 1 And c_d > 1 Then
d
When c_d = 1 Then
d
Else
Null
End D1,
Case
When rn = 1 And c_k > 1 Then
k
When c_k = 1 Then
k
Else
Null
End K1,
Case
When rn = 1 And c_m > 1 Then
m
When c_m = 1 Then
m
Else
Null
End M1
From (Select Count(1) Over(Partition By d,dep Order By d) c_d,
Count(1) Over(Partition By k,dep Order By k) c_k,
Count(1) Over(Partition By m,dep Order By m) c_m,
row_number() Over(Partition By dep Order By dep)rn,
d,
k,
m,
Dep
From (Select 'ali' d, '1000' k, 'M' m, 'x' Dep
From Dual
Union
Select 'ali' d, '1000' k, 'H' m, 'x' Dep
From Dual
Union
Select 'ali' d, '1001' k, 'M' m, 'y' Dep
From Dual
Union
Select 'ali' d, '1000' k, 'H' m, 'y' Dep
From Dual))