mysql - 需要帮助进行一个 mysql 查询以获得满足我要求的预期结果

Question

我在我的范围内编写 mysql 查询以获得结果时遇到了一些问题。实际上，我使用这个现有查询得到了适当的结果，但它的编写方式不正确。这是我的查询:

    SELECT c.ID, c.chn_name,c.chn_logo, 
    (SELECT ID FROM tv_showtime WHERE showtime<='2013-02-18 10:28:35' AND status='Enable' AND chn_id=c.ID ORDER BY ID DESC Limit 0,1) as currentshowid, 
    (SELECT tv_showtime FROM tv_showtime WHERE showtime<='2013-02-18 10:28:35' AND status='Enable' AND chn_id=c.ID ORDER BY ID DESC Limit 0,1) as currentshowtime , 
    (SELECT tv_showtime FROM tv_showtime WHERE showtime >'2013-02-18 10:28:35' AND status='Enable' AND chn_id=c.ID ORDER BY ID ASC Limit 0,1) as nextshowtime 
    FROM tv_channels AS c 
    WHERE c.status="Enable" 
    ORDER BY c.chn_name 
    LIMIT 0,10

这里只有两个表，名为“tv_channels”和“tv_showtime”。我一次需要为每个 channel 记录一条记录(当前时间)。所以这里假设每个 channel 有 12 个 channel 和大约 30 个(每个 channel 可能不同)记录，我只需要显示当前节目的 channel (更多说明:只会显示具有当前节目时间和/或下一个节目时间的 channel 。 )

问题:我需要来自“tv_showtime”的更多字段值来显示其他需要的值。如果我将使用这种方式，那么我必须编写更多内部选择查询，这会减慢我的网站加载速度。那么，您能否建议或建议任何其他方式来编写此查询？

数据库表详细信息:

• tv_channels [ID, chn_name, [其他必填字段]],
• tv_showtime [ID, chn_id, showtime, show_name, hits, last_ip [以及其他几个字段]]

如果您需要更多详细信息才能回答这个问题，请告诉我。任何帮助或建议将不胜感激。谢谢。

Answer 1

正如另一个人所问，但你没有回应每场演出的“结束时间”，我不得不以演出时间是开始时间为前提。也就是说，您如何根据 CURTIME()(而不是固定时间值)确定给定 channel 正在运行的当前节目。

获取每个 channel 和当前时间之前存在的最大显示时间...

同样，如何获得NEXT Show？获取每个 channel 的最小显示时间，该时间在当前时间之后开始。

所以，如果我有以下 1 个 channel 的记录并且当前时间是下午 2:15

Channel   ShowTime   Show_Name
1         12:30pm    Show "X"
1         01:00pm    Show "B"
1         01:30pm    Show "C"     
1         02:00pm    Show "D"      <-  Current Show
1         02:30pm    Show "Y"      <-  Next Show
1         03:00pm    Show "Z"

当前正在运行的节目是下午 2:15 之前的最新一集(节目“D”从下午 2 点开始)下一个节目是当前时间之后的第一个节目(节目“Y”从下午 2:30 开始)。即使行不是按顺序排列，上面的方法也能正常工作，因为我分别使用 MIN() 和 MAX() 来获取时间。

因此，我从 channel 表开始，对每个单独的预聚合查询进行左连接，以分别检测当前放映时间和下一个放映时间，并连接到每个最多可以返回一条记录的 channel ID -- - 如果在合格的 WHERE CURTIME() 考虑范围内有记录。

从那以后，我再次将这些结果集重新加入到实际的电视节目表中，但这次是在与当前或下一次相应时间匹配的 channel 和时间上。

所以现在，我已经准备好一切，可以使用相应的内容别名了。现在，我只需捕获我想要展示的栏目。

由于连接都是 LEFT-JOIN，每一侧都可以有 NULL 值，因此您可能需要调整查询以防止使用 COALESCE() 出现空值，例如我已经采样...

SELECT
      TC.ID,
      TC.Chn_Name,
      TC.Chn_Logo,
      COALESCE( CurShowTimeDetail.ShowTime, 'no time' )  CurShowTime,
      COALESCE( CurShowTimeDetail.Show_Name, '' )        CurShowName,
      COALESCE( CurShowTimeDetail.Hits, 0 )              CurHits,
      COALESCE( NextShowTimeDetail.ShowTime, 'no time' ) NextShowTime,
      COALESCE( NextShowTimeDetail.Show_Name, '' )       NextShowName,
      COALESCE( NextShowTimeDetail.Hits, 0 )             NextHits
   from 
      TV_Channels TC

         LEFT JOIN ( SELECT 
                           ST.chn_id, 
                           MAX( ST.showtime ) CurShowTime
                        from
                           tv_showtime ST
                        where
                           ST.ShowTime < CURTIME()
                        group by
                           ST.chn_id ) CurrentShow
            ON TC.ID = CurrentShow.Chn_ID
            LEFT JOIN tv_showtime CurShowTimeDetail
               ON CurrentShow.Chn_ID = CurShowTimeDetail.Chn_ID
               AND CurrentShow.CurShowTime = CurShowTimeDetail.ShowTime

         LEFT JOIN ( SELECT 
                           ST.chn_id, 
                           MIN( ST.showtime ) NextShowTime
                        from
                           tv_showtime ST
                        where
                           ST.ShowTime > CURTIME()
                        group by
                           ST.chn_id ) NextShow
            ON TC.ID = NextShow.Chn_ID
            LEFT JOIN tv_showtime NextShowTimeDetail
               ON NextShow.Chn_ID = NextShowTimeDetail.Chn_ID
               AND NextShow.NextShowTime = NextShowTimeDetail.ShowTime