mysql - 如何创建子查询

Question

如何将 2 个 mysql 语句合并为一个语句。

这些是我的陈述

MYSQL 1

SELECT et.imei as IMEI, MAX(FROM_UNIXTIME(et.timestamp)) as TID
FROM exp_terminal_log as et
group by et.imei
order by TID desc;

MYSQL 1 输出

IMEI              LATEST DATE
351895053434419   2013-04-28 11:12:28
354851057203265   2013-04-28 11:10:44
354851057234989   2013-04-28 11:10:32

MYSQL 2

SELECT ct.title AS TITLE, t.phoneid as IMEI
FROM transactions AS t
inner JOIN exp_channel_titles AS ct ON (ct.entry_id = t.restaurant_id)
WHERE t.cardid != '88888888' AND t.cardid > 0
AND ct.status= 'open'
GROUP by ct.entry_id 

MYSQL 2 输出

TITLE       IMEI
Café Katz   351895053434419
Restaurant1 354851057203265
Restaurant2 354851057234989

期望的输出

TITLE       IMEI               LATEST DATE
Café Katz   351895053434419    2013-04-28 11:12:28
Restaurant1 354851057203265    2013-04-28 11:10:44
Restaurant2 354851057234989    2013-04-28 11:10:32

这是尝试过的方法，但这不起作用，因为我的子查询返回不止一行。

SELECT et.imei as IMEI, MAX(FROM_UNIXTIME(et.timestamp)) as TID
FROM exp_terminal_log as et
group by et.imei
having et.imei = (

SELECT t.phoneid as IMEI
FROM transactions AS t
inner JOIN exp_channel_titles AS ct ON (ct.entry_id = t.restaurant_id)
WHERE t.cardid != '88888888' AND t.cardid > 0
AND ct.status= 'open'
GROUP by ct.entry_id )

Answer 1

试试这个:

SELECT 
  ct.title AS TITLE, 
  t.phoneid as IMEI,
  t2.TID
FROM transactions AS t
inner JOIN exp_channel_titles AS ct ON ct.entry_id = t.restaurant_id
INNER JOIN
(
  SELECT et.imei as IMEI, MAX(FROM_UNIXTIME(et.timestamp)) as TID
  FROM exp_terminal_log as et
  group by et.imei
) AS t2 ON t.phoneid = t2.IMEI
WHERE t.cardid != '88888888' 
  AND t.cardid > 0
  AND ct.status= 'open'
GROUP by ct.entry_id;