代码之家 › 专栏 › 技术社区 › ErocM

无法使视图在SQL Server上工作

sql-server-2008-r2 tsql

ErocM · 技术社区 · 11 年前

我正在尝试创建一个包含多个表中的列的视图。

它看起来是这样的:

enter image description here

这是我的疑问:

SELECT     
   Billing.WebPortalBilling.WebPortalBillingId, 
   Billing.WebPortalBilling.CorporationId, 
   Billing.WebPortalBilling.TokenId, 
   Billing.WebPortalBilling.GatewaySupportFee, 
   Billing.WebPortalBilling.GatewayPerTransactionFee, 
   Billing.WebPortalBilling.PortalPerCustomerFee, 
   Billing.WebPortalBilling.PortalSupportFee, 
   Customer.Account.AccountNumber, 
   Billing.WebPortalBilling.IsActive, 
   Customer.Customer.Name, 
   Customer.Customer.TaxCode, 
   Company.CorporationStructure.Branch
FROM         
   Company.CorporationStructure 
RIGHT OUTER JOIN
   Customer.Account ON Company.CorporationStructure.CorporationStructureId = Customer.Account.CorporationStructureId 
RIGHT OUTER JOIN
   Customer.Customer ON Company.CorporationStructure.Branch = Customer.Customer.Branch 
RIGHT OUTER JOIN
   Billing.WebPortalBilling ON Customer.Account.CorporationId = Billing.WebPortalBilling.CorporationId
WHERE     
   (Billing.WebPortalBilling.IsActive = 1)

它只返回了1条记录,这是不正确的。我试图将客户的姓名与WebPortalBilling表以及其他两个表中的账号和branth联系起来。

我是sql新手,所以请友善一点。

谢谢

2 回复 | 直到 11 年前

paparazzo 11 年前

正如评论的那样,哪里正在扼杀外部
尝试

SELECT     
   Billing.WebPortalBilling.WebPortalBillingId, 
   Billing.WebPortalBilling.CorporationId, 
   Billing.WebPortalBilling.TokenId, 
   Billing.WebPortalBilling.GatewaySupportFee, 
   Billing.WebPortalBilling.GatewayPerTransactionFee, 
   Billing.WebPortalBilling.PortalPerCustomerFee, 
   Billing.WebPortalBilling.PortalSupportFee, 
   Customer.Account.AccountNumber, 
   Billing.WebPortalBilling.IsActive, 
   Customer.Customer.Name, 
   Customer.Customer.TaxCode, 
   Company.CorporationStructure.Branch
FROM         
   Company.CorporationStructure 
RIGHT OUTER JOIN
   Customer.Account ON Company.CorporationStructure.CorporationStructureId = Customer.Account.CorporationStructureId 
RIGHT OUTER JOIN
   Customer.Customer ON Company.CorporationStructure.Branch = Customer.Customer.Branch 
RIGHT OUTER JOIN  Billing.WebPortalBilling 
    ON Customer.Account.CorporationId = Billing.WebPortalBilling.CorporationId
   AND Billing.WebPortalBilling.IsActive = 1

Hogan 11 年前

试试这个,我认为左联接更清晰。

SELECT    
  B.WebPortalBillingId, 
  B.CorporationId,   
  B.TokenId, 
  B.GatewaySupportFee, 
  B.GatewayPerTransactionFee, 
  B.PortalPerCustomerFee, 
  B.PortalSupportFee, 
  C.AccountNumber, 
  B.IsActive, 
  C.Name,
  C.TaxCode, 
  CS.Branch
FROM  Customer.Customer C
  LEFT JOIN  Company.CorporationStructure CS ON CS.Branch = C.Branch 
  LEFT JOIN  Customer.Account A ON CS.CorporationStructureId = A.CorporationStructureId
  LEFT JOIN  Billing.WebPortalBilling B ON A.CorporationId = B.CorporationId
WHERE B.IsActive = 1

推荐文章

Community wiki · SqlConnections,Parallel.For,一个旧的C#应用程序,初始化SqlConnections时随机挂起

1 年前

UnDiUdin · 如何查询数据库中Filestream文件组的文件名?

7 年前

Prasanna Kumar J · 如果SQL Server 2008 R2中不存在,请插入值

7 年前

Prasanna Kumar J · 如何使用sql server 2008 R2将数据库从现有数据库复制到新数据库sql脚本?

7 年前

Mazhar · 使用子字符串PATINDEX和STUFF修改数据的TSQL

7 年前

Willy · SQL Server存储过程返回意外值

7 年前

Furqan Misarwala · 如何在同一表的外键列中保存自动生成的主键Id

7 年前

SE1986 · sp\u执行SQL为原始查询提供不同的结果

7 年前

MS204 · 从存储在表中的查询中获取结果到其自己的结果集中?

7 年前

sai bharath · 按数值显示字母数字列顺序

7 年前