3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
为什么我创建的视图里没有数据！
我建立了两个表，CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
然后创建一个视图
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
其中p中有数据，但是e中没有数据，执行后发现表Employee中没有数据，为什么，应该至少显示P中的数据呀？
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
然后创建一个视图
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
其中p中有数据，但是e中没有数据，执行后发现表Employee中没有数据，为什么，应该至少显示P中的数据呀？
...全文
请发表友善的回复…
发表回复
victorycyz 2003-12-12
同意楼上两位用左联接。
另外，即使是用内联，也要指定关联字段，否则，如果两表中都有较多的记录，你的查询将得到两表记录数相乘的记录数。这恐怕不是你想要的吧。
另外，即使是用内联，也要指定关联字段，否则，如果两表中都有较多的记录，你的查询将得到两表记录数相乘的记录数。这恐怕不是你想要的吧。
azsoft 2003-12-11
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P
left outer join EmployeeTable E on p.ssn = e.ssn
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P
left outer join EmployeeTable E on p.ssn = e.ssn
txlicenhe 2003-12-11
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P
left join EmployeeTable E on p.ssn = e.ssn
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P
left join EmployeeTable E on p.ssn = e.ssn