create view view_2 as
select tab_1.id,tab_1.name,tab_1.class_id
from tab_1-----,view_1
where tab_1.id not in (select view_1.id from view_1)
and tab_1.class_id not in (select view_1.class_id from view_1)
select tab_1.id,tab_1.name,tab_1.class_id
from tab_1-----,view_1
where tab_1.id not in (select view_1.id from view_1)
and tab_1.class_id not in (select view_1.class_id from view_1)
create view view_1 as
select tab_1.id,tab_1.name,tab_1.class_id,tab_2.book_id,tab_2.book_name
from tab_1,book_1
where tab_1.id = book_1.id and tab_1.class_id = book_1.class_id
如果说他原来的view_2查询没有记录的话,你修改后仍然没有记录。
select tab_1.id,tab_1.name,tab_1.class_id
from tab_1,view_1
where tab_1.id not in (select view_1.id from view_1)
and tab_1.class_id not in (select view_1.class_id from view_1)
条件不对。可以改为
create view view_2 as
select tab_1.id,tab_1.name,tab_1.class_id
from tab_1,view_1
where cast(tab_1.id as char(20)) + cast(tab_1.class_id as char(20))
not in
(select cast(view_1.id as char(20))+cast(view_1.class_id as char(20))
from view_1)
所有没有结果数的学生:
select tab_1.id,tab_1.name,tab_1.class_id
from tab_1,view_1
????
view_2的语句的确有问题:
where tab_1.id not in (select view_1.id from view_1)
and tab_1.class_id not in (select view_1.class_id from view_1)
因为tab_1.id在view_1中的确没有,但tab_1.class_id为班级号在view_1重视存在的,所以用and肯定插不出来,但就不知该怎么写?
create view view_2 as
select a.id,a.name,a.class_id
from tab_1 a
where not exists(select 1
from book_1 b
where a.id = b.id and a.class_id = b.class_id)
我写的第一个方法粘贴的时候没注意,要修改一下
from tab_1,view_1
改成
from tab_1
最好用后面那种方法,直接从原表创建,这样会比创建在view上灵活,更快
谢谢大家