当前位置：首页 → 问答吧 → SQL触发器case when 判断问题！！

SQL触发器case when 判断问题！！

时间：2011-11-09

来源：互联网

SQL code


create CreditCard_Cost(
ID INT,
Username varchar(20),
Bank varchar(20),
Monetray money,
Post_date datetime,
Ftype int
)
create --日志表
LOG_Card_Cost(
Username varchar(20),
Money_amount money,
Money1,
Money2,
)
create CreditCard_Type
Ftype int,
Properties varchar(20),
Mtype varchar(20)

SQL code


测试数据：
insert into dbo.CreditCard_Cost
select '03011' ,'中国银行' ,'-200' ,GETDATE(),0
UNION ALL   select'03012' ,'中国银行' ,'-300' ,GETDATE(),1
UNION ALL   select '03013' ,'中国银行' ,'-400' ,GETDATE(),2
UNION ALL   select '03014' ,'中国银行' ,'-500' ,GETDATE(),3
UNION ALL   select '03015' ,'中国银行' ,'-600' ,GETDATE(),4
UNION ALL   select '03016' ,'中国银行' ,'-700' ,GETDATE(),5

insert into dbo.CreditCard_Type
select 0 ,-1,1
UNION ALL   select 1,1,1
UNION ALL   select 2,-1,2
UNION ALL   select 3,1,2
UNION ALL   select 4,-1,3
UNION ALL   select 5,1,3

SQL code


ALTER TRIGGER [dbo].[CreditCard_Cost_UPDATE]  ON [dbo].[CreditCard_Cost]
AFTER UPDATE 
AS 
    BEGIN            
        update t
        set t.Money_amount = t.Money_amount + isnull(case when x.Mtype = 1 then  x.Monetary1 end, 0) 
                                            - isnull(case when y.Mtype = 1 then  y.Monetary2 end,0),
            
            t.Money_1 = t.Money_1 + isnull(case when x.Mtype = 2 then  x.Monetary1 end, 0) 
                                    - isnull(case when y.Mtype = 2 then  y.Monetary2 end,0),
            
            t.Money_2 = t.Money_2 + isnull(case when x.Mtype = 3 then x.Monetary1 end, 0) 
                                    - isnull(case when y.Mtype = 3 then  y.Monetary2 end,0)
        
        from LOG_Card_Cost t 
             LEFT join (select i.Username,sum( c.Properties*i.monetary  ) Monetary1
                                          ,c.Mtype     from inserted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as x on t.Username = x.Username
                                            
             LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
                                          ,c.Mtype     from deleted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as y on t.Username = y.Username
                            
            where x.Username is not null  or y.Username    is not null
    END

我写的触发器如上，要求判断Mtype的值11，22，33不用case when 要怎么写，我想不出来，（老大要求）
CreditCard_Type，Ftype和Properties 字段不可以改变，其他可以扩展表

作者: mmm987456321 发布时间: 2011-11-09

比起代码我更喜欢楼主的图像

作者: Beirut 发布时间: 2011-11-09

谢谢

作者: mmm987456321 发布时间: 2011-11-09

那就写三条语句,分别计算.
update t set ... where x.Mtype = 1 and y.Mtype = 1
update t set ... where x.Mtype = 2 and y.Mtype = 2
update t set ... where x.Mtype = 3 and y.Mtype = 3

作者: dawugui 发布时间: 2011-11-09

引用 3 楼 dawugui 的回复:

那就写三条语句,分别计算.
update t set ... where x.Mtype = 1 and y.Mtype = 1
update t set ... where x.Mtype = 2 and y.Mtype = 2
update t set ... where x.Mtype = 3 and y.Mtype = 3

不用case ，不用dawugui的这个方式，还真想不到别的合理点儿的了。

作者: OrchidCat 发布时间: 2011-11-09

分开判断
SQL code


--try
if @Mtype =1
elseif  @Mtype =2
else  @Mtype =3

作者: Beirut 发布时间: 2011-11-09

SQL code

ALTER TRIGGER [dbo].[CreditCard_Cost_UPDATE]  ON [dbo].[CreditCard_Cost]
AFTER UPDATE 
AS 
    BEGIN            
        update t
        set t.Money_amount = t.Money_amount + isnull(x.Monetary1, 0) 
                                            - isnull(y.Monetary2,0),
            
        from LOG_Card_Cost t 
             LEFT join (select i.Username,sum( c.Properties*i.monetary  ) Monetary1
                                          ,c.Mtype     from inserted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as x on t.Username = x.Username
                                            
             LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
                                          ,c.Mtype     from deleted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as y on t.Username = y.Username
                            
            where x.Username is not null  or y.Username    is not null 
                and x.Mtype = 1 and y.Mtype = 1

        update t
        set t.Money_1 = t.Money_1 + isnull(x.Monetary1, 0) 
                                    - isnull(y.Monetary2,0),
        from LOG_Card_Cost t 
             LEFT join (select i.Username,sum( c.Properties*i.monetary  ) Monetary1
                                          ,c.Mtype     from inserted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as x on t.Username = x.Username
                                            
             LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
                                          ,c.Mtype     from deleted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as y on t.Username = y.Username
                            
            where x.Username is not null  or y.Username    is not null
                and x.Mtype = 2 and y.Mtype = 2
        update t
        set t.Money_2 = t.Money_2 + isnull(x.Monetary1, 0) 
                                    - isnull(y.Monetary2,0)
        from LOG_Card_Cost t 
             LEFT join (select i.Username,sum( c.Properties*i.monetary  ) Monetary1
                                          ,c.Mtype     from inserted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as x on t.Username = x.Username
                                            
             LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
                                          ,c.Mtype     from deleted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as y on t.Username = y.Username
                            
            where x.Username is not null  or y.Username    is not null
                and x.Mtype = 3 and y.Mtype = 3
    END

作者: qianjin036a 发布时间: 2011-11-09

不用case..when 难道用if..else?

作者: fredrickhu 发布时间: 2011-11-09

求判断Mtype的值11，22，33不用case when 要怎么写]
------------------
是不觸發?還是

作者: roy_88 发布时间: 2011-11-09

引用 5 楼 beirut 的回复:

分开判断
SQL code

--try
if @Mtype =1
elseif @Mtype =2
else @Mtype =3

小爱，你太坏了....

作者: OrchidCat 发布时间: 2011-11-09

能用case when不用?

把兩個left join 用full join連接再更新

作者: roy_88 发布时间: 2011-11-09

引用 4 楼 orchidcat 的回复:

引用 3 楼 dawugui 的回复:

那就写三条语句,分别计算.
update t set ... where x.Mtype = 1 and y.Mtype = 1
update t set ... where x.Mtype = 2 and y.Mtype = 2
update t set ... where x.Mtype = 3 and y.Mtype = 3

不……

老大的意思是，用case WHEN 没效率，这是测试数据表，公司的表有几十条字段如：1.1.2.2.3.3....33.33这样的，如何用case when 就.....
他的写法要类是
SQL code


 LEFT join (select i.Username,sum(c.Properties*i.monetary ) Monetary2
                                          ,c.Mtype     from deleted as i
                        LEFT JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype group by i.Username,c.Mtype    
                        ) as y on t.Username = y.Username

这样的想法，直接取出来....提示可以扩展CreditCard_Type表，（Ftype和Properties 字段不可以改变）

作者: mmm987456321 发布时间: 2011-11-09

引用 5 楼 beirut 的回复:
分开判断

SQL code

--try
if @Mtype =1
elseif @Mtype =2
else @Mtype =3

估计你老大就是想让你用if ... elseif...,

作者: smilysoft 发布时间: 2011-11-09

SQL code

ALTER TRIGGER [dbo].[CreditCard_Cost_UPDATE]  ON [dbo].[CreditCard_Cost]
AFTER UPDATE 
AS 
    BEGIN            
         UPDATE t
        SET Money_amount=t.Money_amount
        +ISNULL((select sum(c.Properties*i.monetary  )  from inserted as i inner JOIN  dbo.CreditCard_Type  as c on i.Ftype=c.Ftype   AND c.Mtype =1 AND i.Username=t.Username),0)
        -isnull((select sum(c.Properties*i.monetary ) from deleted as i INNER JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype  AND c.Mtype =1  AND i.Username=t.Username),0)
        ,Money_1=Money_1
        +ISNULL((select sum(c.Properties*i.monetary  )  from inserted as i inner JOIN  dbo.CreditCard_Type  as c on i.Ftype=c.Ftype   AND c.Mtype =2 AND i.Username=t.Username),0)
        -isnull((select sum(c.Properties*i.monetary ) from deleted as i INNER JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype  AND c.Mtype =1  AND i.Username=t.Username),0)
        ,Money_2=Money_2
        +ISNULL((select sum(c.Properties*i.monetary  )  from inserted as i inner JOIN  dbo.CreditCard_Type  as c on i.Ftype=c.Ftype   AND c.Mtype =3 AND i.Username=t.Username),0)
        -isnull((select sum(c.Properties*i.monetary ) from deleted as i INNER JOIN     dbo.CreditCard_Type    as c on i.Ftype=c.Ftype  AND c.Mtype =3  AND i.Username=t.Username),0)
        FROM LOG_Card_Cost T
          
            
    END

作者: roy_88 发布时间: 2011-11-09

引用 11 楼 mmm987456321 的回复:

引用 4 楼 orchidcat 的回复:

引用 3 楼 dawugui 的回复:

那就写三条语句,分别计算.
update t set ... where x.Mtype = 1 and y.Mtype = 1
update t set ... where x.Mtype = 2 and y.Mtype = 2
update t set ... where x.Mtype =……

如果要效率，分開效率高，先調用inserted,再用deleted減

作者: roy_88 发布时间: 2011-11-09

LEFT JOIN子查询太多一样的效率不高

作者: fredrickhu 发布时间: 2011-11-09

SQL code

ALTER TRIGGER [dbo].[CreditCard_Cost_UPDATE]  ON [dbo].[CreditCard_Cost]
AFTER UPDATE 
AS 
    BEGIN            
        UPDATE a
        SET Money_amount=a.Money_amount+b.Money_amount,
            Money_1=a.Money_1+b.Money_1,
            Money_2=a.Money_2+b.Money_2
        FROM 
            LOG_Card_Cost AS a
        INNER JOIN 
        ( 
            SELECT 
                Username,Money_amount=SUM(CASE WHEN t3.Mtype =1 THEN monetary*Properties ELSE 0 END),Money_1=SUM(CASE WHEN t3.Mtype =2 THEN monetary*Properties ELSE 0 END),
                Money_2=SUM(CASE WHEN t3.Mtype =3 THEN monetary*Properties ELSE 0 END)
            FROM (SELECT Ftype,Username,SUM(monetary) AS monetary FROM (SELECT monetary,Ftype,Username FROM INSERTED UNION ALL SELECT -monetary,Ftype,Username FROM DELETED)t
            GROUP BY Ftype,Username)t2
            INNER JOIN CreditCard_Type AS t3 ON t2.Ftype=t3.Ftype
        )b on a.Username=b.Username
                  
    END

可這樣測試

作者: roy_88 发布时间: 2011-11-09

可按以上方法試試

作者: roy_88 发布时间: 2011-11-09

SQL触发器case when 判断问题！！

热门阅读

热门下载