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

mmm987456321 2011-11-09 10:56:34 


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)





测试数据：

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





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 字段不可以改变，其他可以扩展表


...全文
584 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
mmm987456321 2011-11-10
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 roy_88 的回复:]

引用 16 楼 roy_88 的回复:
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,
……


改改……
[/Quote]
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)


这不是也用到了case when，Mtype 的效果是为了影响Money_amount money,
Money1,
Money2,这3个的值，用1，1，2，2，3，3的来方法是我自己想出来的，我老大只是给了这个题目，但是我去交的时候,才叫我不要用case when
mmm987456321 2011-11-10
  • 打赏
  • 举报
 回复
thank you for everyone
mmm987456321 2011-11-10
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 q465897859 的回复:]

最后那张表，扩张FM1,fM2,fM3,里面存0和1值看下能不能实现你的要求
形成矩阵表
[/Quote]

就是这方法好啊，恍然大悟
q465897859 2011-11-10
  • 打赏
  • 举报
 回复
最后那张表，扩张FM1,fM2,fM3,里面存0和1值看下能不能实现你的要求
形成矩阵表
q465897859 2011-11-10
  • 打赏
  • 举报
 回复
最后那张表，扩张FM1,fM2,fM3,里面存0和1值看下能不能实现你的要求
形成矩阵表
中国风 2011-11-09
  • 打赏
  • 举报
 回复
可按以上方法試試
中国风 2011-11-09
  • 打赏
  • 举报
 回复 
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

可這樣測試
--小F-- 2011-11-09
  • 打赏
  • 举报
 回复
LEFT JOIN子查询太多 一样的效率不高
中国风 2011-11-09
  • 打赏
  • 举报
 回复
[Quote=引用 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 =……
[/Quote]

如果要效率，分開效率高，先調用inserted,再用deleted減
中国风 2011-11-09
  • 打赏
  • 举报
 回复 
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
PB菜鸟 2011-11-09
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 beirut 的回复:]
分开判断

SQL code


--try
if @Mtype =1
elseif @Mtype =2
else @Mtype =3
[/Quote]
估计你老大就是想让你用if ... elseif...,
mmm987456321 2011-11-09
  • 打赏
  • 举报
 回复
[Quote=引用 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


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


 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 字段不可以改变）
中国风 2011-11-09
  • 打赏
  • 举报
 回复
能用case when不用?

把兩個left join 用full join連接再更新
Mr_Nice 2011-11-09
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 beirut 的回复:]

分开判断
SQL code

--try
if @Mtype =1
elseif @Mtype =2
else @Mtype =3
[/Quote]

小爱，你太坏了....
中国风 2011-11-09
  • 打赏
  • 举报
 回复
求判断Mtype的值11，22，33不用case when 要怎么写]
------------------
是不觸發?還是
--小F-- 2011-11-09
  • 打赏
  • 举报
 回复
不用case..when 难道用if..else?
-晴天 2011-11-09
  • 打赏
  • 举报
 回复 
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
黄_瓜 2011-11-09
  • 打赏
  • 举报
 回复
分开判断


--try

if @Mtype =1

elseif  @Mtype =2

else  @Mtype =3
Mr_Nice 2011-11-09
  • 打赏
  • 举报
 回复
[Quote=引用 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
[/Quote]

不用case ，不用dawugui的这个方式，还真想不到别的合理点儿的了。
dawugui 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
加载更多回复（4）
SQL Server 触发器 表的特定字段更新时，触发Update触发器
代码如下: create trigger TR_MasterTable_Update on MasterTable after update as if update ([Type])–当Type字段被更新时，才会触发此触发器 insert into MasterLogTable select Id ,(Case [Type] when 1 then ‘Type1’ when 2 then ‘Type2’ when 3 then ‘Type3’ when 4 then ‘Type4’ else ‘TypeDefault’ end) ,Name from inserted go 另外再补充一句
sql不常用函数总结以及事务，增加，删除触发器
sql不常用函数总结以及事务，增加，删除触发器 distinct 删除重复行 declare @x 申明一个变量 convert(varchar(20),tscore.tenglish) 类型转换 cast(xx as varchar(10)) 类型转换 www.jb51.net ====================================================== 情况 case when xxx then xx when xxxx then xxxxx else end =================================================
oracle存储过程进阶
介绍了oracle存储过程中常见的sql写法,游标(隐式,显式),触发器, CASE ... WHEN ... THEN ...ELSE ... END,IF... THEN ... ELSIF ...THEN...ELSE...END IF,记录类型变量定义和使用 ，%type定义变量，%rowtype定义变量，goto，异常。多且全
MySQL触发器运用于迁移和同步数据的实例教程
1.迁移数据 进行数据库移植，SQL Server=>MySQL。SQL Server上有如下的Trigger SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER TRIGGER [trg_risks] ON dbo.projectrisk FOR INSERT, UPDATE AS BEGIN UPDATE projectrisk SET classification = case when calc>= 9 then 3 when calc <9>=4 then 2 when calc <
SQL Case when 的使用方法
SQL Case when 的使用方法 Case具有两种格式：简单Case函数和Case搜索函数。 简单Case函数##### CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END Case搜索函数##### CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END 这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章