MYSQL查询包括“表1”中所有“表2”

Question

我有这个php导出日期到一个csv文件：

$link = mysql_connect($host, $user, $pass) or die("No se pudo conectar." . mysql_error());
mysql_select_db($db) or die ("No se pudo conectar.");

$csv_output = "Creado el, Id de Ticket, Departamento, Tema de Ayuda, Prioridad, Nombre de Staff, Apellido de Staff, Nombre del cliente, Email del cliente, Asunto, Telefono, Extension, Estado de ticket, Direccion, Marca, Tienda, Lugar de Tienda, Codigo del PC, Fecha de compra, Factura, Documento, ID, Celular, Serie del Producto, Estado del Producto, Descripcion(PyP Defectuosa), Serie(PyP Defectuosa), Proveedor(PyP Defectuosa), Factura(PyP Defectuosa), Guia(PyP Defectuosa), Fecha(PyP Defectuosa), Garantía(PyP Defectuosa), Cantidad(PyP Defectuosa), Estado(PyP Defectuosa), Comentario(PyP Defectuosa), Usuario(PyP Defectuosa), Courier(PyP Defectuosa), N°Remito(PyP Defectuosa), Fecha de envio(PyP Defectuosa)";

$csv_output .= "\n";

$query = "(SELECT t1.created, t1.ticket_id, t3.dept_name, t1.helptopic, t1.priority_id, t2.firstname, t2.lastname, t1.name, t1.email, t1.subject, t1.phone, t1.phone_ext, t1.status, t1.address, t1.pcbrand, t1.storeLocation, t1.place, t1.pcCode, t1.purchaseDate, t1.invoice, t1.tipoid, t1.numId, t1.cell_phone, t1.nserieprod, t1.estprod, t4.ticket_id, t4.`desc` , t4.serial, t4.supplier, t4.invoice, t4.guide, t4.date, t4.warranty, t4.volume, t4.state, t4.comment, t4.user, t4.nomcourier, t4.nremito, t4.fenvio
FROM ticket AS t1, staff AS t2, department AS t3, prod_cambio AS t4
WHERE t2.dept_id = t3.dept_id
AND t1.staff_id = t2.staff_id
AND t1.ticket_id = t4.ticket_id

order by t1.ticket_id)";

$result = mysql_query($query);

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_row($result)) {
for ($j = 0; $j < 39; $j++) {
if ($j <= 25){
$csv_output .= $row[$j]. " ,";  
}
$csv_output .= $row[$j]. " ,";
}
$csv_output .= "\n";
}
}
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

我正在选择两个不同的表，并尝试将它们转换为一个表。我的问题是：

Created:            Ticket_id (Table 1):      Dept_name:       Priority_id:            Firstname:              Ticket_id (Table 2): 
2011-11-23 10:04:33     2                    Soporte Lima             2                   Juan Carlos                  2
2011-11-23 10:28:55     3                    Soporte Lima             2                   Antonio Arturo               3
2011-11-23 10:42:07     4                    Soporte Lima             2                   Renzo                        4
2011-11-23 10:44:33     5                    Soporte Lima             2                   Renzo                        5
2011-11-23 10:44:33     5                    Soporte Lima             2                   Renzo                        5
2011-11-23 10:44:33     5                    Soporte Lima             2                   Renzo                        5
2011-11-23 11:00:58     6                    Soporte Lima             2                   Miguel                       6

如果您注意到每次“表2”中的Ticket_id与“表1”匹配时，“表1”都在重复，我如何打印如下内容：

Created:            Ticket_id (Table 1):      Dept_name:       Priority_id:            Firstname:              Ticket_id (Table 2): 
2011-11-23 10:04:33     2                    Soporte Lima             2                   Juan Carlos                  2
2011-11-23 10:28:55     3                    Soporte Lima             2                   Antonio Arturo               3
2011-11-23 10:42:07     4                    Soporte Lima             2                   Renzo                        4
2011-11-23 10:44:33     5                    Soporte Lima             2                   Renzo                        5
                                                                                                                       5
                                                                                                                       5
2011-11-23 11:00:58     6                    Soporte Lima             2                   Miguel                       6

重复时在“表1”部分留出空格，仅打印“表2”列

我希望在"table 1“结果中包含所有"table 2”，但不重复"table "1列的信息，而是留出空格，继续打印"table 2“列。有办法做到这一点吗？

Answer 1

更改输出：

$last_ticket_id = 0;
while ($row = mysql_fetch_row($result)) {
    if ($last_ticket_id == $row[1]) { $print_data = false; } else { $print_data = true; }
    for ($j = 0; $j < 39; $j++) {
        // not understand this but i leav
        if ($j <= 25) { $csv_output .= $row[$j]. " ,"; }
        // check is leav empty or not
        if (!$print_data && in_array($j, array(0,1,2,3,4)) ) {
            $csv_output .= " ,";
        } else {
            $csv_output .= $row[$j]. " ,";
        }
    }
    $csv_output .= "\n";
    $last_ticket_id = $row[1];
}

Answer 2

您是否尝试过选择DISTINCT？从您的示例结果判断，似乎只需添加distinct就可以消除重复行。