试试这个
SELECT I.studentno,type,dept,flag FROM icreader I
LEFT JOIN icreader_forbid F
ON I.studentno = F.studentno
WHERE type = '本科生'
SELECT I.studentno,type,dept,flag FROM icreader I
LEFT JOIN icreader_forbid F
ON I.studentno = F.studentno
WHERE type = '本科生'
我要的结果是
学号|类型|部门|状态|违规次数|违规原因如果状态为5的学生,则把违规原因和违规次数显示出来,如果状态不等于5 则赋空值
when 5 then select icreader_forbid.times from icreader_forbid where icreader.studentno = icreader_forbid.studentno,
else null
endfrom icreader,icreader_forbid
where icreader.studentno = icreader_forbid.studentno and icreader.type = '本科生';
这样能行吗?我也是菜鸟,错了不要笑
LEFT JOIN icreader_forbid b
ON a.studentno = b.studentno
WHERE a.type = '本科生'
SELECT I.studentno,type,dept,flag FROM icreader I
LEFT JOIN icreader_forbid F
ON (I.studentno = F.studentno AND type=5)
WHERE type = '本科生'
LEFT JOIN icreader_forbid F
ON (I.studentno = F.studentno AND type=5)
WHERE type = '本科生'
LEFT JOIN icreader_forbid b
ON a.studentno = b.studentno
WHERE a.type = '本科生'存储过程:create procedure pr_getData
@studentno varchar(200)='%',
@type varchar(200) ='%'
as
SELECT a.*,times,content FROM icreader a
LEFT JOIN icreader_forbid b
ON a.studentno = b.studentno
WHERE a.type like @type and a.studentno like @studentnoexecute pr_getData @type ='本科生'
execute pr_getData @studentno='20060101'
但是你的数据库设计有点问题
times(违规次数)和content(违规原因)
应该是多对多关系
应该分别放在2个表里
SELECT I.studentno,type,dept,flag,times,content FROM icreader I
LEFT JOIN icreader_forbid F
ON (I.studentno = F.studentno AND type=5)
WHERE type = '本科生'
测试 通过 感谢大家帮忙~`
小弟 不胜感激