qq_41917077
2019-03-18 11:51
采纳率: 75%
浏览 1.3k

mysql 在查询过程中对数据库数据进行比对操作

现有课程表:course,表示学生应该在带什么书

        * +------+-----------+--------------+
        * |id    |    user_id|     book_name|
        * +---------------------------------+
        * |1     |          1|          语文|
        * +---------------------------------+
        * |2     |          2|          数学|
        * +------+-----------+--------------+

表pacakge表示学生所属背包内带的书

        * +------+-----------+--------------+
        * |id    |    user_id|     book_name|
        * +---------------------------------+
        * |1     |          1|          语文|
        * +---------------------------------+
        * |2     |          1|          数学|
        * +------+-----------+--------------+
        * |3     |          1|          英语|
        * +---------------------------------+
        * |4     |          2|          化学|
        * +------+-----------+--------------+
        * |5     |          2|          物理|
        * +---------------------------------+
        * |6     |          2|          生物|
        * +------+-----------+--------------+

现在想通过course c LEFT JOIN pacakge p ON c.user_id = p.user_id的情况下查询并判断学生是否带了该堂课程的书
的sql应该怎么写?
要求返回列
course.id(课程id),course.user_id(用户id),course.book_name(所需书本),isTooked(是否携带0:带了,1:没带)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • 1进击的小白 2019-03-18 14:00
    已采纳

    select a.id,a.user_id,a.book_name,case WHEN a.book_name=b.book_name then 0 ELSE 1 end as isTooked from course a inner join package b on a.user_id=b.user_id group by a.user_id
    图片说明

    点赞 打赏 评论
  • terminal_ 2019-03-18 12:13

    select c.id, c.user_id, c.book_name, c.book_name = p.book_name from course c left join package p on c.user_id = p.user_id

    点赞 打赏 评论
  • have_power 2019-03-18 13:59
    alter table pacakge add istooked int;
    update pacakge set istooked = 1;
    select
    a.id,
    a.user_id,
    a.book_name,
    ifnull(b.istooked,0,b.istoooked)
    from 
    course a
    left join
    pacakge b
    on a.user_id = b.user_id
    and a.book_name = b.book_name
    
    点赞 打赏 评论

相关推荐 更多相似问题