关于如何根据条件创建select语句的问题

Question

对于创建基于条件的select语句(SQL SERVER)的以下问题，我正在寻找一些我应该考虑的选项：

注意:简化实际问题

如果我有两个表，每个表都有一个字段，我需要使用示例：

CUSTOMER.CUSTOMER_ID
PRODUCT.PRODUCT_ID

我还有另外两个表，它们包含基于这两个字段的特殊产品定价示例：

CUSTOMER_PRICING 
***************************************
* CUSTOMER_ID  * PRODUCT_ID  * PRICE  *
*                                     *
*     ABC      *    100      *  5.00  *
***************************************

MARKET_PRICING 
***************************************
* CUSTOMER_ID  * PRODUCT_ID  * PRICE  *
*                                     *
*     ACME      *    200      *  7.00 *
***************************************

对于每个CUSTOMER.CUSTOMER_ID和PRODUCT.PRODUCT_ID，我希望使用左外部联接连接CUSTOMER_PRICING和MARKET_PRICING记录。但这些条件只有在

A)价格字段不为空

B)如果CUSTOMER_ID/PRODUCT_ID两者都存在，我只需要来自CUSTOMER_PRICE的记录

类似于：

IF EXISTS (SELECT C.CUSTOMER_ID, P.PRODUCT_ID FROM CUSTOMER C, PRODUCT P 
           WHERE C.CUSTOMER_ID, P.PRODUCT_ID IN 
           (SELECT PRICE FROM CUSTOMER_PRICE WHERE PRICE IS NOT NULL))
ELSE IF EXISTS 
          (SELECT C.CUSTOMER_ID, P.PRODUCT_ID FROM CUSTOMER C, PRODUCT P 
           WHERE C.CUSTOMER_ID, P.PRODUCT_ID IN 
           (SELECT PRICE FROM MARKET_PRICE WHERE PRICE IS NOT NULL))

*然后以某种方式对这些结果进行连接.....

有什么建议吗？

Answer 1

我想我已经想出了一个解决方案。我相信SQL专家可以想出更好的方法，但这应该会让您行动起来：

WITH CTE AS
(
    SELECT C.CUSTOMER_ID, C.PRODUCT_ID, C.CUSTOMER_PRICE as [PRICE], 1 as [T]
    FROM CUSTOMER_PRICE C WHERE C.CUSTOMER_PRICE IS NOT NULL
    UNION
    SELECT P.CUSTOMER_ID, P.PRODUCT_ID,P.MARKET_PRICE as [PRICE], 2 as [T]
    FROM MARKET_PRICE P WHERE P.MARKET_PRICE IS NOT NULL
)
select CUSTOMER_ID,PRODUCT_ID,
    (
        SELECT TOP (1) PRICE FROM CTE i WHERE i.CUSTOMER_ID = o.CUSTOMER_ID AND i.PRODUCT_ID = o.PRODUCT_ID
        ORDER BY T
    ) as [PRICE]
from CTE o
GROUP BY CUSTOMER_ID,PRODUCT_ID

这将为您提供一个不同的客户ID、产品ID和价格列表；有利于CUSTOMER_PRICE价格。

编辑此选项仅适用于SQL数据库2008及更高版本。它使用了Common Table Expressions，这是2008年的新结构。

更新已修复的where子句。

Answer 2

您可以将CUSTOMER和PRODUCT的叉积与CUSTOMER_PRICING和MARKET_PRICING连接起来，过滤掉CUSTOMER_PRICING.PRICE和MARKET_PRICING.PRICE都为NULL的行。它可能看起来像这样：

SELECT
  c.CUSTOMER_ID,
  p.PRODUCT_ID,
  COALESCE(cp.PRICE, mp.PRICE) AS PRICE
FROM CUSTOMER c
  CROSS JOIN PRODUCT p
  LEFT JOIN CUSTOMER_PRICING cp ON cp.CUSTOMER_ID = c.CUSTOMER_ID AND cp.PRODUCT_ID = p.PRODUCT_ID
  LEFT JOIN MARKET_PRICING   mp ON mp.CUSTOMER_ID = c.CUSTOMER_ID AND mp.PRODUCT_ID = p.PRODUCT_ID
WHERE cp.PRICE IS NOT NULL OR mp.PRICE IS NOT NULL
-- Or, put differently:
-- WHERE NOT (cp.PRICE IS NULL AND mp.PRICE IS NULL)

Answer 3

如果您使用的是ORACLE，则应该使用union来消除重复行，并进行两个单独的查询。