dtc88867 2012-07-09 16:46
浏览 28
已采纳

创建一个结合了MySQL中几个查询结果的结果集?

This is my scenario: I have two different tables: Table 'subscriptions' contains all the information related to subscriptions to user sites, and the 'ratings' table, which contains scores to products of subscribing sites. Simplifying is something like that:

Subscriptions: 'id','user','place','created', etc.
Ratings: 'id','user','product','created',etc.

Now I need mysql to return my most recent 10 items, either a subscription or a rating. So far, the solution found was to make two different calls:

SELECT * FROM subscriptions WHERE ... ORDER BY created LIMIT 10. 

With the two results (20 elements), I order multidimensional array by 'created' and get the top 10 with (saving the result in $items):

$items = array_slice($items, 0, 10);

I suspect that this solution is not efficient, because I get it in two calls, and return more elemnts than I need. I've been reading that the UNION operator allows you to select several different tables at once, but with the same number of columns. My question is, what is the most efficient way to join a specific number of elements of different types ordered by a common field (created in my case) in just one call? is ot possible? Thanks a lot, any help is appreciated, Jesús.

  • 写回答

3条回答 默认 最新

  • dongnai3960 2012-07-09 16:47
    关注

    UNION is required.

    You can learn how to use it here:

    Join two tables, then Order By date, BUT combining both tables

    It's not explained in the link I posted, but can put a LIMIT on a UNION as well. Combined with an ORDER BY this is what you need. (cred to Frits van Campen)

    (SELECT cols FROM subscriptions) 
    UNION 
    (SELECT cols FROM ratings);
    ORDER BY created LIMIT 10
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 R语言Rstudio突然无法启动
  • ¥15 关于#matlab#的问题:提取2个图像的变量作为另外一个图像像元的移动量,计算新的位置创建新的图像并提取第二个图像的变量到新的图像
  • ¥15 改算法,照着压缩包里边,参考其他代码封装的格式 写到main函数里
  • ¥15 用windows做服务的同志有吗
  • ¥60 求一个简单的网页(标签-安全|关键词-上传)
  • ¥35 lstm时间序列共享单车预测,loss值优化,参数优化算法
  • ¥15 Python中的request,如何使用ssr节点,通过代理requests网页。本人在泰国,需要用大陆ip才能玩网页游戏,合法合规。
  • ¥100 为什么这个恒流源电路不能恒流?
  • ¥15 有偿求跨组件数据流路径图
  • ¥15 写一个方法checkPerson,入参实体类Person,出参布尔值