显示每辆自行车的最长距离，如果超过100，则显示绿色图标 [英] showing the longest distance for each bike and a green icon if over 100

问题描述

我在

I asked a simpler version of this question at date and miles when over 100, otherwise return red traffic light and then realized I oversimplified my query.

我问了一个新问题，因为为我的原始问题提供了解决方案，但是我问错了问题.

I asked a new question because a solution was provided to my original question, but I asked the wrong question.

这是杰斯特:

我今年的愚蠢目标是每辆自行车骑一个世纪.

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

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

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 r.ride_user = '$athlete' and b.bike_retired = 'No' AND ride_miles > 100

我尝试了另一个问题的JOINS并将我的where子句添加到联接中.如果我是桌子上的唯一用户，那么他们的表现很好，而且我不必检查自行车是否已退役.一旦添加了这些部分，一切都弄糟了，我又迷失了.

I experimented with the JOINS of my other question and adding my where clause to the join. They worked good if I was the only user in the table and I didn't have to check if the bike was retired. Once I added those portions it got all messed up and once again I'm lost.

一旦我能取回正确的数据，就可以使用PHP对其进行按摩.我一直在想我需要一个ORDER BY.

Once I can get the right data back I can use PHP to massage it. I keep thinking I need an ORDER BY.

推荐答案

使用IF根据是否满足阈值返回不同的颜色.

Use IF to return a different color depending on whether the threshold has been met.

SELECT r.bike_name, r.ride_date, r.ride_miles,
       IF(r.ride_miles > 100, 'green', 'red') AS color
FROM rides AS r
JOIN bikes AS b ON r.bike_name = b.bike_name
WHERE YEAR(r.ride_date) = $current_year
  AND r.ride_user = '$athlete'
  AND b.bike_retired = 'No'

这篇关于显示每辆自行车的最长距离，如果超过100，则显示绿色图标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！