doutang9037 2017-11-30 07:40 采纳率: 100%
浏览 43
已采纳

根据用户选择最后一个学习(最大)课程

What I am doing?

I am displaying the user according to there studied. I want to fetch the last course studied by each user.

Problem?

I am not getting the last course name according to last studied course. I am getting the first course name.(java) but i want (oracle) as last studied course by user.

User Table

user_id | Name
====================
1       | Zishan                                          
2       | Ellen

Course Table

course_id | course_name | user_id | course_year 
==================================================
   1      |  java       | 1       | 2015   
   2      |  C++        | 1       | 2017
   3      |  oracle     | 1       | 2016
   4      |  dot net    | 2       | 2016

Result Table

Name    | last_course_name | last_course_year
============================================
Zishan  | java             | 2017
Ellen   | dot net          | 2016

Expected Result

Name    | last_course_name | last_course_year
============================================
Zishan  | C++              | 2017
Ellen   | dot net          | 2016

Query

SELECT `u`.`name`, MAX(`c`.`course_year`) as last_course_year , `c`.`course_name` as last_course_name 
FROM `user` as `u`
LEFT OUTER JOIN `course` as `c` ON `u`.`id` = `c`.`user_id`
GROUP BY `u`.`id`

Active Record Query:

$this->db->select('u.name','c.course_name as last_course_name');
$this->db->select_max('c.course_year as last_course_year'); 
$this->db->from('user as u');
$this->db->join('course as c', 'u.id = c.user_id', 'left');
$this->db->join('course as c1', 'c.user_id = c1.user_id', 'left outer');    
$this->db->where('c1.user_id IS NULL', null, false); 
$this->db->group_by('u.id');
$user_couse_data_query = $this->db->get();
  • 写回答

4条回答 默认 最新

  • duanchao4445 2017-11-30 08:01
    关注

    Here you go to the latest row from course table for each student

    SELECT `u`.`name`, `c`.`course_name` as last_course_name ,c.course_year
    FROM `user` as `u`
    LEFT JOIN `course` as `c` ON `u`.`id` = `c`.`user_id`
    LEFT JOIN `course` as `c1` ON `c`.`user_id` = `c1`.`user_id` AND  `c`.`course_year` < `c1`.`course_year`
    WHERE `c1`.`user_id` is null
    

    Demo

    Note it may return multiple courses if they share same year

    Equivalent active record query will be something like

    $this->db->select('u.name','c.course_name as last_course_name','c.course_year as last_course_year');
    $this->db->from('user as u');
    $this->db->join('course as c', 'u.id = c.user_id', 'left');
    $this->db->join('course as c1', 'c.user_id = c1.user_id AND c.course_year < c1.course_year', 'left');    
    $this->db->where('c1.user_id IS NULL', null, false); 
    $user_couse_data_query = $this->db->get();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀