改个昵称好难啊 2017-11-16 04:01 采纳率: 100%
浏览 1791
已采纳

在mysql中要查绚的结果由两个表构成,这两个表中有一个表是不确定的,要根据查出的条件判断连接某个表

共有四个表
topic user teacher student

CREATE TABLE student(
student_id INT AUTO_INCREMENT NOT NULL,
student_name VARCHAR(80) NOT NULL,
student_number VARCHAR(20) NOT NULL,
student_photopath VARCHAR(50) ,
PRIMARY KEY(student_id)
);

    CREATE TABLE  teacher(
teacher_id                          INT AUTO_INCREMENT NOT NULL,
teacher_name                    VARCHAR(80) NOT NULL,
teacher_number                VARCHAR(80) UNIQUE NOT NULL,         
teacher_photopath            VARCHAR(50) ,
PRIMARY KEY(teacher_id)   

);

CREATE TABLE user(
user_id INT AUTO_INCREMENT NOT NULL,
target ENUM('1','0') NOT NULL, -- 身份标识(学生:1 老师:0)
user_number VARCHAR(80) UNIQUE NOT NULL,--学生或老师的账号

PRIMARY KEY(userlogin_id)

)

CREATE TABLE topic(
topic_id INT AUTO_INCREMENT NOT NULL,
topic_title VARCHAR(300) NOT NULL, -- 话题标题
topic_content TEXT NOT NULL, -- 话题内容
user_number VARCHAR(20) NOT NULL, -- 话题创建人账号
PRIMARY KEY(topic_id)
) ;

我的需求是这样的要查询 topic_id,topic_title,topic_content , user_number
,user_name,user_photopath(user_name和user_photopath,是student或者teacher表中的),需要根据user表中的target判断一下具体去哪个表中查,这样的查询语句怎么写。不知道我说清楚了没,这个问题困惑了好久,求大神解答

  • 写回答

2条回答 默认 最新

  • danielinbiti 2017-11-16 04:21
    关注
    select * from (
      select topic_id,topic_title,topic_content , user_number
    ,student_name as user_name,student_photopath as user_photopath from topic,student,user where topic.user_number=student.student_number
    and student.student_number=user.user_number and user.target=1
    union all
      select topic_id,topic_title,topic_content , user_number
    teacher_name as user_name,teacher_photopath as user_photopath from topic,teacher,user where topic.user_number=teacher.teacher_number
    and teacher.teacher_number=user.user_number and user.target=0
    ) order by topic_id
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 如何在scanpy上做差异基因和通路富集?
  • ¥20 关于#硬件工程#的问题,请各位专家解答!
  • ¥15 关于#matlab#的问题:期望的系统闭环传递函数为G(s)=wn^2/s^2+2¢wn+wn^2阻尼系数¢=0.707,使系统具有较小的超调量
  • ¥15 FLUENT如何实现在堆积颗粒的上表面加载高斯热源
  • ¥30 截图中的mathematics程序转换成matlab
  • ¥15 动力学代码报错,维度不匹配
  • ¥15 Power query添加列问题
  • ¥50 Kubernetes&Fission&Eleasticsearch
  • ¥15 報錯:Person is not mapped,如何解決?
  • ¥15 c++头文件不能识别CDialog