当子查询跟随=，！=，<，<=，>，> =或子查询用作表达式时，子查询返回的值不超过1-Java 学习之路

当我在sql server的视图中执行选择行时，我得到了 Headers 错误 .

这是我的代码

CREATE VIEW vw_cashbook AS
SELECT cashbooktranno, 
       cashbookdate, 
       cashbookrefaccname, 
       cashbookgroupname, 
       cashbookaccname, 
       cashbookdescription, 
       cashbookreceipt, 
       cashbookpayment,
       (select sum(cashbook.cashbookreceipt) - sum(cashbook.cashbookpayment) from tbl_cashbook) as totalpayment,
       company.companyname
FROM tbl_cashbook cashbook 
  LEFT OUTER JOIN tbl_companyregistration company 
               ON cashbook.branchid = company.companyid
group by cashbooktranno, cashbookdate, cashbookrefaccname, cashbookgroupname, cashbookaccname, 
cashbookdescription, cashbookreceipt, cashbookpayment, company.companyname

现在，此代码用于从表中创建视图 . 这段代码非常完美，因此根据我的视图也创建了代码 . 现在，问题是，当我从创建的视图中选择行或在那时编辑视图时，它会显示错误

子查询返回的值超过1 . 当子查询遵循=，！=，<，<=，>，> =或子查询用作表达式时，不允许这样做 .

这是我对视图的选择查询

SELECT TOP 1000 [cashbooktranno]
      ,[cashbookdate]
      ,[cashbookrefaccname]
      ,[cashbookgroupname]
      ,[cashbookaccname]
      ,[cashbookdescription]
      ,[cashbookreceipt]
      ,[cashbookpayment]
      ,[totalpayment]
      ,[companyname]
  FROM [AutoFinanceSoftware].[dbo].[vw_cashbook]

那么我的查询中是否有任何问题，或者是否有任何其他问题 .

1 回答

不需要子查询 . 使用：

CREATE VIEW vw_cashbook AS
SELECT cashbooktranno, 
       cashbookdate, 
       cashbookrefaccname, 
       cashbookgroupname, 
       cashbookaccname, 
       cashbookdescription, 
       cashbookreceipt, 
       cashbookpayment,
       sum(cashbook.cashbookreceipt) - sum(cashbook.cashbookpayment) as totalpayment,
       company.companyname
FROM tbl_cashbook cashbook 
  LEFT OUTER JOIN tbl_companyregistration company 
               ON cashbook.branchid = company.companyid
group by cashbooktranno, cashbookdate, cashbookrefaccname, cashbookgroupname, cashbookaccname, 
cashbookdescription, cashbookreceipt, cashbookpayment, company.companyname;

或者甚至（如果两列都是 NOT NULL ）：

CREATE VIEW vw_cashbook AS
SELECT cashbooktranno, 
       cashbookdate, 
       cashbookrefaccname, 
       cashbookgroupname, 
       cashbookaccname, 
       cashbookdescription, 
       cashbookreceipt, 
       cashbookpayment,
       sum(cashbook.cashbookreceipt - cashbook.cashbookpayment) as totalpayment,
       company.companyname
FROM tbl_cashbook cashbook 
  LEFT OUTER JOIN tbl_companyregistration company 
               ON cashbook.branchid = company.companyid
group by cashbooktranno, cashbookdate, cashbookrefaccname, cashbookgroupname, cashbookaccname, 
cashbookdescription, cashbookreceipt, cashbookpayment, company.companyname

回复于 2024-05-03T08:28:55+08:00

当子查询跟随=，！=，<，<=，>，> =或子查询用作表达式时，子查询返回的值不超过1

1 回答

相关问题