-- 要有用 Alias 的习惯, 不然读得累不说,也容易犯你这样的错误。 CREATE VIEW dbo.v_listAllocOut AS SELECT A.CK_AllocOutDate , A.CK_AllocOutNo , O.CK_BranchName outbranch , T.CK_BranchName tobranch FROM CK_AllocOut A INNER JOIN dbo.CK_Branch O ON A.CK_OutBranchID=O.CK_BranchID INNER JOIN dbo.CK_Branch T ON A.CK_ToBranchID = O.CK_BranchID
报错是因为SQLServer认不出你的后面两个表关联中字段是对应哪个表的
这个Alloc表没有问题的,有两个项,都引用的是CK_Branch表的外键。问题是,我在视图里没法将这两个ID换成名字呢 CK_OutBranchID INT NOT NULL FOREIGN KEY REFERENCES CK_Branch(CK_BranchID), CK_ToBranchID INT NOT NULL FOREIGN KEY REFERENCES CK_Branch(CK_BranchID),
或者换个例子跟你说吧,假设有个A表,只有一列自增的ID,那么下面的写法是可以的: select id as AID,id+1 as AID FROM A但是当有外层调用或者插入临时表是就会报错,比如: select AID FROM (select id as AID,id+1 as AID FROM A) A因为SQL Server不知道你这个AID指的究竟是子查询的哪个列,你的情况就和这个类似
您的意思?如下红色部分?CREATE VIEW dbo.v_listAllocOut AS SELECT CK_AllocOutDate , CK_AllocOutNo , CK_BranchNameOut outbranch , CK_BranchNameTo tobranch FROM CK_AllocOut那join时,怎么写呢,多谢了。 我试过了,这样还是报错。 SELECT dbo.CK_AllocOut.CK_AllocOutDate , dbo.CK_AllocOut.CK_AllocOutNo , outbranch , tobranch FROM CK_AllocOut inner JOIN dbo.CK_Branch ON dbo.CK_Branch.CK_BranchID = CK_AllocOut.CK_OutBranchID inner JOIN dbo.CK_Branch ON dbo.CK_Branch.CK_BranchID = CK_AllocOut.CK_ToBranchID报错如下:(sql 2008) FROM 子句中的对象 "dbo.CK_Branch" 和 "dbo.CK_Branch" 具有相同的表现名称。请使用相关名称来区分它们。
SELECT a.CK_AllocOutDate , a.CK_AllocOutNo , outbranch --这两个字段你对应看是b还是c表的 , tobranch --这两个字段你对应看是b还是c表的 FROM CK_AllocOut a inner JOIN dbo.CK_Branch b ON a.CK_BranchID = b.CK_OutBranchID inner JOIN dbo.CK_Branch c ON a.CK_BranchID = c.CK_ToBranchID
CREATE VIEW dbo.v_listAllocOut
AS
SELECT
A.CK_AllocOutDate
, A.CK_AllocOutNo
, O.CK_BranchName outbranch
, T.CK_BranchName tobranch
FROM CK_AllocOut A
INNER JOIN dbo.CK_Branch O ON A.CK_OutBranchID=O.CK_BranchID
INNER JOIN dbo.CK_Branch T ON A.CK_ToBranchID = O.CK_BranchID
这个Alloc表没有问题的,有两个项,都引用的是CK_Branch表的外键。问题是,我在视图里没法将这两个ID换成名字呢 CK_OutBranchID INT NOT NULL FOREIGN KEY REFERENCES CK_Branch(CK_BranchID),
CK_ToBranchID INT NOT NULL FOREIGN KEY REFERENCES CK_Branch(CK_BranchID),
难道你要的关联关系是这样?: CK_AllocOut.CK_OutBranchID=CK_AllocOut.CK_ToBranchID=CK_Branch.CK_BranchID 好好看清楚, 想清楚!!!
难道你要的关联关系是这样?: CK_AllocOut.CK_OutBranchID=CK_AllocOut.CK_ToBranchID=CK_Branch.CK_BranchID 好好看清楚, 想清楚!!!是的,AllocOut表两个字段utBranchID, InBranchID都来自CK_Branch表的外键,而我想在视图v_listAllocOut中把BranchID换成名字,所以用了JOIN
意思是这样的,按我的思路,这样总是不行呢,烦请大侠指教
select id as AID,id+1 as AID
FROM A但是当有外层调用或者插入临时表是就会报错,比如:
select AID
FROM (select id as AID,id+1 as AID
FROM A) A因为SQL Server不知道你这个AID指的究竟是子查询的哪个列,你的情况就和这个类似
您的意思?如下红色部分?CREATE VIEW dbo.v_listAllocOut
AS
SELECT
CK_AllocOutDate
, CK_AllocOutNo
, CK_BranchNameOut outbranch
, CK_BranchNameTo tobranch
FROM CK_AllocOut那join时,怎么写呢,多谢了。
我试过了,这样还是报错。
SELECT
dbo.CK_AllocOut.CK_AllocOutDate
, dbo.CK_AllocOut.CK_AllocOutNo
, outbranch
, tobranch
FROM CK_AllocOut
inner JOIN dbo.CK_Branch ON dbo.CK_Branch.CK_BranchID = CK_AllocOut.CK_OutBranchID
inner JOIN dbo.CK_Branch ON dbo.CK_Branch.CK_BranchID = CK_AllocOut.CK_ToBranchID报错如下:(sql 2008)
FROM 子句中的对象 "dbo.CK_Branch" 和 "dbo.CK_Branch" 具有相同的表现名称。请使用相关名称来区分它们。
a.CK_AllocOutDate
, a.CK_AllocOutNo
, outbranch --这两个字段你对应看是b还是c表的
, tobranch --这两个字段你对应看是b还是c表的
FROM CK_AllocOut a
inner JOIN dbo.CK_Branch b ON a.CK_BranchID = b.CK_OutBranchID
inner JOIN dbo.CK_Branch c ON a.CK_BranchID = c.CK_ToBranchID