开发者

how to combine these queries

and get meaningful results.

Currently I am running these three queries:

  SELEC开发者_如何学GoT t.type,t.id,s.title FROM db1.tags t INNER JOIN db1.st s ON s.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='s' ORDER BY tag desc LIMIT 0, 19
  SELECT t.type,t.id,v.title FROM db1.tags t INNER JOIN db1.vi v ON v.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='v' ORDER BY tag desc LIMIT 0, 19
  SELECT t.type,t.id,i.ca AS title FROM db1.tags t INNER JOIN db2.tablename i ON i.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='i' ORDER BY tag desc LIMIT 0, 19

then trying to combine the data results but what I would really prefer is if I could combine them into a single query.

Any thoughts?


You can use UNION ALL:

SELECT * FROM (
    SELECT t.type,t.id,s.title
    FROM db1.tags t
    INNER JOIN db1.st s ON s.id=t.id
    WHERE id LIKE '%%' AND t.tag='foo' AND t.type='s'
    ORDER BY tag DESC
    LIMIT 0, 19
) AS T1
UNION ALL
SELECT * FROM (
    SELECT t.type,t.id,v.title
    FROM db1.tags t
    INNER JOIN db1.vi v ON v.id=t.id
    WHERE id LIKE '%%' AND t.tag='foo' AND t.type='v'
    ORDER BY tag DESC
    LIMIT 0, 19
) AS T2
UNION ALL
SELECT * FROM (
    SELECT t.type,t.id,i.ca AS title
    FROM db1.tags t
    INNER JOIN db2.tablename i ON i.id=t.id
    WHERE id LIKE '%%' AND t.tag='foo' AND t.type='i'
    ORDER BY tag DESC
    LIMIT 0, 19
) AS T3
ORDER BY type, id DESC


you should consider using UNION. since you always return the 3 same fields type/id/title

this would also make 3 internal requests to the database , however, you will be able to limit the results and order the full aggregated result.


If you don't want to use Unions you could try using outer joins. Add the t.type=something in the on clause. Finally you'd have to use a case statement to select the title. Not really easier than the Union approach but it might be faster since it would just be one call.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜