超过100的日期和里程，否则返回红灯 [英] date and miles when over 100, otherwise return red traffic light

问题描述

这应该很简单，我一开始以为很简单，但现在我陷入了僵局.

This should be simple, and I started out thinking it was, but now I'm at an impasse.

背景，我有一个带桌子的数据库，我可以在其中存放我当前的自行车.我还有一张桌子，用来存放我当前的游乐设施.我通常将 bike_name 列上的两个表连接起来，如下面的查询所示.

The background, I have a DB with a table where I store my current bikes. I also have a table where I store my current rides. I usually join the two tables on the bike_name column as you'll see in my query below.

今年我有一个愚蠢的目标:在每辆自行车上骑一个世纪.

I had the silly goal of riding a century on each bike this year.

1. 我想创建一个状态仪表板，每辆自行车都有一个图标表示已完成，绿色表示是，红色表示否.

1. I want to create a status dashboard that has each bike with an icon indicating it's been done, green for yes, red for no.

另外，如果是，我想填充ride_date和ride_miles.

Additionally, if it is yes, I want to populate the ride_date and ride_miles.

第二个条件很容易做到，我的 SQL 查询是:

The second criteria is easy enough to do, the SQL query I have is:

SELECT r.bike_name,
       r.ride_date,
       r.ride_miles
  FROM rides r
       JOIN bikes b
            ON r.bike_name=b.bike_name
 WHERE DATE_FORMAT(r.ride_date, '%Y')='$current_year' 
       AND ride_miles > 100

这样做的问题是它只会拉回超过 100 次的骑行(和自行车).我可以按摩数据以制作一个红色图标并发布相关信息.我无法为没有 100 的那些填充红色图标，因为我什至不会通过查询了解它们.我一直坐在这里试图思考这个问题，但想不出一个好的解决方案.我今年刚开始接触 PHP，虽然我已经取得了很多成就，但我想有时我可能会以令人费解的方式来做.

The problem with this is it will only pull back rides (and the bikes) over 100. I can massage the data to make a red icon and post the relevant info. I can't populate the red icon for ones without 100 because I won't even know about them with my query. I've been sitting here trying to think about this and can't come up with a good solution. I just started messing with PHP this year, and while I've accomplished a lot, I think sometimes I might do it in convoluted ways.

我的想法是我可以将 SQL 重写为 ORDER BY 每辆自行车的最长骑行时间，获取该数据，然后使用 PHP 中的逻辑来填充我的信息......嗯，也许就是这样我需要做什么...

The thought I had was that I could rewrite the SQL to ORDER BY longest ride for each bike, get that data, then use logic within PHP to populate my info... Hmm, maybe that's what I need to do...

推荐答案

您可以执行 LEFT OUTER JOIN 以便它只返回超过 100 英里的行程的行程信息，但我认为您可能想要分解查询，以便您拥有有一个世纪的自行车"和这些/所有自行车的骑行".但是为了让您的原始查询更接近我会做类似的事情

You could do a LEFT OUTER JOIN so that it will only return the ride information for rides that are over 100 miles, but I think you might want to break up the query so that you have "bikes with a century on them" and "rides for those/all bikes". But to get your original query closer I would do something like

SELECT b.bike_name, r.ride_date, r.ride_miles
    FROM bikes b
    LEFT OUTER JOIN rides r ON r.bike_name = b.bike_name 
        AND DATE_FORMAT(r.ride_date, '%Y') = '$current_year'
        AND ride_miles > 100

如果ride_date 和ride_miles 为NULL，那么您就知道这辆自行车上从未有过一个世纪.任何至少有一个非 NULLride_date 的自行车都完成了一个世纪.

If the ride_date and ride_miles are NULL then you know the bike never had a century on it. Any bike that has at least one non-NULL ride_date has done a century.

这篇关于超过100的日期和里程，否则返回红灯的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！