mysql之学习秘籍(七)
t id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 25 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.00 sec)
7: 子查询:
7.1:查询出最新一行商品(以商品编号最大为最新,用子查询实现)
select goods_id,goods_name from
ecs_goods where goods_id =(select max(goods_id) from ecs_goods);
7.2:查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)
7.3:用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);
7.4:用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;
创建触发器:
CREATE trigger tg2
after insert on ord
for each row
update goods set goods_number=goods_number-new.num where id=new.gid
CREATE trigger tg3
after delete on ord
for each row
update goods set goods_number=good_number+old.num where id=old.gid
CREATE trigger tg4
after update on ord
for each row
update goods set goods_number=goods_number+old.num-new.num where id=old.gid