我的查询回复了多个查询。 [英] My query replying multiple queries.

问题描述

  SELECT  
 a.CustomerName 
，a.AgreementValue 
，a.TotalValue  - a.AgreementValue  AS  BlackAmount 
，（ SELECT  SUM（PaidAmount） AS  Paid2Me  FROM  AmountMaster  WHERE  AmountTypeId =  2   AND  PaymentToId =  1   AND  CustomerId =  8   AND  PlaceId =  1 ） AS  Paid2Me 
，（ SELECT  SUM（PaidAmount） AS  Paid2Builder  FROM  AmountMaster  WHERE  AmountTypeId =  2   AND  PaymentToId =  2  和 CustomerId = < span class =code-digit> 8  和 PlaceId =  1 ） AS  Paid2Builder 
，（ SELECT （（ SELECT  SUM（PaidAmount） AS  Paid2Me  FROM  AmountMaster  WHERE  AmountTypeId =  2   AND  PaymentToId =  1   AND  CustomerId =  8   AND  PlaceId =  1   GROUP   BY  CustomerId） - （< span class =code-keyword> SELECT  SUM（PaidAmount）  AS  PaidAmount  FROM  AmountMaster  WHERE  AmountTypeId = < span class =code-digit> 2  和 PaymentToId =  2   AND  CustomerId =  8   AND  PlaceId =  1   GROUP   BY  CustomerId）） AS  DueAmount） AS  DueAmount 
  FROM  
 CustomerDetails a 
  JOIN  AmountMaster b  ON  a.CustomerId = b.CustomerId 
  WHERE  
 a.CustomerId =  8   AND  b.PlaceId =  1  

这是我的查询，并且多次执行相同的记录，但我只想要一条记录，如何更改此查询只获得一条记录？



我尝试使用order by和group by但是它抛出错误



我得到的回答是这样的

测试800 200 20000 NULL NULL 
测试800 200 20000 NULL NULL 
测试800 200 20000 NULL NULL 

解决方案

也许你可以试试

  SELECT  
 a.CustomerName 
，a.AgreementValue 
，a.TotalValue  -  a.AgreementValue  AS  BlackAmount 
，（ SELECT  SUM（PaidAmount） FROM  AmountMaster  WHERE  AmountTypeId =  2   AND  PaymentToId =  1   AND  CustomerId =  8   AND  PlaceId =  1 ） AS  Paid2Me 
，（ SELECT  SUM（PaidAmount） FROM  AmountMaster  WHERE  AmountTypeId =  2   AND  PaymentToId =  2  和 CustomerId =  8  和 PlaceId =  1 ） AS  Paid2Builder 
，Paid2Me  -  Paid2Builder  AS  DueAmount 
  FROM  
 CustomerDetails a 
  JOIN  AmountMaster b  ON  a.CustomerId = b.CustomerId 
  WHERE  
 a.CustomerId =  8   AND  b.PlaceId =  1  

SELECT
  a.CustomerName
 ,a.AgreementValue
 ,a.TotalValue - a.AgreementValue AS BlackAmount
 ,(SELECT SUM(PaidAmount) AS Paid2Me FROM AmountMaster WHERE AmountTypeId = 2 AND PaymentToId = 1 AND CustomerId = 8 AND PlaceId = 1) AS Paid2Me
 ,(SELECT SUM(PaidAmount) AS Paid2Builder FROM AmountMaster WHERE AmountTypeId = 2 AND PaymentToId = 2 and CustomerId = 8 and PlaceId = 1) AS Paid2Builder
 ,(SELECT ((SELECT SUM(PaidAmount) AS Paid2Me FROM AmountMaster WHERE AmountTypeId = 2 AND PaymentToId = 1 AND CustomerId = 8 AND PlaceId = 1 GROUP BY CustomerId) - (SELECT SUM(PaidAmount) AS PaidAmount FROM AmountMaster WHERE AmountTypeId = 2 and PaymentToId = 2 AND CustomerId = 8 AND PlaceId = 1 GROUP BY CustomerId)) AS DueAmount) AS DueAmount 
FROM
 CustomerDetails a
 JOIN AmountMaster b ON a.CustomerId = b.CustomerId
WHERE
  a.CustomerId = 8 AND b.PlaceId = 1

This is my query and is executing same records many times, But i want only one record, How can I change this query to get only one record?

I tried using order by and group by but it's throwing error

And i'm getting answer is like this

Testing 800 200 20000   NULL    NULL
Testing 800 200 20000   NULL    NULL
Testing 800 200 20000   NULL    NULL

解决方案

Maybe you could try

SELECT
  a.CustomerName
 ,a.AgreementValue
 ,a.TotalValue - a.AgreementValue AS BlackAmount
 ,(SELECT SUM(PaidAmount) FROM AmountMaster WHERE AmountTypeId = 2 AND PaymentToId = 1 AND CustomerId = 8 AND PlaceId = 1) AS Paid2Me
 ,(SELECT SUM(PaidAmount) FROM AmountMaster WHERE AmountTypeId = 2 AND PaymentToId = 2 and CustomerId = 8 and PlaceId = 1) AS Paid2Builder
 ,Paid2Me - Paid2Builder AS DueAmount
FROM
 CustomerDetails a
 JOIN AmountMaster b ON a.CustomerId = b.CustomerId
WHERE
  a.CustomerId = 8 AND b.PlaceId = 1

这篇关于我的查询回复了多个查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！