SQL 中的多个 WHERE 函数

Question

我正在尝试编写一个SQL查询，以返回信息，包括产品代码，每年的销售收入，成本，销售人员来自两个不同表的信息。我需要返回“北方”地区的产品ID信息的最低登机成本。

我使用其中region ='north'只是获取北部地区的产品信息，而 将其排序至高点并找到产品成本最低。是否有一种方法只是返回北部地区的登机成本最低的产品？

Answer 1

在 WHERE 子句中，您可以添加：

... AND onboarding cost=(SELECT MAX(onboarding cost) FROM ... WHERE region='North' ...)

 SELECT 
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North" 
AND ProductRevenueAndCosts.OnboardingCost=(
   SELECT MAX(ProductRevenueAndCosts.OnboardingCost)
   FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
   SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
   WHERE SalesPeople.Region = "North"
)
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC -- this is obsolete
;

但这最终会返回多个结果 - 以防出现多个结果比一种产品具有相同的最高入职成本。

Answer 2

是的，您需要限制记录数，如下所示：

MySQL、PostgreSQL

SELECT 
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North"
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC
LIMIT 0, 1;

其中 0 是起始索引（第一行），1 是您想要获取的记录数。

SQL Server

SELECT TOP 1
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North"
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC;

其中 TOP 1 告诉 RDBMS 您只对第一行感兴趣。

甲骨文

SELECT 
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North"
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;