一条多表联查SQL语句优化的问题

[mechille]

SELECT *,
(SELECT name FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as name,
(SELECT detail FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as detail
FROM tbl_a
WHERE id1='1';

暂且无视那个*号。请问这个语句怎么优化到最佳

[lenmore]

SQL Server可以这样写的，减少一半IO：
SELECT *
FROM tbl_a
CROSS APPLY (SELECT TOP (1) name, detail FROM tbl_b WHERE id1=tbl_a.id1 AND id2=tbl_a.id2 ORDER BY date DESC) AS T
WHERE id1='1'

[cxe2v]

我能看出来的也就是楼上那种，把后面两个括号里的select写成一句

[mechille]

@lenmore 呃，如果是MYSQL呢，貌似MYSQL没有CROSS APPLY这样的语句

[yangqi]

你这不就是一个简单的Join么？还是我理解错了？

SELECT tbl_a.*, tbl_b.name, tbl_b.detail
FROM tbl_a
JOIN tbl_b ON tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2
WHERE tbl_a.id1='1'
ORDER BY tbl_b.date DESC LIMIT 1

[cjjer]

@yangqi

仿佛你理解错了。

楼主获取的是top 1，多条记录的话就。。。

我不折腾的试了。

楼主可以试试先主表 inner join一下得到表x，然后主表left join这个表x。。。。

[yangqi]

@cjjer 没错啊，我给出的query里也是top 1 order by date啊。。。感觉楼主简单问题复杂化了吧。。。

[yangqi]

@cjjer 好吧，之前是理解错了，楼主中间俩个select为啥要分开，其实就是一条query

SELECT *, tmp.name, tmp.detail
FROM tbl_a,
(SELECT name, detail
FROM tbl_b
WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2
ORDER BY date DESC LIMIT 1
) as tmp,
WHERE tbl_a.id1='1';

[mechille]

@yangqi

严格按照你这个语句写的，结果

1054 Unknown column 'tbl_a.id1' in 'where clause'

[yangqi]

@mechille

SELECT *, tmp.name, tmp.detail
FROM tbl_a,
(SELECT name, detail
FROM tbl_b
JOIN tbl_a
ON tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2
ORDER BY date DESC LIMIT 1
) as tmp
WHERE tbl_a.id1='1';