php - MySQL 使用过滤器、COUNT、GROUP BY/LIMIT/ORDER BY 和子查询查询 HTML 表

Question

我收到一个让我头疼了大约 2 天的查询，我需要一些帮助。请注意!

我需要做的是获取下表中描述的数据，并且输出必须使用客户(名称、codeX/Y/Z)、应用程序(名称、wave)、迁移标签上的过滤器显示每个应用程序的每条消息。标签，migrationMessage。(日期)使用分页(每页 50 个)。

每个应用程序可以显示 0 到 n 条消息，我必须使用属性“rowspan”制作一个 HTML 表格。

以下是表格:

“#:PK”

“@:FK”

• 应用程序(#id，标签，@customerId)
• 客户(#id、姓名、代码X、代码Y、代码Z)
• erpApplication(#id, label, @customerId)
• migrationMessage(@applicationId、creationTime、closureDate、previsionalDate、@labelId、@authorId)
• migrationLabel(#id, 标签, 排序)
• 作品(@employeeId、@applicationId、@roleId)
• 员工(#id，用户名)

这是当前的查询:

<?php
$query = 'SELECT 
        c.id AS customerId, c.name AS customerName, c.X3Code, c.mfgProCode, c.as400Code, c.jdeCode,
        a.id AS appId, a.label AS appName, a.wave,
        mm.closureDate, mm.previsionalDate, mm.content,
        ml.id, ml.label,
        q.msgCount
        FROM (
            SELECT a.id
            FROM `application` a
            JOIN `customer` c ON c.id = a.customerId
            ORDER BY c.name
        ) l
        JOIN application a ON a.id = l.id
        JOIN `customer` c ON c.id = a.customerId
        LEFT JOIN (
            SELECT applicationId, COUNT(*) AS msgCount
            FROM application a
            JOIN migrationMessage mm ON mm.applicationId = a.id
            JOIN migrationLabel ml ON ml.id = mm.labelId
            JOIN customer c ON c.id = a.customerId
            ' . $where . '
            GROUP BY applicationId
            LIMIT ' . $limit . ' OFFSET ' . $offset . '
        ) q ON q.applicationId = a.id
        LEFT JOIN `migrationMessage`mm ON mm.applicationId = q.applicationId
        LEFT JOIN `migrationLabel` ml ON ml.id = mm.labelId
        JOIN `works` w ON w.applicationId = a.id '
        . ($user->isOperator() ? ' AND w.roleId = ' . Role::Rri : ' AND w.employeeId = ' . $user->id)
    . ' JOIN employee e ON e.id = w.employeeId'
        . ($user->isJustUser() ? ' AND e.siteId = ' . $user->siteId : '')
        . $where
        . ' ORDER BY c.name, a.label, ml.ordering'
        . 'LIMIT ' . $limit . ' OFFSET ' . $offset;

几乎所有事情都可以使用这个查询，当我得到一个包含多条消息的应用程序时，问题就发生了，这些消息本身占据了两页......例如，“rowspan”(查询中的 msgCount)为 5，但最后 3 条消息与前 2 条消息位于不同的页面上，然后 HTML 显示完全错误。 See HTML result

Answer 1

我从头开始编写查询，得到的结果完全符合我的需求。

对于那些想知道的人，关键是 RIGHT JOIN'ed 查询:

<?php
$query = 'SELECT
        c.id AS customerId, c.name AS customerName, c.X3Code, c.mfgProCode, c.as400Code, c.jdeCode,
        a.id AS appId, a.label AS appName, a.wave,
        mm.closureDate, mm.previsionalDate, mm.content,
        ml.id, ml.label,
        q0.msgCount
        FROM application a
        JOIN customer c ON c.id = a.customerId
        LEFT JOIN migrationMessage mm ON mm.applicationId = a.id
        LEFT JOIN migrationLabel ml ON ml.id = mm.labelId
        JOIN `works` w ON w.applicationId = a.id '
        . ($user->isOperator() ? ' AND w.roleId = ' . Role::Rri : ' AND w.employeeId = ' . $user->id)
    . ' JOIN employee e ON e.id = w.employeeId'
        . ($user->isJustUser() ? ' AND e.siteId = ' . $user->siteId : '')

    . ' LEFT JOIN (
        SELECT applicationId, COUNT(*) AS msgCount
        FROM application a
        JOIN migrationMessage mm ON mm.applicationId = a.id
        JOIN migrationLabel ml ON ml.id = mm.labelId
        JOIN customer c ON c.id = a.customerId
        GROUP BY applicationId
    ) q0 ON q0.applicationId = a.id
    RIGHT JOIN (
        SELECT a.id
        FROM application a
        JOIN customer c ON c.id = a.customerId
        LEFT JOIN migrationMessage mm ON mm.applicationId = a.id
        LEFT JOIN migrationLabel ml ON ml.id = mm.labelId
        ' . $where . '
        GROUP BY a.id
        ORDER BY c.name, a.label, ml.ordering
        LIMIT ' . $limit . ' OFFSET ' . $offset . '
    ) q1 ON q1.id = a.id
    ORDER BY c.name, a.label, ml.ordering'