问题简化一下
＊＊＊＊ 表和视图的定义 ＊＊＊
//A类设备
CREATE TABLE dbo.A
(
ID numeric(18, 0),
GG1 char(15) NULL,
ZT char(8)
)
//B类设备
CREATE TABLE dbo.B
(
ID numeric(18, 0),
GG1 int NULL,
GG2 int NULL,
ZT char(8)
)
//视图定义
CREATE VIEW MyView
AS
select ID,
'A' as LB,
GG1 as GG,
ZT
from A
union
Select ID,
'B' as LB,
convert(char(8),GG1)+convert(char(8),GG2) as GG,
ZT
from B
********** 基于视图的查询例子和结果集如下 ************
select * from myview
ID LB GG ZT
---------------------- -- ---------------- --------
1 A aaa 000A
1 B 34 43 000A
2 A aaa 000B
2 B 22 21 000B
3 A abcd 000C
4 A cdef 000A
5 A 2221 000B
(7 row(s) affected)
正确!
select * from Myview where (zt='000A')
ID LB GG ZT
---------------------- -- ---------------- --------
1 A aaa 000A
1 B 34 43 000A
4 A cdef 000A
(3 row(s) affected)
正确!
select * from Myview where (gg='aaa')AND(zt='000A')
ID LB GG ZT
---------------------- -- ---------------- --------
1 A aaa 000A
(1 row(s) affected)
正确!
select * from Myview where (gg='aaa')OR(zt='000A')
ID LB GG ZT
---------------------- -- ---------------- --------
1 A aaa 000A
1 B 34 43 000A
2 A aaa 000B
2 B 22 21 000B
3 A abcd 000C
4 A cdef 000A
5 A 2221 000B
(7 row(s) affected)
错误!!!
A表和B表中都有满足条件的记录,SQL却把所有的Ａ表Ｂ表记录都选出！！！
为什么？？
