create view view1 as
select date1,room,class,teacher,kecheng from class where num=1 and date1='2002-1-1'
gocreate view view2 as
select date1,room,class,teacher,kecheng from class where num=2 and date1='2002-1-1'
goselect view1.date1,view1.room,view1.class,view1.teacher,view1.kecheng,view2.class,view2.teacher,view2.kecheng from view1 left join view2 on view1.date1=view2.date1 and view1.room=view2.roomIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view1')
DROP VIEW view1
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view2')
DROP VIEW view2
我在SQL200中测试过了
select date1,room,class,teacher,kecheng from class where num=1 and date1='2002-1-1'
gocreate view view2 as
select date1,room,class,teacher,kecheng from class where num=2 and date1='2002-1-1'
goselect view1.date1,view1.room,view1.class,view1.teacher,view1.kecheng,view2.class,view2.teacher,view2.kecheng from view1 left join view2 on view1.date1=view2.date1 and view1.room=view2.roomIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view1')
DROP VIEW view1
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view2')
DROP VIEW view2
我在SQL200中测试过了
(我想把日期变量通过参数传给视图)例:
select date1,room,class,teacher,kecheng from class where num=1 and date1=变量这样可以吗?另:在VB里是是如何调用带参数的存储过程的?
dim command1 as New ADODB.Command
Dim Pr1 As New ADODB.Parameter
dim g_con as New ADODB.Connection
command1.CreateParameter pr1
设置pr1的属性
指定command1存储过程名
command1.Execute