主页 / coding / 问题 / 77698674
Accepted
Bob
Asked: 2023-12-21 22:35:31 +0800 CST

具有两个不同参数的 where 子句中的空值

  • 772

数据库中有以下结构。l_family_id 始终作为参数返回，但 l_account 和 l_product_id 每次 2 之一为 null。如何克服这个问题，以便我在选择“可搜索帐户”或“产品 ID”时不会出现错误。

              such as

            select product_id from tbl27 
                where family_id = l_family_id
                and account  = null
                and product_id = l_product_id
                 
               and   
                  
            select product_id from tbl27 
                where family_id = l_family_id
                and account  = l_product_id
                and product_id = null 


             
            family_id       family   account    product_id

               1            family1    101      10
               1            family1    101      20
               1            family1    103      30
               1            family1    103      40
               2            family2    201      110
               2            family2    201      120
               2            family2    204      130
               2            family2    204      140
            




 CREATE TABLE tbl27 (family_id int, family VARCHAR2(30), account int, product_id 
    int);

      insert into tbl27 (family_id,family,account, product_id)
      SELECT 1,  'family1', 101, 10    FROM DUAL UNION ALL
      SELECT 1,  'family1', 101, 20    FROM DUAL UNION ALL
      SELECT 1,  'family1', 103, 30    FROM DUAL UNION ALL
      SELECT 1,  'family1', 103, 40    FROM DUAL UNION ALL
      SELECT 2,  'family2', 201, 110   FROM DUAL UNION ALL
      SELECT 2,  'family2', 201, 120   FROM DUAL UNION ALL
      SELECT 2,  'family2', 204, 130   FROM DUAL UNION ALL
      SELECT 2,  'family2', 204, 140   FROM DUAL;


        Expected result;

            select product_id from tbl27 
            where family_id = 1
            and account  = 101
            and product_id = null 

            product_id
                10
                20
sql

1 个回答

  1. Best Answer

    我想我明白你的要求。您需要检查参数是否为空或值是否匹配。

    --Load Data
CREATE TABLE #tmp_tbl27 (family_id int, family VARCHAR(30), account int, product_id int);

INSERT INTO #tmp_tbl27 (family_id, family, account, product_id)
    SELECT 1,  'family1', 101, 10    UNION ALL
    SELECT 1,  'family1', 101, 20    UNION ALL
    SELECT 1,  'family1', 103, 30    UNION ALL
    SELECT 1,  'family1', 103, 40    UNION ALL
    SELECT 2,  'family2', 201, 110   UNION ALL
    SELECT 2,  'family2', 201, 120   UNION ALL
    SELECT 2,  'family2', 204, 130   UNION ALL
    SELECT 2,  'family2', 204, 140   

-- Example        
DECLARE @familyId int = 1;
DECLARE @accountId int = 101;
DECLARE @productId int = null;

select product_id from #tmp_tbl27 
where family_id = @familyId
and (@accountId is null OR account = @accountId)
and (@productId is null OR product_id = @productId)

    在此输入图像描述

    • 0

相关问题