表A 里面有两个字段:ID,UserID现在要求写一个触发器,在插入记录的时候进行判断,如果插入数据UserID的值存在并且大于5条的时候返回,不执行插入操作比如将UserID是0002的记录插入表中,这时表中UserID等于0002的记录已经大于5条了,那么这次操作就不会被执行!这个触发器该怎么写呢?【写出触发器,并且是最优的给分60】表B里面有三个字段:ID,AddTime,clickAddTime类型为DATETIME ,click为BIGINT求一SQL语句,要求按月分组求click的和,如5月份的点击之和,6月的点击之和等等!【写出SQL语句,给分20】
select count(*) into @ee from tt where UserID=new.UserID
if @ee>=5 then
可以设置1个报错的语句,比如主键重复
endif
http://blog.chinaunix.net/u3/116107/showart.php?id=2326234这里详细说明了怎么样用触发器拦截数据操作。2:
select month(AddTime),sum(click) from tt group by month(AddTime)
mysql> select '1'+'2';
+---------+
| '1'+'2' |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)mysql>
一样的运行,会隐式转换的。
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
http://blog.csdn.net/ACMAIN_CHM/archive/2009/07/25/4380183.aspx
select month(AddTime),sum(click)
from 表B
group by month(AddTime)
create trigger update_exceed BEFORE INSERT on A
for each row
begin
select count(*) into @ee from A where UserID=new.UserID;
if @ee>=5 then
insert into A(id) values(0);
end if;
end 触发器书写成功,谢谢WWWWA和zuoxingyu的帮助,我在此将代码贴出和大家分享,如果代码有什么不妥处也请大家指正!
select month(AddTime) as month,sum(cast(click as int)) as sum from tt where datepart(yy,AddTime)=2010 group by month(AddTime)select top 20 year(AddTime) as year,sum(cast(click as int)) as sum from tt group by year(AddTime)