使用左表的条件执行右外连接 [英] perform right outer join with a condition for left table
问题描述
我有两张桌子,
学生:
rollno | name
1 | abc
2 | efg
3 | hij
4 | klm
出勤率:
name | 日期 | statuss
Abc | 10-10-2013 | A
efg | 10-10-2013 | A
Abc | 11-10-2013 | A
hij | 25-10-2013 | A
我所需的输出是:
一些查询条件为哪里的日期介于'10之间 - 09-2013'和'13 -10-2013'
rollno | name | count
1 | Abc | 2
2 | efg | 1
3 | hij | 0
4 | klm | 0
i尝试使用:
< pre lang =SQL> SELECT p.rollno,p.name, case when s.statuss = ' A' then COUNT(p.rollno) else ' 0' end as count 来自出勤率正确 外部 加入学生p s.rollno = p.rollno 其中 s。 date ' 2013' 年10月9日 和 ' 13-10-2013' group by p.rollno,p.regno,p.name,s.statuss 订单 按 p.rollno
output 是:
rollno | name | count
1 | Abc | 2
2 | efg | 1
i希望学生表中的剩余值也被追加我试过很多方法都是徒劳的,请给我一个提供输出的查询,
提前感谢。 I发现它,谢谢:)
SELECT p.rollno,p.name, case 当 s.statuss = ' A' 然后 COUNT(p.rollno) else ' 0' end as 计算
来自出勤s
正确 < span class =code-keyword> outer join 学生p
s.rollno = p.rollno
和 s 。 date ' 10-09-2013' 和 ' 13-10-2013'
group by p.rollno,p.regno,p.name,s.statuss
order by p .rollno;
I have two tables,
student:
rollno | name
1 | Abc
2 | efg
3 | hij
4 | klm
attendance:
name | date |statuss
Abc | 10-10-2013 | A
efg | 10-10-2013 | A
Abc | 11-10-2013 | A
hij | 25-10-2013 | A
my required output is:
some query with where condition as "where date between '10-09-2013' and '13-10-2013' "
rollno| name |count
1 | Abc | 2
2 | efg | 1
3 | hij | 0
4 | klm | 0
i tried using:
SELECT p.rollno,p.name,case when s.statuss='A' then COUNT(p.rollno) else '0' end as count from attendance s right outer join student p on s.rollno=p.rollno where s.date between '10-09-2013' and '13-10-2013' group by p.rollno,p.regno,p.name,s.statuss order by p.rollno
output is:
rollno| name |count
1 | Abc | 2
2 | efg | 1
i want the remaining values from the student table to also be appended,i tried many ways all are in vain,pls provide me a query which provides the output,
thanks in advance.
I found it,thank you :)
SELECT p.rollno,p.name,case when s.statuss='A' then COUNT(p.rollno) else '0' end as count from attendance s right outer join student p on s.rollno=p.rollno and s.date between '10-09-2013' and '13-10-2013' group by p.rollno,p.regno,p.name,s.statuss order by p.rollno;
这篇关于使用左表的条件执行右外连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!