dongliaoqiang8524
dongliaoqiang8524
2014-11-25 06:26

不知道如何使用PHP / MySQL处理相当复杂的查询

This query looks like this:

INSERT INTO `results` (`class_id`, `rider_id`, `schedule_id`, `finish`, `points`, `season_id` )
SELECT DISTINCT racedata.class_id, riders.id as rider_id, schedule.id as schedule_id,  racedata.cf_finish, racedata.main_points, schedule.season_id
FROM (racedata LEFT JOIN riders ON racedata.ridername = riders.ridername) 
INNER JOIN `schedule` ON `schedule`.date = STR_TO_DATE(`racedata`.event_date, '%m/%d/%Y') WHERE cf_finish > 0;

I need to change the query so that it uses the current year as a lookup to another table called seasons to get the id associated with the current year. It has fields id and season which contains years.

Right now I store season_id in the schedule table, but don't want to use that field any longer. I"m trying to simplify the front end of the app, which is in turn complicating the backend.

I want to use EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM NOW()) to get the current year, it works in where statements great, but I've not used it in this context before.

Should I be trying to do a nested select inside my select to get the data I want?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douliedai4838 douliedai4838 7年前
    INSERT INTO `results`
        (`class_id`, `rider_id`, `schedule_id`,
        `finish`, `points`, `season_id` )
    SELECT DISTINCT racedata.class_id, riders.id as rider_id,
        schedule.id as schedule_id,  racedata.cf_finish,
        racedata.main_points, season.season_id
    FROM racedata
    LEFT JOIN riders
    ON racedata.ridername = riders.ridername
    INNER JOIN `schedule`
    ON `schedule`.date = STR_TO_DATE(`racedata`.event_date, '%m/%d/%Y')
    INNER JOIN `seasons`
    ON `seasons`.date = EXTRACT(YEAR FROM NOW())
    AND `seasons`.date = EXTRACT(YEAR FROM `schedule`.date)
    WHERE cf_finish > 0;
    
    点赞 评论 复制链接分享