PHP MYSQL 查询连接表以获取父表查询的最后一行

Question

我有两个表:ticket 和 history_ticket

table 票:

ticket_id  | ticket_desc   
 1         |  software   
 2         |  hardware   
 3         |  other

表 history_ticket :

history_id | ticket_id | message  | status    
 1         |  1        |  text    |  process 
 2         |  2        |  text    |  solve   
 3         |  3        |  text    |  process
 4         |  3        |  text    |  solve

我想要这样的结果

ticket_id  | ticket_desc  | status 
 1         |  software    |  process
 2         |  hardware    |  solve
 3         |  other       |  solve

我尝试了各种连接和子选择，但没有成功

任何帮助/指导将不胜感激!

更新:如果我像这样改变结果会怎样

ticket_id  | ticket_desc  | last_status | count_message
     1     |  software    |  process    |     1
     2     |  hardware    |  solve      |     1
     3     |  other       |  solve      |     2

Answer 1

试试这个:

SELECT
  t.ticket_id, 
  ticket_desc, 
  ht.status
FROM ticket AS t
INNER JOIN history_ticket AS ht ON t.ticket_id = ht.ticket_id
INNER JOIN
(
   SELECT ticket_id, MAX(history_id) maxid
   FROM history_ticket
   GROUP BY ticket_id
) AS ht2 ON ht.history_id = ht2.maxid;

SQL Fiddle Demo

这会给你:

| TICKET_ID | TICKET_DESC |  STATUS |
-------------------------------------
|         1 |    software | process |
|         2 |    hardware |   solve |
|         3 |     Problem |   solve |

---

更新 1

要获取每张票的消息数，您只需在子查询中包含 COUNT(history_id) AS sum_message，如下所示:

SELECT
  t.ticket_id, 
  ticket_desc, 
  ht.status, 
  ht2.sum_message
FROM ticket AS t
INNER JOIN history_ticket ht ON t.ticket_id = ht.ticket_id
INNER JOIN
(
   SELECT 
     ticket_id, 
     MAX(history_id) maxid, 
     COUNT(history_id) AS sum_message
   FROM history_ticket
   GROUP BY ticket_id
) AS ht2 ON ht.history_id = ht2.maxid;

Updated SQL Fiddle Demo

这会给你:

| TICKET_ID | TICKET_DESC |  STATUS | SUM_MESSAGE |
---------------------------------------------------
|         1 |    software | process |           1 |
|         2 |    hardware |   solve |           1 |
|         3 |     Problem |   solve |           2 |

---

更新2

如果您想为 ids divisi_id 选择名称，对于简单的值，您可以为此使用 CASE 表达式:

SELECT
  t.ticket_id, 
  ticket_desc, 
  CASE 
    WHEN t.divisi_id = 101 THEN 'Divisi A'
    WHEN t.divisi_id = 102 THEN 'Divisi B'
  END AS 'Divisi',
  ht.status, 
  ht2.sum_message
FROM ticket AS t
INNER JOIN history_ticket ht ON t.ticket_id = ht.hticket_id
INNER JOIN
(
   SELECT hticket_id, MAX(history_id) maxid, COUNT(history_id) AS sum_message
   FROM history_ticket
   GROUP BY hticket_id
) AS ht2 ON ht.history_id = ht2.maxid;

Updated SQL Fiddle Demo

这会给你:

| TICKET_ID | TICKET_DESC |   DIVISI |  STATUS | SUM_MESSAGE |
--------------------------------------------------------------
|         1 |    software | Divisi A | process |           1 |
|         2 |    hardware | Divisi B |   solve |           1 |
|         3 |     Problem | Divisi A |   solve |           2 |

对于多个值，您可以将它们放在临时表中，或者您可以在子查询中选择它们并加入表以获取名称，如下所示:

SELECT
  t.ticket_id, 
  ticket_desc, 
  d.Divisi,
  ht.status, 
  ht2.sum_message
FROM ticket AS t
INNER JOIN history_ticket ht ON t.ticket_id = ht.hticket_id
INNER JOIN
(
   SELECT hticket_id, MAX(history_id) maxid, COUNT(history_id) AS sum_message
   FROM history_ticket
   GROUP BY hticket_id
) AS ht2 ON ht.history_id = ht2.maxid
INNER JOIN
(
  SELECT 101 AS divisi_id, 'Divisi A' AS Divisi
  UNION ALL
  SELECT 102             , 'Divisi B'
  ... -- here you put other values or you can join a temp table instead
) AS D ON t.divisi_id = D.divisi_id;

Updated SQL Fiddle Demo