代码之家 › 专栏 › 技术社区 › natsu1627

从输出表中删除重复行

html-table inner-join sql php

0

natsu1627 · 技术社区 · 11 年前

我想删除表中的重复输出,表如下所示

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

订单号和客户名称使用rowspan=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>';
}

上述代码的输出是第一个示例

如何实现第二个示例,我的表关系是否有问题,或者我应该使用正确的查询?

4 回复 | 直到 11 年前

1

palavesa muthu 11 年前

正如jarlh所说的,这不是dbms的事情。要做到这一点,请使用order by order no编辑查询,并使用下面的代码作为第二个示例

$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'];
}

2

0

MegaAppBear 11 年前

您最好使用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>';
}

在这种情况下,您不需要rowspan=2。

3

0

Stanislovas KalaÅ¡nikovas 11 年前

如果数据不是动态的,可以使用以下方法:

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

4

0

amirdavod 11 年前

检查此查询:

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))