Mysql查询问题 [英] Mysql query problem

问题描述

我的数据库：

 部分
 id 
 section_name 
 
等级
 id 
 user_id 
 section_id 
日期
等级
  

我希望我的数据能像这样显示：

Section_name grade

但是我希望成绩与今天的日期最接近......
这是我迄今为止的成绩，但它并没有不显示最新成绩。相反，它通过ID命令（我猜）

  SELECT * 
 
 FROM 
等级， 
部分
 
 WHERE 
 sections.id = grades.section_id 
 
 AND 
 grades.user_id ='。$ id。'
 
 GROUP BY grades.section_id 
 
 ORDER BY grades.date DESC 
  

编辑：$ id变量是来自会话的用户标识。

解决方案

我会根据您想要的特定user_id进行预查询，并查找每段的最大日期。然后，重新 - 回到部分和成绩（现在这个预先考虑将极大地限制结果集合）。然后获取部分名称，最后为匹配特定学生（用户）参加课程的日期给出适当的分数。

  select STRAIGHT_JOIN 
 PreQuery.Section_ID，
 Sections.section_name，
 PreQuery.LastDatePerSection 
 from 
（选择section_id，
 user_id，
 max（date）as LastDatePerSection 
 from 
 grades 
 where 
 user_id = YourUserIDParameter 
 group by 
 section_id）预先查询
 
连接节
 on PreQuery.Section_ID = Sections.ID 
 
在PreQuery.Section_ID = grades.Section_ID上加入成绩
 $ b $ AND PreQuery.User_ID = grades.User_ID 
 AND PreQuery.LastDatePerSection = grade.Date 
  

I have a problem with my mysql query.

My database:

sections
id
section_name

grades
id
user_id
section_id
date
grade

I want my data to be shown like this:

Section_name grade

But i wat want the grade to be the closest to todays date... This is what i have so far but it doesn't show the latest grade. instead it orders by id (I guess)

SELECT *

                        FROM 
                        grades, 
                        sections 

                        WHERE 
                        sections.id = grades.section_id

                        AND
                        grades.user_id = '.$id.'

                        GROUP BY grades.section_id

                        ORDER BY grades.date DESC

EDIT: the $id variable is the user id from a session.

解决方案

I would pre-query based on the specific user_id you wanted, and find their max date per section.. Then, re-join back to sections and grades (now that this prequery will extremely limit the result set to join against). Then get section name and finally proper grade for the date matching the specific student (user) taking the course.

select STRAIGHT_JOIN
      PreQuery.Section_ID,
      Sections.section_name,
      PreQuery.LastDatePerSection
   from
      ( select section_id, 
               user_id,
               max( date ) as LastDatePerSection 
            from
               grades
            where
               user_id = YourUserIDParameter
            group by
               section_id ) PreQuery

      join sections
         on PreQuery.Section_ID = Sections.ID

      join grades
         on PreQuery.Section_ID = grades.Section_ID
         AND PreQuery.User_ID = grades.User_ID
         AND PreQuery.LastDatePerSection = grades.Date

这篇关于Mysql查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！