创建视图

不同结构的表也可以用union来联合查询，需要指定为相同的字段；写视图语句时不能传参数！

CREATE  VIEW `message_list_view` AS 
select `p_gamepokey_account`.`userid` AS `userid`,`p_gamepokey_account`.`id` AS `id`,'0' AS `type`,'成就' AS `content`,'系统' AS `msg_sender`,`p_gamepokey_account`.`expdate` AS `sendtime`,`p_gamepokey_account`.`prop_id` AS `prop_id`,`p_gamepokey_account`.`prop_count` AS `prop_value`,'' AS `to_prop_id`,'' AS `to_prop_value`,'' AS `match_id` from `p_gamepokey_account` where (`p_gamepokey_account`.expdate >= DATE_SUB(SYSDATE(),INTERVAL 30 DAY) and `p_gamepokey_account`.type = '1' ) 
union 
select `p_match_ranking_log`.`userid` AS `userid`,`p_match_ranking_log`.`id` AS `id`,'1' AS `type`,`p_match_ranking_log`.`match_name` AS `content`,`p_match_ranking_log`.`ranking` AS `msg_sender`,`p_match_ranking_log`.`time` AS `sendtime`,`p_match_ranking_log`.`prop_id` AS `prop_id`,`p_match_ranking_log`.`prop_value` AS `prop_value`,'' AS `to_prop_id`,'' AS `to_prop_value`,'' AS `match_id` from `p_match_ranking_log` where `p_match_ranking_log`.prop_value > 0 and `p_match_ranking_log`.award is not null
union 
select `t`.`userid` AS `userid`,`t`.`id` AS `id`,'2' AS `type`,`t1`.`prop_name` AS `content`,'系统' AS `msg_sender`,`t`.`exchange_time` AS `sendtime`,`t`.`to_propid` AS `prop_id`,1 AS `prop_value`,'' AS `to_prop_id`,'' AS `to_prop_value`,`t`.`match_id` AS `match_id` from (`p_exchange_prop_log` `t` join `p_prop_config` `t1`) where ((`t`.`to_propid` = `t1`.`prop_id`) and (`t`.`type` = '3')) 
union 
select `t`.`userid` AS `userid`,`t`.`id` AS `id`,'3' AS `type`,`t1`.`prop_name` AS `content`,'系统' AS `msg_sender`,`t`.`exchange_time` AS `sendtime`,`t`.`from_propid` AS `prop_id`,'1' AS `prop_value`,'104999' AS `to_prop_id`,`t1`.`change_gold` AS `to_prop_value`,`t`.`match_id` AS `match_id` from (`p_exchange_prop_log` `t` join `p_prop_config` `t1`) where ((`t`.`from_propid` = `t1`.`prop_id`) and (`t`.`type` = '2'))

查询的时候，写：
select * from message_list_view where userid = '374' ORDER BY sendtime desc limit 0,20;