SQL:如何为其他表上的每条记录选择具有最近过去日期的记录 [英] SQL: How to select the record with the most recent past date for every record on other table

问题描述

如何在MySQL的单个查询中重新构造这两个查询?

How can I reformulate this two queries in one single query on MySQL?

SELECT * FROM tvnetwork  
//this query gives me a list of TV networks

但是网络通常会更改名称和徽标，因此对于每个检索到的电视网络:

But networks usually change names and logos, so for each of the retrieved TV networks:

SELECT name, logo FROM tvnetworkinfo 
  WHERE id = $tvnetwork.id 
  AND date_since < NOW()
    ORDER BY date_since desc LIMIT 1

//this one gives me the most recent logo and name for the network

我特意省去了下一个名称/徽标更改.例如.我希望使用"NatGeo"代替以前的国家地理"，但我也希望使用"SciFi"而不是尚未实现的"SyFy".

I intentionally leave out the NEXT name/logo change. E.g. I want "NatGeo" instead of the old "National Geographic", but I also want "SciFi" instead of the not yet implemented "SyFy".

我想在一个查询中检索所有内容.有什么办法吗?

I'd like to retrieve everything in a single query. ¿Is there any way to do that?

推荐答案

获取最新的网络名称列表&徽标，使用:

To get the most recent list of network names & logos, use:

SELECT x.name,
       x.logo
  FROM (SELECT tni.name,
               tni.logo
               CASE 
                 WHEN @name = tni.name THEN @rownum := @rownum + 1 
                 ELSE @rownum := 1
               END AS rank
               @name := tni.name
          FROM TVNETWORKINFO tni
       -- JOIN TVNETWORK tn ON tn.id = tni.id
          JOIN (SELECT @rownum := 0, @name := '') r
         WHERE tni.date_since < NOW()
      ORDER BY tni.name, tni.date_since DESC) x
 WHERE x.rank = 1

我忽略了对TVNETWORK表的JOIN，它似乎对于输出不是必需的.通过在我提供的查询中删除双连字符来取消注释.

I disregarded the JOIN to the TVNETWORK table, it didn't seem to be necessary to the output. Uncomment it by removing the double hyphen before it in the query I provided.

这篇关于SQL:如何为其他表上的每条记录选择具有最近过去日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！