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

将两个查询与单独的索引组合在一起

mysql

rborum · 技术社区 · 8 年前

我有两个查询从两个不同的表中提取数据,但我需要它们在同一个报表中提取数据。我在它们之间有一个共享密钥,第一个表有一个条目对应于第二个表中的许多条目。

我的第一个问题:

SELECT Proposal_ID,
    substr(Proposal_Name, 1, 3) AS Prefix,
    substr(Proposal_Name, 4, 6) AS `Number`,
    Institution,
    CollegeCode,
    DepartmentCode,
    Proposer_FirstName,
    Proposer_LastName

FROM proposals.proposal
WHERE Institution = 'T';

样本数据:

+----+--------+--------+-------+----------+----------+-----------+----------+
| ID | Prefix | Number | Inst. | CollCode | DeptCode | FirstName | LastName |
+----+--------+--------+-------+----------+----------+-----------+----------+
| 18 | SYP    | 4675   | T     | AS       | SOC      | Linda     | McGaff   |
+----+--------+--------+-------+----------+----------+-----------+----------+
| 20 | GEO    | 4340   | T     | AS       | SGS      | Teddy     | Graham   |
+----+--------+--------+-------+----------+----------+-----------+----------+

我的第二个问题:

SELECT Parent_Proposal,
    SUBSTRING_INDEX(GROUP_CONCAT(`status`.`Status_Code` ORDER BY `status`.`Status_Time` DESC), ',', 1) AS status_code,
    SUBSTRING_INDEX(GROUP_CONCAT(`status`.`Status_Time` ORDER BY `status`.`Status_Time` DESC), ',', 1) AS status_timestamp
FROM proposals.`status`    
GROUP BY `status`.Parent_Proposal

样本数据:

+-----------------+-------------+----------------------+
| Parent_Proposal | Status_Code | Status_Time          |
+-----------------+-------------+----------------------+
| 18              | 40          | 2016-11-09 06:30:35  |
+-----------------+-------------+----------------------+
| 20              | 11          | 2017-03-20 10:26:31  |
+-----------------+-------------+----------------------+

我基本上需要根据status_timestamp提取最新的status_代码和status_timestamp,然后将其与第一个带有parent_proposal列的表关联起来。

有没有办法在不将所有数据分组的情况下对结果的子集进行分组?

预期结果:

+----+--------+--------+-------+----------+----------+-------+--------+-------------+----------------------+
| ID | Prefix | Number | Inst. | CollCode | DeptCode | FName | LName  | Status_Code | Status_Time          |
+----+--------+--------+-------+----------+----------+-------+--------+-------------+----------------------+
| 18 | SYP    | 4675   | T     | AS       | SOC      | Linda | McGaff | 40          | 2016-11-09 06:30:35  |
+----+--------+--------+-------+----------+----------+-------+--------+-------------+----------------------+
| 20 | 11     | GEO    | 4340  | AS       | SGS      | Teddy | Graham | 11          | 2017-03-20 10:26:31  |
+----+--------+--------+-------+----------+----------+-------+--------+-------------+----------------------+

谢谢你的帮助和洞察力!

1 回复 | 直到 8 年前

Tim Biegeleisen 8 年前

我想你想要这个。只需将两个表连接在一起,然后对 status 表以查找每个父方案的最新记录。

SELECT
    p.Proposal_ID,
    SUBSTR(p.Proposal_Name, 1, 3) AS Prefix,
    SUBSTR(p.Proposal_Name, 4, 6) AS Number,
    p.Institution,
    p.CollegeCode,
    p.DepartmentCode,
    p.Proposer_FirstName,
    p.Proposer_LastName,
    s1.Status_Code,
    s1.Status_Time 
FROM proposals.proposal p
LEFT JOIN proposals.status s1
    ON p.ID = s1.Parent_Proposal
INNER JOIN
(
    SELECT Parent_Proposal, MAX(Status_Time) AS Max_Status_Time
    FROM proposals.status
    GROUP BY Parent_Proposal
) s2
    ON s1.Parent_Proposal = s2.Parent_Proposal AND s1.Status_Time = s2.Max_Status_Time
WHERE
    p.Institution = 'T';