文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >子查询错误，如何解决

问子查询错误，如何解决
EN

Stack Overflow用户
提问于 2016-05-26 10:24:13
回答 3查看 54关注 0票数 0

当运行以下查询时，我会得到一个错误：

子查询返回的值超过一个。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，这是不允许的。

我知道这意味着什么，但我不知道如何解决它，我正在考虑为每个循环创建一个，它将比较我的预算表中的站点，并根据不同的站点返回值。

您的意见将不胜感激。

代码语言：javascript
复制
Declare @FromDate DateTime
DEclare @ToDate DateTime
Declare @WACost Numeric (18,2)
Declare @WABill Numeric (18,2)
Declare @FBill Numeric (18, 2)
Declare @FCost Numeric (18,2)

Set @FromDate = '20160501'
Set @ToDate = '20160531'

Set @WACost = (
              Select
                    sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + Sum((dbo.FleetData.OwnCost + dbo.FleetData.OpCost)
                    * dbo.DataInput.Hrs)
              From       dbo.DataInput INNER JOIN
                         dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                         dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                         dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID
             WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.workcodes.WorkCode = 101
              ) 

Set @WABill = (
              Select
                sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + Sum((dbo.FleetData.OwnBill + dbo.FleetData.OpBill)
                * dbo.DataInput.Hrs)
              From dbo.Datainput Inner Join
                   dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                   dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                   dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID

              WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.WorkCodes.WorkCode = 101
              )

Set @FBill = (
             Select
                ((dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
                (dbo.budget.PG * (1 + PGPerc))) / (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

             From dbo.budget 

             WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
              )

Set @FCost = (
                Select (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG) / 
                (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

                From dbo.budget 

                WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
             )

SELECT     

             @WACost as WACostTotal, @WABill as WABillTotal, @FBill as FBillCalc, @FCost as FCostCalc,

              dbo.DataInput.DataInputID as DataInpID, dbo.DataInput.ProdDate as ProdDate, dbo.WorkCodes.WorkCode as WCode, dbo.DataInput.Hrs as ProdHrs, dbo.DataInput.Loads as ProdLoads, 
              dbo.DataInput.Fuel as ProdFuel, dbo.FleetData.FleetNo as ProdFltNo, dbo.JobCodes.JobCode, 
              dbo.FleetType.EquipmentType as EqType, dbo.FleetModel.ModelName as Model, dbo.Sites.SiteName as ProdSiteName, 
              dbo.JobCodes.JobDetails as JCDet, dbo.JobCodes.BillMeth as BillM,
              dbo.FleetData.OwnCost as FltOwnCost, dbo.fleetdata.OpCost as FltOpCost, dbo.fleetdata.OwnBill as FltOwnBill,
              dbo.FleetData.OpBill as FltOpBill,

              (dbo.FleetModel.ModelSize * dbo.DataInput.Loads) as ProdBCM,
              dbo.HRData.EmpNo as HREmp, dbo.HRData.RatePH as EmpCost, dbo.HRData.RateBill as EmpBill, 
              dbo.DataInput.ShiftHrs as ProdShHrs, dbo.FleetModel.ModelSize as MSize, 
              (dbo.DataInput.Fuel * dbo.JobCodes.FuelRate) as ProdFuelCost,
              dbo.budget.BudDate as BdgDate, dbo.Budget.RevBCM as BdgRevBCM, dbo.Budget.PricePerBCM as BdgBCMPrice,
              dbo.budget.BCMValue as BdgBCMVal, dbo.budget.SalWage as BdgSalWage, dbo.budget.Fuel as FuelBdg, 
              dbo.budget.PG0 as PGZero, dbo.budget.PG as PGCP,
              dbo.budget.siteId as BudgSite,

               --LAST DAY BCM
              IIF(dbo.DataInput.ProdDate = @ToDate, dbo.DataInput.Loads * dbo.FleetModel.ModelSize, 0) as LDBCM,

              --BILLING CALCS

              --PG Billing
              IIF(dbo.Workcodes.WorkCode  = 101,
              (dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
              (dbo.budget.PG * (1 + PGPerc)),0) as PGBill,

              --FleetBill
              IIF(dbo.Workcodes.WorkCode  = 101,
              (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as FleetBill,

              --LabourCost
              iif(dbo.WorkCodes.WorkCode  = 101,
              (dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill),0) as LabourBill,  

              --Total Variable Bill
              iif(dbo.Workcodes.WorkCode  = 101,
               (dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as TotalVarBill,

               --WABill
               iif(dbo.Workcodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs) / 
               @WABill * @FBill +
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs), 0) as WABill,

              --COST CALCS

              --PG Cost

              IIF(dbo.WorkCodes.WorkCode  = 101,
              (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG),0) as PGCost,

              --FLEETCOST
              IIF(dbo.WorkCodes.WorkCode  = 101,
              (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as FleetCost,

              --LabourCost
              iif(dbo.WorkCodes.WorkCode  = 101,
              (dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH),0) as LabourCost,

               --Total Variables Cost
               iif(dbo.WorkCodes.WorkCode  = 101,
               (dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as TotalVarCost,

               --WAOnly
               iif(dbo.WorkCodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) / 
               @WACost * @FCost,0)
               as WACalc,

               --WA
               iif(dbo.WorkCodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) / 
               @WACost * @FCost +
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) , 0) as WACost


FROM            dbo.DataInput INNER JOIN
                         dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                         dbo.FleetMake ON dbo.FleetData.FleetMakeId = dbo.FleetMake.FleetMakeId INNER JOIN
                         dbo.FleetModel ON dbo.FleetData.FleetModelId = dbo.FleetModel.ModelId INNER JOIN
                         dbo.FleetType ON dbo.FleetData.FleetTypeId = dbo.FleetType.FleetTypeId INNER JOIN
                         dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                         dbo.Sites ON dbo.DataInput.SiteId = dbo.Sites.SiteId INNER JOIN
                         dbo.Budget ON dbo.Sites.SiteId = dbo.Budget.SiteId AND dbo.DataInput.Period = dbo.Budget.Period INNER JOIN
                         dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID INNER JOIN
                         dbo.JobCodes ON dbo.DataInput.JobCodeId = dbo.JobCodes.JobCodeId

WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate

Group By dbo.DataInput.DataInputID, dbo.DataInput.DataInputID, dbo.DataInput.ProdDate, dbo.WorkCodes.WorkCode, dbo.DataInput.Hrs,
              dbo.DataInput.Loads, dbo.DataInput.Fuel, dbo.FleetData.FleetNo, dbo.JobCodes.JobCode, 
              dbo.FleetType.EquipmentType, dbo.FleetModel.ModelName, dbo.Sites.SiteName, 
              dbo.JobCodes.JobDetails, dbo.JobCodes.BillMeth, dbo.FleetData.OwnCost, dbo.fleetdata.OpCost, 
              dbo.fleetdata.OwnBill, dbo.FleetData.OpBill,dbo.HRData.EmpNo, dbo.HRData.RatePH, dbo.HRData.RateBill, 
              dbo.DataInput.ShiftHrs, dbo.FleetModel.ModelSize, dbo.budget.BudDate, dbo.Budget.RevBCM, dbo.Budget.PricePerBCM,
              dbo.budget.BCMValue, dbo.budget.SalWage, dbo.budget.Fuel, dbo.budget.PG0, dbo.budget.PG,
              dbo.budget.siteId, dbo.jobcodes.FuelRate, dbo.datainput.workcodeid, dbo.budget.SalWagePerc, dbo.Budget.PGPerc, 
              dbo.WorkCodes.WorkCodeID  

Order By dbo.DataInput.ProdDate
sql
sql-server
subquery
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2016-05-26 10:31:48

下面是你容易出错的地方。

代码语言：javascript
复制
Set @FBill = (
             Select
                ((dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
                (dbo.budget.PG * (1 + PGPerc))) / (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

             From dbo.budget 

             WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
              )

Set @FCost = (
                Select (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG) / 
                (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

                From dbo.budget 

                WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
)

您可以使用top和order返回如下所示的一条记录

代码语言：javascript
复制
set @fbill=select top 1 from table order by somevalue
票数 0
EN

Stack Overflow用户

发布于 2016-05-26 10:31:20

在下面尝试，如果需要在子查询中使用Order by。

代码语言：javascript
复制
Declare @FromDate DateTime
DEclare @ToDate DateTime
Declare @WACost Numeric (18,2)
Declare @WABill Numeric (18,2)
Declare @FBill Numeric (18, 2)
Declare @FCost Numeric (18,2)

Set @FromDate = '20160501'
Set @ToDate = '20160531'

Set @WACost = (Select TOP 1
                    sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + Sum((dbo.FleetData.OwnCost + dbo.FleetData.OpCost)
                    * dbo.DataInput.Hrs)
              From       dbo.DataInput INNER JOIN
                         dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                         dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                         dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID
             WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.workcodes.WorkCode = 101
              ) 

Set @WABill = (SELECT TOP 1
                sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + Sum((dbo.FleetData.OwnBill + dbo.FleetData.OpBill)
                * dbo.DataInput.Hrs)
              From dbo.Datainput Inner Join
                   dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                   dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                   dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID
              WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.WorkCodes.WorkCode = 101
              )

Set @FBill = (SELECT TOP 1 
                ((dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
                (dbo.budget.PG * (1 + PGPerc))) / (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

             From dbo.budget 

             WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
              )

Set @FCost = (SELECT TOP 1 (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG) / 
                (DAY(EOMONTH(@ToDate))) * Day(@ToDate)
                From dbo.budget 

                WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
             )

SELECT     @WACost as WACostTotal, @WABill as WABillTotal, @FBill as FBillCalc, @FCost as FCostCalc,
                dbo.DataInput.DataInputID as DataInpID, dbo.DataInput.ProdDate as ProdDate, dbo.WorkCodes.WorkCode as WCode, dbo.DataInput.Hrs as ProdHrs, dbo.DataInput.Loads as ProdLoads, 
              dbo.DataInput.Fuel as ProdFuel, dbo.FleetData.FleetNo as ProdFltNo, dbo.JobCodes.JobCode, 
              dbo.FleetType.EquipmentType as EqType, dbo.FleetModel.ModelName as Model, dbo.Sites.SiteName as ProdSiteName, 
              dbo.JobCodes.JobDetails as JCDet, dbo.JobCodes.BillMeth as BillM,
              dbo.FleetData.OwnCost as FltOwnCost, dbo.fleetdata.OpCost as FltOpCost, dbo.fleetdata.OwnBill as FltOwnBill,
              dbo.FleetData.OpBill as FltOpBill,

              (dbo.FleetModel.ModelSize * dbo.DataInput.Loads) as ProdBCM,
              dbo.HRData.EmpNo as HREmp, dbo.HRData.RatePH as EmpCost, dbo.HRData.RateBill as EmpBill, 
              dbo.DataInput.ShiftHrs as ProdShHrs, dbo.FleetModel.ModelSize as MSize, 
              (dbo.DataInput.Fuel * dbo.JobCodes.FuelRate) as ProdFuelCost,
              dbo.budget.BudDate as BdgDate, dbo.Budget.RevBCM as BdgRevBCM, dbo.Budget.PricePerBCM as BdgBCMPrice,
              dbo.budget.BCMValue as BdgBCMVal, dbo.budget.SalWage as BdgSalWage, dbo.budget.Fuel as FuelBdg, 
              dbo.budget.PG0 as PGZero, dbo.budget.PG as PGCP,
              dbo.budget.siteId as BudgSite,

               --LAST DAY BCM
              IIF(dbo.DataInput.ProdDate = @ToDate, dbo.DataInput.Loads * dbo.FleetModel.ModelSize, 0) as LDBCM,

              --BILLING CALCS

              --PG Billing
              IIF(dbo.Workcodes.WorkCode  = 101,
              (dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
              (dbo.budget.PG * (1 + PGPerc)),0) as PGBill,

              --FleetBill
              IIF(dbo.Workcodes.WorkCode  = 101,
              (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as FleetBill,

              --LabourCost
              iif(dbo.WorkCodes.WorkCode  = 101,
              (dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill),0) as LabourBill,  

              --Total Variable Bill
              iif(dbo.Workcodes.WorkCode  = 101,
               (dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as TotalVarBill,

               --WABill
               iif(dbo.Workcodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs) / 
               @WABill * @FBill +
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + 
               (FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs), 0) as WABill,

              --COST CALCS

              --PG Cost

              IIF(dbo.WorkCodes.WorkCode  = 101,
              (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG),0) as PGCost,

              --FLEETCOST
              IIF(dbo.WorkCodes.WorkCode  = 101,
              (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as FleetCost,

              --LabourCost
              iif(dbo.WorkCodes.WorkCode  = 101,
              (dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH),0) as LabourCost,

               --Total Variables Cost
               iif(dbo.WorkCodes.WorkCode  = 101,
               (dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as TotalVarCost,

               --WAOnly
               iif(dbo.WorkCodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) / 
               @WACost * @FCost,0)
               as WACalc,

               --WA
               iif(dbo.WorkCodes.WorkCode  = 101,
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) / 
               @WACost * @FCost +
               ((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + 
               (FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) , 0) as WACost


FROM            dbo.DataInput INNER JOIN
                         dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
                         dbo.FleetMake ON dbo.FleetData.FleetMakeId = dbo.FleetMake.FleetMakeId INNER JOIN
                         dbo.FleetModel ON dbo.FleetData.FleetModelId = dbo.FleetModel.ModelId INNER JOIN
                         dbo.FleetType ON dbo.FleetData.FleetTypeId = dbo.FleetType.FleetTypeId INNER JOIN
                         dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
                         dbo.Sites ON dbo.DataInput.SiteId = dbo.Sites.SiteId INNER JOIN
                         dbo.Budget ON dbo.Sites.SiteId = dbo.Budget.SiteId AND dbo.DataInput.Period = dbo.Budget.Period INNER JOIN
                         dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID INNER JOIN
                         dbo.JobCodes ON dbo.DataInput.JobCodeId = dbo.JobCodes.JobCodeId

WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate

Group By dbo.DataInput.DataInputID, dbo.DataInput.DataInputID, dbo.DataInput.ProdDate, dbo.WorkCodes.WorkCode, dbo.DataInput.Hrs,
              dbo.DataInput.Loads, dbo.DataInput.Fuel, dbo.FleetData.FleetNo, dbo.JobCodes.JobCode, 
              dbo.FleetType.EquipmentType, dbo.FleetModel.ModelName, dbo.Sites.SiteName, 
              dbo.JobCodes.JobDetails, dbo.JobCodes.BillMeth, dbo.FleetData.OwnCost, dbo.fleetdata.OpCost, 
              dbo.fleetdata.OwnBill, dbo.FleetData.OpBill,dbo.HRData.EmpNo, dbo.HRData.RatePH, dbo.HRData.RateBill, 
              dbo.DataInput.ShiftHrs, dbo.FleetModel.ModelSize, dbo.budget.BudDate, dbo.Budget.RevBCM, dbo.Budget.PricePerBCM,
              dbo.budget.BCMValue, dbo.budget.SalWage, dbo.budget.Fuel, dbo.budget.PG0, dbo.budget.PG,
              dbo.budget.siteId, dbo.jobcodes.FuelRate, dbo.datainput.workcodeid, dbo.budget.SalWagePerc, dbo.Budget.PGPerc, 
              dbo.WorkCodes.WorkCodeID  

Order By dbo.DataInput.ProdDate
票数 0
EN

Stack Overflow用户

发布于 2016-05-26 10:54:02

只是个通知。

如果你想要整个五月，那么你应该：

代码语言：javascript
复制
Set @FromDate = '20160501'
Set @ToDate   = '20160601'

并检查如下：

代码语言：javascript
复制
( @FromDate <= ProdDate ) AND ( ProdDate < @ToDate )

我想你已经少了一天了。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/37458094

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档