如何在存储过程中为同一列使用两个参数?

我试图在Stack overflow上找到一个类似的问题,但没有成功……。

这是我的存储过程代码。

USE [DATABASENAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SPNAME]
  @IN_dtmin datetime, 
  @IN_dtmax datetime = null, 
  @IN_key varchar(500), 
  @IN_set varchar(80), 
  @IN_locktype varchar(500)
WITH EXEC AS CALLER
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT IDLOCK, DT, LOCKTYPE,
       MAX(CASE WHEN PRODUCTATTRIBUTE = 'CodeSet' THEN  VALUE END) AS CodeSet,
       MAX(CASE WHEN PRODUCTATTRIBUTE = 'KeySet' THEN  VALUE END) AS KeySet
FROM LOCKREGISTER LR LEFT JOIN 
     LOCKTYPES T
     ON LR.IDLOCKTYPE = T.IDLOCKTYPE LEFT JOIN 
     PRODUCTATTRIBUTES PA
     ON LR.IDPRODUCT = PA.IDPRODUCT AND 
        PRODUCTATTRIBUTE IN ('CodeSet','KeySet')
WHERE LR.DT BETWEEN IIF(@IN_DTMIN IS NULL,GETDATE(),@IN_DTMIN) AND IIF(@IN_DTMAX IS NULL,GETDATE(),@IN_DTMAX)
  AND (PA.VALUE like ISNULL(@IN_key,'%') OR (PA.VALUE IS NULL AND @IN_key IS NULL)) 
  AND (PA.VALUE like ISNULL(@IN_set,'%') OR (PA.VALUE IS NULL AND @IN_set IS NULL))
  AND (T.LOCKTYPE like ISNULL(@IN_locktype,'%') OR (T.LOCKTYPE IS NULL AND @IN_locktype IS NULL))
GROUP BY IDLOCK, DT, LOCKTYPE;
END

这是我不使用任何过滤器的结果。

enter image description here

这是我执行存储过程的结果,如果我用 dates filterSet 过滤器。

enter image description here

如上图所示,在CodeSet列中,结果变成了 “NULL”。

如何在执行SP时保持CodeSet列的值?有些过滤器在某些情况下可能是空的)。

这将是第一行所需结果的一个例子。

enter image description here

解决方案:

我认为问题在于 PRODUCTATTRIBUTES 是一个实体属性值(EAV)表,这在查询中是出了名的难处理。

请注意,在第二次执行包含参数的查询中,你为 @IN_set 出现在 KeySet 列,因为你没有区分 PRODUCTATTRIBUTE 值 (CodeSetKeySet)在您的 WHERE 子句。

为了简化这样的查询逻辑,我通常会重复连接到EAV表,创建只包含每个属性所需的键值对的子查询。因此,在这种情况下,我将连接到 PRODUCTATTRIBUTES 两次,一次是为了取回 CodeSet 兴趣值,然后再得到 KeySet 值。我还会将聚合转移到这些子查询上,这样可以消除歧义,也可以减少拉入内存的数据量。

当然,由于缺乏表和数据来测试,这还没有经过测试,但根据经验猜测,新的结构应该是这样的。

SELECT IDLOCK, DT, LOCKTYPE,
       CS.CodeSet,
       KS.KeySet
FROM LOCKREGISTER LR LEFT JOIN 
     LOCKTYPES T
     ON LR.IDLOCKTYPE = T.IDLOCKTYPE LEFT JOIN 
     ( -- Sub-query to get 'CodeSet' values by IDPRODUCT
      SELECT 
        IDPRODUCT,
        MAX(VALUE) AS CodeSet
      FROM PRODUCTATTRIBUTES
      WHERE PRODUCTATTRIBUTE = 'CodeSet'
      GROUP BY IDPRODUCT
     ) AS CS
     ON LR.IDPRODUCT = CS.IDPRODUCT LEFT JOIN
     (  -- Sub-query to get 'KeySet' values by IDPRODUCT
      SELECT 
        IDPRODUCT,
        MAX(VALUE) AS KeySet
      FROM PRODUCTATTRIBUTES
      WHERE PRODUCTATTRIBUTE = 'KeySet'
      GROUP BY IDPRODUCT
     ) AS KS
     ON LR.IDPRODUCT = KS.IDPRODUCT

WHERE LR.DT BETWEEN IIF(@IN_DTMIN IS NULL,GETDATE(),@IN_DTMIN) AND IIF(@IN_DTMAX IS NULL,GETDATE(),@IN_DTMAX)
  AND (KS.KeySet like ISNULL(@IN_key,'%') OR (KS.KeySet IS NULL AND @IN_key IS NULL)) 
  AND (CS.CodeSet like ISNULL(@IN_set,'%') OR (CS.CodeSet IS NULL AND @IN_set IS NULL))
  AND (T.LOCKTYPE like ISNULL(@IN_locktype,'%') OR (T.LOCKTYPE IS NULL AND @IN_locktype IS NULL));

给TA打赏
共{{data.count}}人
人已打赏
未分类

条件性超时

2022-9-9 3:51:22

未分类

在DT中,两个可编辑的列互相计算。

2022-9-9 4:02:16

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索