子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[weilong147247943]

create view view_select_assertremove
as
SELECT Remove_1.remid, dbo.Stock.stAsId, dbo.Stock.asName, dbo.Sort.soName, Remove_1.remTime, dbo.Stock.asStandard,dbo.Stock.asComponey, Remove_1.remCount,
dbo.Stock.stPrice, dbo.Stock.stPrice * Remove_1.remCount AS remmoney, Machine_1.machName, Address_1.adrName,
(SELECT dbo.Machine.machName
FROM dbo.Machine INNER JOIN
dbo.Address AS Address_2 ON dbo.Machine.machineId = Address_2.adrMachineId INNER JOIN
dbo.Remove AS Remove_2 ON Address_2.adrId = Remove_2.remIntoAdId) AS remintomachname,
(SELECT dbo.Address.adrName
FROM dbo.Address INNER JOIN
dbo.Remove ON dbo.Address.adrId = dbo.Remove.remIntoAdId) AS remintoadrname, Remove_1.remRemark
FROM dbo.Remove AS Remove_1 INNER JOIN
dbo.Asserts ON Remove_1.remAsId = dbo.Asserts.id INNER JOIN
dbo.Stock ON dbo.Asserts.asstockId = dbo.Stock.stId INNER JOIN
dbo.Address AS Address_1 INNER JOIN
dbo.Machine AS Machine_1 ON Address_1.adrMachineId = Machine_1.machineId ON dbo.Stock.stAddressId = Address_1.adrId INNER JOIN
dbo.Sort ON dbo.Stock.stSoId = dbo.Sort.soId
go

select * from view_select_assertremove

用这个视图查询数据的时候出现了“子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。”　这个错误。数据库没改数据前查的出.　请大家看看是怎么回事！

[liangCK]

就是你的子查询返回了多个值..

所以是一对多的..可以尝试使用TOP 1

[水族杰纶]

create view view_select_assertremove 
as 
SELECT  Remove_1.remid, dbo.Stock.stAsId, dbo.Stock.asName, dbo.Sort.soName, Remove_1.remTime, dbo.Stock.asStandard,dbo.Stock.asComponey, Remove_1.remCount,  
                      dbo.Stock.stPrice, dbo.Stock.stPrice * Remove_1.remCount AS remmoney, Machine_1.machName, Address_1.adrName, 
                          (SELECT  top 1  dbo.Machine.machName 
                            FROM          dbo.Machine INNER JOIN 
                                                  dbo.Address AS Address_2 ON dbo.Machine.machineId = Address_2.adrMachineId INNER JOIN 
                                                  dbo.Remove AS Remove_2 ON Address_2.adrId = Remove_2.remIntoAdId) AS remintomachname, 
                          (SELECT  top 1   dbo.Address.adrName 
                            FROM          dbo.Address INNER JOIN 
                                                  dbo.Remove ON dbo.Address.adrId = dbo.Remove.remIntoAdId) AS remintoadrname, Remove_1.remRemark 
FROM        dbo.Remove AS Remove_1 INNER JOIN 
                      dbo.Asserts ON Remove_1.remAsId = dbo.Asserts.id INNER JOIN 
                      dbo.Stock ON dbo.Asserts.asstockId = dbo.Stock.stId INNER JOIN 
                      dbo.Address AS Address_1 INNER JOIN 
                      dbo.Machine AS Machine_1 ON Address_1.adrMachineId = Machine_1.machineId ON dbo.Stock.stAddressId = Address_1.adrId INNER JOIN 
                      dbo.Sort ON dbo.Stock.stSoId = dbo.Sort.soId 
go 

[水族杰纶]

create view view_select_assertremove 
as 
SELECT  Remove_1.remid, dbo.Stock.stAsId, dbo.Stock.asName, dbo.Sort.soName, Remove_1.remTime, dbo.Stock.asStandard,dbo.Stock.asComponey, Remove_1.remCount,  
                      dbo.Stock.stPrice, dbo.Stock.stPrice * Remove_1.remCount AS remmoney, Machine_1.machName, Address_1.adrName, 
                          (SELECT  top 1  dbo.Machine.machName 
                            FROM          dbo.Machine INNER JOIN 
                                                  dbo.Address AS Address_2 ON dbo.Machine.machineId = Address_2.adrMachineId INNER JOIN 
                                                  dbo.Remove AS Remove_2 ON Address_2.adrId = Remove_2.remIntoAdId) AS remintomachname, 
                          (SELECT  top 1   dbo.Address.adrName 
                            FROM          dbo.Address INNER JOIN 
                                                  dbo.Remove ON dbo.Address.adrId = dbo.Remove.remIntoAdId) AS remintoadrname, Remove_1.remRemark 
FROM        dbo.Remove AS Remove_1 INNER JOIN 
                      dbo.Asserts ON Remove_1.remAsId = dbo.Asserts.id INNER JOIN 
                      dbo.Stock ON dbo.Asserts.asstockId = dbo.Stock.stId INNER JOIN 
                      dbo.Address AS Address_1 INNER JOIN 
                      dbo.Machine AS Machine_1 ON Address_1.adrMachineId = Machine_1.machineId ON dbo.Stock.stAddressId = Address_1.adrId INNER JOIN 
                      dbo.Sort ON dbo.Stock.stSoId = dbo.Sort.soId 
go 

[rockyvan]

把你這兩個子查詢改成左連接。

(SELECT dbo.Machine.machName 
FROM dbo.Machine INNER JOIN 
dbo.Address AS Address_2 ON dbo.Machine.machineId = Address_2.adrMachineId INNER JOIN 
dbo.Remove AS Remove_2 ON Address_2.adrId = Remove_2.remIntoAdId) AS remintomachname,
 
(SELECT dbo.Address.adrName 
FROM dbo.Address INNER JOIN 
dbo.Remove ON dbo.Address.adrId = dbo.Remove.remIntoAdId) AS remintoadrname

[weilong147247943]

要怎么改呢？

[weilong147247943]

　　　发错了，不好意思，刚没看到你们的答案！

[weilong147247943]

还有个问题，数据库里面的数据不同它显示的也不同！
　　　有一个数据库显示的数据是错误的！（machName,adrName,remintoadrname,remintomachname这四列的值都相同！）　帮忙看看！

[中国风]

[Quote=引用 7 楼 weilong147247943 的回复:]
还有个问题，数据库里面的数据不同它显示的也不同！
有一个数据库显示的数据是错误的！（machName,adrName,remintoadrname,remintomachname这四列的值都相同！）　帮忙看看！
[/Quote]

注意子查詢的條件表名用別名

[中国风]

字查詢條件引用外部表

[weilong147247943]

用了别名了。