带有 IF ELSE 的 SQL 脚本无法正常工作

Question

我使用的是 SQL Server 2000。有一个名为 Trialbalance_diff 的表。结构如下图所示。

accno       des                 month   diff

1010011001  Cash in Hand          5     -732230.0
1030033001  Seylan Bank           4      309042.0
1050011001  Lease Debtors         2        9899.0
1050011002  Lease VAT Suspense    5        2240.0

我需要将这些数据重写到另一个名为 temp_TB_Diff 的表中。应根据月份插入字段Diff。像这样，

acc_code    Acc_desc               Jan           Feb       Mar      Apr     May

1010011001  Cash in Hand                                             -732230.0
1030033001  Seylan Bank                                    309042.0
1050011001  Lease Debtors               9899.0
1050011002  Lease VAT Suspense                                          2240.0

我为此编写了这个SQL脚本。

DECLARE @month int;

SELECT @month = [month] FROM trialbalance_diff

BEGIN 

IF @month = 1
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, jan)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 2
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Feb)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 3
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Mar)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 4
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Apr)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 5
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, May)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 6
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, June)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 7
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, July)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 8
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Aug)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 9
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Sep)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 10
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Oct)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 11
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, Nov)
    SELECT accno, des, diff FROM trialbalance_diff
ELSE IF @month = 12
    INSERT INTO temp_TB_Diff(acc_code, acc_desc, [Dec])
    SELECT accno, des, diff FROM trialbalance_diff

END

除了一个小问题之外，它可以工作。数据不会插入特定月份。应该进入不同月份列的值仅插入到一列中。

看起来像这样，

acc_code    Acc_desc            Jan      Feb       Mar       Apr        May

1010011001  Cash in Hand                                             -732230.0
1030033001  Seylan Bank                                               309042.0
1050011001  Lease Debtors                                               9899.0
1050011002  Lease VAT Suspense                                          2240.0

我在脚本中缺少什么导致了这个问题？我似乎不明白为什么。

任何帮助将不胜感激。非常感谢。

Answer 1

标量变量只是一个值。当您分配变量时，您认为哪一行填充该变量？ SQL Server 将选择任意行。在本例中，它选择了 5，因此只执行了其中一个查询。暴力破解的简单方法是：

INSERT INTO temp_TB_Diff(acc_code, acc_desc, Jan)
    SELECT accno, des, diff FROM trialbalance_diff WHERE Month = 1;

INSERT INTO temp_TB_Diff(acc_code, acc_desc, Feb)
    SELECT accno, des, diff FROM trialbalance_diff WHERE Month = 2;

INSERT INTO temp_TB_Diff(acc_code, acc_desc, Mar)
    SELECT accno, des, diff FROM trialbalance_diff WHERE Month = 3;

...

Answer 2

用例语句如下：

 INSERT INTO temp_TB_Diff
    SELECT 
    accno, 
    des, 
    case when month = 1 then diff end as jan,
    case when month = 2 then diff end as feb,
    case when month = 3 then diff end as mar,
    case when month = 4 then diff end as apr
    ....

    from trialbalance_diff