mysql> desc goods//
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| useid | char(10) | YES | | NULL | |
| goodsname | char(15) | YES | | NULL | |
| price | int(20) | YES | | NULL | |
| number | int(10) | YES | | NULL | |
| cost | int(20) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)mysql> desc user//
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| useid | char(10) | YES | | NULL | |
| sum | int(20) | YES | | NULL | |
| integral | int(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
以上一两张表的结构,要实现的功能是当goods表中插入数据时如果user(这个表中已经插入数据)表中有useid相同的就把(goods.price-goods.cost)*goods.number*015的结果插入到user.integral中,并且当user.integral的值大于1000的时候,就减掉1000让user.sum的值增加100.
我触发器实现了前半部代码如下:
create trigger ss
before insert on goods
for each row begin
update user set integral=((new.price-new.cost)*new.number*0.15+user.integral) where user.useid=new.useid;
end;
//
剩下的使了n种方法都实现不了,望高手指点!!
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| useid | char(10) | YES | | NULL | |
| goodsname | char(15) | YES | | NULL | |
| price | int(20) | YES | | NULL | |
| number | int(10) | YES | | NULL | |
| cost | int(20) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)mysql> desc user//
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| useid | char(10) | YES | | NULL | |
| sum | int(20) | YES | | NULL | |
| integral | int(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
以上一两张表的结构,要实现的功能是当goods表中插入数据时如果user(这个表中已经插入数据)表中有useid相同的就把(goods.price-goods.cost)*goods.number*015的结果插入到user.integral中,并且当user.integral的值大于1000的时候,就减掉1000让user.sum的值增加100.
我触发器实现了前半部代码如下:
create trigger ss
before insert on goods
for each row begin
update user set integral=((new.price-new.cost)*new.number*0.15+user.integral) where user.useid=new.useid;
end;
//
剩下的使了n种方法都实现不了,望高手指点!!
解决方案 »
- 这段代码运行不行呢
- 我使用了left和right这样的字段,写入数据库错误了。
- 急急急,帮忙看看这个简单的子查询怎么就错了
- 跪求答案,在线等,实在着急的很
- 两个Mysql数据库之间的操作问题
- MySQL(timestamp)问题~同样的一个简单语句在Unix可以正确执行,在windows上却不行~
- mysql 中的blob操作(读写)
- windows下安装mysql时总是显示mysql for visual studio1.0.2下载失败,我应该怎么办?
- mysql中,同一数据库中,不同的数据表可采用不同的存储引擎吗?
- windows下的mysql innodb写入很慢是这么回事
- SQL查询
- 大家看下一个关于索引的诡异问题
IF(integral>=1000,integral-1000,
((new.price-new.cost)*new.number*0.15+user.integral)) ,
SUM=IF(integral>=1000,SUM+100,SUM)
where user.useid=new.useid;
Query OK, 1 row affected (0.00 sec)mysql> select * from user//
+-------+------+----------+
| useid | sum | integral |
+-------+------+----------+
| as | 100 | 45885 |
+-------+------+----------+
1 row in set (0.00 sec)
应该出现这样的结果:
+-------+------+----------+
| useid | sum | integral |
+-------+------+----------+
| as | 4600| 885 |
+-------+------+----------+
这样就完美了!!
呵呵。
new.price-new.cost)*new.number*0.15+user.integral?
(1510-180)*230*.15=45885sum、integral值是多少+-------+------+----------+
| useid | sum | integral |
+-------+------+----------+
| as | 4600| 885 |
+-------+------+----------+
integral:(1510-180)*230*.15=45885-45000
sum:45000/10+100确实没有明白你的计算方法
+-------+------+----------+
| useid | sum | integral |
+-------+------+----------+
| as | 4500| 885 |
+-------+------+----------+
刚刚计算错了,
再次谢谢你~
45885 885?
如是:
select right(cast(45885 as char),3),
(45885-(0+right(cast(45885 as char),3)))/10在你的TRIGGER中,将45885修改成你的
(new.price-new.cost)*new.number*0.15公式
((new.price-new.cost)*new.number*0.15+user.integral)) ,
SUM=IF(integral>=1000,SUM+100,SUM)
仔细看过过这个代码感觉也不合理:当integral>=1000是也要加上这个的((new.price-new.cost)*new.number*0.15+user.integral))。只不过加了之后只要是大于1000就让这个字段减去1000然后再sum字段是加上100。但是IF(integral>=1000,integral-1000,
((new.price-new.cost)*new.number*0.15+user.integral))写只要是大于1000就不会加了,只是减去1000。上面的我测试一下。看是否出的结果和想要的结果一样。
before insert on goods
for each row begin
update user set integral=(new.price-new.cost)*new.number*0.15+user.integral,sum=if(integral>=1000,sum+100,sum),integral=if(integral>=1000,integral-1000,integral)
where user.useid=new.useid;
end;
//暂时这样解决的,但是还是不能一次插入的sum的值大于2000.郁闷中。
想到更好的办法再给大家看看吧,再次谢谢楼上的!!