如何在T-SQL中编写select case

how can i write the select case statement in t-sql ? My Code below doesn't work, help I just want the match case, insert statement start insert the value into table.

如何在t-sql中编写select case语句?下面的代码不起作用，帮助我只想要匹配的情况，insert语句开始将值插入到表中。

SET @DayName = dbo.GetWeekDayNameOfDate(@SaleDate)
      SELECT 
            CASE @DayName
                WHEN 'Sunday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Sunday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
                WHEN 'Monday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Monday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
                WHEN 'Tuesday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Tuesday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
                WHEN 'Wednesday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Wednesday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
                WHEN 'Thursday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Thursday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
                WHEN 'Friday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Friday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
                WHEN 'Saturday' THEN 
                    INSERT INTO Tmp_Data(ProductID,ProductName,Saturday,NetProfit) 
                    VALUES(@ProductId,@ProductName,@Qty,@Profit);
            END

2 个解决方案

#1

I agree with @John Dewey's comment - you shouldn't have a column for each weekday, but rather a single column that has the weekday. Assuming for a moment that you can't fix the broken schema, next you need to understand that CASE is an expression that returns a value, not a control of flow statement like it works in some other languages. This means you can't say CASE THEN something THEN go do something else.

我同意@John Dewey的说法——你不应该每个工作日都有一个专栏，而应该有一个工作日的专栏。假设您暂时无法修复损坏的模式，接下来您需要理解这种情况是返回值的表达式，而不是流语句的控件，就像它在其他语言中工作一样。这意味着你不能说CASE然后某事去做某事。

Here is one way to write your statement that will reduce at least some of the redundancy you're attempting:

这里有一种方法可以使你的陈述至少减少一些冗余:

SET @DayName = dbo.GetWeekDayNameOfDate(@SaleDate);

INSERT dbo.Tmp_Data(ProductID,ProductName,NetProfit,
    Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
    SELECT @ProductId,@ProductName,@Profit,
      CASE @DayName WHEN 'Sunday'    THEN @Qty END,
      CASE @DayName WHEN 'Monday'    THEN @Qty END,
      CASE @DayName WHEN 'Tuesday'   THEN @Qty END,
      CASE @DayName WHEN 'Wednesday' THEN @Qty END,
      CASE @DayName WHEN 'Thursday'  THEN @Qty END,
      CASE @DayName WHEN 'Friday'    THEN @Qty END,
      CASE @DayName WHEN 'Saturday'  THEN @Qty END;

You could also also build the statement in dynamic SQL. Not the way I would prefer, just including that option for brevity.

您还可以使用动态SQL构建语句。这不是我喜欢的方式，只是包含了简洁的选项。

SET @DayName = dbo.GetWeekDayNameOfDate(@SaleDate);

DECLARE @sql NVARCHAR(4000);

SET @sql = N'INSERT dbo.Tmp_Data(ProductID,ProductName,NetProfit,' 
    + @DayName + ') SELECT ' 
      + CONVERT(VARCHAR(12), @ProductId) +','
      + '''' + @ProductName + ''','
      + CONVERT(VARCHAR(12), @Profit) + ','
      + CONVERT(VARCHAR(12), @Qty) + ';';

PRINT @sql;
-- EXEC sp_executesql @sql;

#2

-4

Please try this.

请试试这个。

SET @DayName = dbo.GetWeekDayNameOfDate(@SaleDate)
SELECT 
    CASE WHEN @DayName = 'Sunday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Sunday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
        WHEN @DayName = 'Monday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Monday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
        WHEN @DayName= 'Tuesday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Tuesday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
        WHEN @DayName= 'Wednesday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Wednesday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
        WHEN @DayName= 'Thursday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Thursday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
        WHEN @DayName= 'Friday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Friday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
        WHEN @DayName= 'Saturday' THEN 
            INSERT INTO Tmp_Data(ProductID,ProductName,Saturday,NetProfit) 
            VALUES(@ProductId,@ProductName,@Qty,@Profit);
    END

#1