SQL Server StoredProcedure中的问题 [英] Problem in SQL Server StoredProcedure

问题描述

很长一段时间，Iam在下面的查询中遇到错误： -



< pre lang =sql> SELECT * FROM （ SELECT dbo.Consumers.ConsumerCode，dbo.Consumers.Name，dbo.Tariff.TariffID，dbo.ConsumerAdditionalCost.ConnectedLoad，MAX（dbo.MeterRecord.kWh_del），dbo.Deposit.CurrentBalance，ROW_NUMBER（） OVER （ ORDER BY dbo.Consumers.Name） as row FROM dbo.Consumers JOIN
dbo.ConsumerAdditionalCost ON dbo.Consumers.ID = dbo.ConsumerAdditionalCost.ConsumerId JOIN dbo.Deposit ON dbo.Consumers.ID = dbo.Deposit.ConsumerId JOIN db o.Tariff ON Tariff.ID = Consumers.TariffId JOIN dbo.MeterInfo ON Consumers.ID = MeterInfo.ConsumerID INNER JOIN dbo.MeterRecord ON MeterRecord.MeterID = MeterInfo.ID WHERE dbo.Consumers.Blocked = 1 和 Tariff.IsActive = 1 和 ConsumerAdditionalCost.IsActive = 1）a
WHERE row> （ @ pageIndex ）和行< =（（ @ pageIndex ）+ @ itemsPerPage）

我想取最大值只有一个值，即MAX（dbo） .MeterRecord.kWh_del）。那时它显示错误，甚至不是以可执行的形式。当我避免''MAX（dbo.MeterRecord.kWh_del）''它是一个可执行的形式，但逻辑上它是在创建错误。 />


请查看上面的查询和帮助。

解决方案

首先提出建议......而不是完全限定每列使用表中的别名...例如

  SELECT  csmr.ConsumerCode  FROM  dbo.Consumers  AS  csmr 

- 它会使您的代码减少噪音并且更易于阅读 - 这通常会使代码更容易发现问题 - 这就是我所做的在这里找到错误



包含

MAX（dbo.MeterRecord）时出现错误的原因.kWh_del）是因为你没有 GROUP BY 条款



这样的东西可能有用（注意我还没有测试过这个）

  SELECT  ConsumerCode，Name，TariffID，ConnectedLoad，
 CurrentBalance，ROW_NUMBER（） OVER （ ORDER   BY  A.Name） as 行，MAX（kWh_del）
  FROM  
（ SELECT  A.ConsumerCode，A.Name，D.TariffID，B.ConnectedLoad，F.kWh_del，C.CurrentBalance 
  FROM  dbo.Consumers A 
  JOIN  dbo.ConsumerAdditionalCost B  ON  A.ID = B.ConsumerId 
  JOIN  dbo.Deposit C  ON  A.ID = C.ConsumerId 
  JOIN  dbo.Tariff D  ON  D.ID = A.TariffId 
  JOIN  dbo。 MeterInfo E  ON  A.ID = E.ConsumerID 
  INNER   JOIN  dbo.MeterRecord F  ON  F.MeterID = E.ID 
  WHERE  A.Blocked = 1 和 D.IsActive = 1 和 B.IsActive = 1）a 
  WHERE 行> （ @ pageIndex ）和行< =（（ @ pageIndex ）+ @ itemsPerPage）
  GROUP   BY   1 ， 2 ， 3 ， 4 ， 5  

最后，但是这个是个人偏好的东西，我喜欢使用临时表，以便我可以看到我真正得到的数据，然后担心它是否适合单独的步骤

Hi,
It''s for a long time,Iam strugling with the errors in the below query:-

SELECT *  FROM(SELECT dbo.Consumers.ConsumerCode,dbo.Consumers.Name,dbo.Tariff.TariffID,dbo.ConsumerAdditionalCost.ConnectedLoad,MAX(dbo.MeterRecord.kWh_del),dbo.Deposit.CurrentBalance,ROW_NUMBER() OVER (ORDER BY dbo.Consumers.Name) as row FROM dbo.Consumers JOIN
dbo.ConsumerAdditionalCost ON dbo.Consumers.ID=dbo.ConsumerAdditionalCost.ConsumerId JOIN dbo.Deposit ON dbo.Consumers.ID=dbo.Deposit.ConsumerId JOIN dbo.Tariff ON Tariff.ID=Consumers.TariffId JOIN dbo.MeterInfo ON Consumers.ID=MeterInfo.ConsumerID INNER JOIN dbo.MeterRecord ON MeterRecord.MeterID=MeterInfo.ID  WHERE dbo.Consumers.Blocked=1 and Tariff.IsActive=1 and ConsumerAdditionalCost.IsActive=1)a
 WHERE  row > (@pageIndex) and row <= ((@pageIndex)+@itemsPerPage)

I want to take the maximum value of only one value,that is MAX(dbo.MeterRecord.kWh_del).That time it is showing errors and not even in an executable form.When Iam avoiding ''MAX(dbo.MeterRecord.kWh_del)'' it is in an executable form but logically it is creating errors.

Please look at the above query and help.

解决方案

Firstly a word of advice ... instead of fully qualifying each column use aliases on your tables ... for example

SELECT csmr.ConsumerCode FROM dbo.Consumers AS csmr

- it will make your code less noisy and easier to read - which often makes it easier to spot problems - it''s what I did to find the error here

The reason that you are getting errors when you include
MAX(dbo.MeterRecord.kWh_del) is because you have no GROUP BY clause

Something like this might work (note I have not tested this)

SELECT ConsumerCode,Name,TariffID, ConnectedLoad,
CurrentBalance, ROW_NUMBER() OVER (ORDER BY A.Name) as row, MAX(kWh_del)
FROM
(SELECT A.ConsumerCode,A.Name,D.TariffID, B.ConnectedLoad, F.kWh_del, C.CurrentBalance
FROM dbo.Consumers A
JOIN dbo.ConsumerAdditionalCost B ON A.ID=B.ConsumerId
JOIN dbo.Deposit C ON A.ID=C.ConsumerId
JOIN dbo.Tariff D ON D.ID=A.TariffId
JOIN dbo.MeterInfo E ON A.ID=E.ConsumerID
INNER JOIN dbo.MeterRecord F ON F.MeterID=E.ID
WHERE A.Blocked=1 and D.IsActive=1 and B.IsActive=1) a
 WHERE  row > (@pageIndex) and row <= ((@pageIndex)+@itemsPerPage)
 GROUP BY 1,2,3,4,5

Finally, but this is a personal preference thing, I like to use temporary tables so that I can see what data I''m really getting and then worry about whether or not it will fit on a page in a separate step

这篇关于SQL Server StoredProcedure中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！