有两个表,
一个是学生表student,列出了所有的成员
有两个字段
id,name另外一个是学生借书表borrow
表结构
borrowId
borrowName '借阅人
borrowDate '借阅时间
BorrowDamage '图书是否有损坏 0没有 1 有损坏能不能产生一个视图
结构类似于
id
name
borrowcount ,借阅次数
damageCount '损坏次数如何写这个语法。谢谢。
一个是学生表student,列出了所有的成员
有两个字段
id,name另外一个是学生借书表borrow
表结构
borrowId
borrowName '借阅人
borrowDate '借阅时间
BorrowDamage '图书是否有损坏 0没有 1 有损坏能不能产生一个视图
结构类似于
id
name
borrowcount ,借阅次数
damageCount '损坏次数如何写这个语法。谢谢。
create view v1
as
select a.id,a.name,count(*)as 借阅次数,sum(case when b.BorrowDamage=1 then 1 else 0 end)as 损坏次数
from table1 a inner join table2 b on a.id=b.borrowName
group by a.id,a.name
[code]
create view v1
as
select a.id,a.name,count(*)as 借阅次数,sum(case when b.BorrowDamage=1 then 1 else 0 end)as 损坏次数
from table1 a inner join table2 b on a.id=b.borrowName
group by a.id,a.name
create view v1
as
select a.id,a.name,count(b.*)as 借阅次数,sum(b.BorrowDamage=1 )as 损坏次数
from table1 a inner join table2 b on a.id=b.borrowId
group by a.id
as
select a.id ,
a.name ,
borrowcount = isnull((select count(*) from borrow where borrowName = a.name),0),
damageCount = isnull((select count(*) from BorrowDamage where BorrowDamage = 1 and borrowName = a.name),0)
from student a
as
select a.id,a.name,count(b.*)as 借阅次数,sum(b.BorrowDamage)as 损坏次数
from table1 a inner join table2 b on a.id=b.borrowId
group by a.id
Create view view_name
as
select name,count(1) as 借阅次数
sum(case when borrowdamage=1 then 1 else 0 end) as 损坏次数 from student a,borrow b
where a.id=b.borrowname