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

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';

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

tbl

id1

id2

9 条回复 • 1970-01-01 08:00:00 +08:00

lenmore

2014-01-23 18:38:14 +08:00

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

2014-01-23 21:00:30 +08:00

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

mechille

2014-01-23 22:08:13 +08:00

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

yangqi

2014-01-23 22:47:46 +08:00

你这不就是一个简单的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

2014-01-24 00:32:09 +08:00

@yangqi

仿佛你理解错了。

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

我不折腾的试了。

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

yangqi

2014-01-24 00:38:45 +08:00

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

yangqi

2014-01-24 00:47:54 +08:00

@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

2014-01-24 10:24:41 +08:00

@yangqi

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

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

yangqi

2014-01-24 12:55:44 +08:00

@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';