sql - 根据参数的条件WHERE

Question

我创建了一个报告，但是我对您来说可能很简单，但是我想不出任何可以让我解决问题的方法。

我已经基于SQL查询(具有多个CTE)创建了一个报告，该报告应通过以下方式参数化:

where
deliverydate between @FromDate and @ToDate
and
carrierid = @Carrier

到目前为止，它一直在工作。但是我想创建另一个具有3个值的参数。根据这些值，我想获取所有符合条件的记录。像这样排序:

当参数值= 1时，我想获取每条记录

当参数值= 2时，我希望每条记录在column1 <>
column2

当参数值= 3时，我想获取每条记录，其中
column1 = column2

Column1和column2是我创建的报告中的列。
最终选择如下所示:

SELECT  SALESID ,
        CARRIERID ,
        dlvmodeid ,
        SUM(totalweight) AS totalweight ,
        SUM(totalcharges) AS totalcharges ,
        TOTALCHARGESCURRENCY ,
        SUM(HowManyPackagesForThisSO) AS HowManyPackagesForThisSO ,
        SUM(HowManyPackagesForThisSO_st) AS HowManyPackagesForThisSO_st ,
        SUM(InHowManyDays) AS InHowManyDays ,
        SUM(cspjnumber) AS HowManyPackingSlip
FROM    countingcte
WHERE   deliverydate BETWEEN @FromDate AND @ToDate
        AND carrierid = @Carrier
GROUP BY SALESID ,
        CARRIERID ,
        dlvmodeid ,
        TOTALCHARGESCURRENCY

有人知道我的问题的解决方案吗？

我想到的确切列是HowManyPackagesForThisSO和HowManyPackagesForThisSO_st。

Answer 1

将此添加到您的WHERE子句中:

AND ((@Param = 1) OR
     (@Param = 2 AND col1 <> col2) OR
     (@Param = 3 AND col1 = col2))