数据库多表创建视图实例，修改视图的数据类型

数据库多表创建视图，是我们经常要用到的，下面简单分享一个实例，需要注意的地方是**括号里面的字段**要用反引号包裹，其他地方均不需要反引号包裹。

推荐写法

```
CREATE VIEW `view_data_out_service` AS
select
    `sm`.`id` AS `sm_id`,
    `sm`.`job_type` AS `sm_job_type`,
    `sm`.`start_settlement_date` AS `sm_start_settlement_date`,
    cast(`sm`.`create_time` as char(20) charset utf8mb4) AS `sm_create_time`,
    cast(`sm`.`update_time` as char(20) charset utf8mb4) AS `sm_update_time`,
    `sm`.`service_dep` AS `sm_service_dep`,
    `sm`.`pergana` AS `sm_pergana`,
    `sm`.`zone` AS `sm_zone`,
    `sm`.`dealer_code` AS `sm_dealer_code`,
    `sm`.`dealer_name` AS `sm_dealer_name`,
    `sm`.`job_num` AS `sm_job_num`,
    `sm`.`sname` AS `sm_sname`,
    `sm`.`sphone` AS `sm_sphone`,
    `sm`.`vin` AS `sm_vin`,
    `sm`.`type` AS `sm_type`,
    `qm`.`sms_push` AS `qm_sms_push`,
    `qm`.`sms_push_time` AS `qm_sms_push_time`,
    `qm`.`wechat_push` AS `qm_wechat_push`,
    `qm`.`wechat_push_time` AS `qm_wechat_push_time`,
    `qm`.`sessionid` AS `qm_sessionid`,
    `qm`.`anonymous` AS `qm_anonymous`,
    `qm`.`reply_time` AS `qm_reply_time`
from
    (`csi`.`csi_survey_members` `sm` join `csi`.`csi_questionnaire_member` `qm`)
where
    (`sm`.`job_num` = `qm`.`job_num`)
group by
    `sm`.`id`
```

```
CREATE VIEW view_test (`sm.id`,`sm.service_dep`,`sm.pergana`,`sm.zone`,`sm.dealer_code`,`sm.dealer_name`,`sm.job_num`,`sm.sname`,`sm.sphone`,`sm.vin`,`sm.type`,`qm.sms_push`,`qm.sms_push_time`,`qm.wechat_push`,`qm.wechat_push_time`,`qm.sessionid`,`qm.anonymous`,`qm.reply_time`)
AS SELECT sm.id,sm.service_dep,sm.pergana,sm.zone,sm.dealer_code,sm.dealer_name,sm.job_num,sm.sname,sm.sphone,sm.vin,sm.type,qm.sms_push,qm.sms_push_time,qm.wechat_push,qm.wechat_push_time,qm.sessionid,qm.anonymous,qm.reply_time
FROM csi_survey_members sm,csi_questionnaire_member qm
where sm.job_num = qm.job_num
GROUP BY  sm.id;
```

上述讲的是创建视图，字段会按照数据库原字段分配，如果我们需要修改视图的字段类型可参考下述 cast 语句

```
CREATE VIEW view_data_out_service (`sm_id`,`sm_job_type`,`sm_start_settlement_date`,`sm_create_time`,`sm_update_time`,`sm_service_dep`,`sm_pergana`,`sm_zone`,`sm_dealer_code`,`sm_dealer_name`,`sm_job_num`,`sm_sname`,`sm_sphone`,`sm_vin`,`sm_type`,`qm_sms_push`,`qm_sms_push_time`,`qm_wechat_push`,`qm_wechat_push_time`,`qm_sessionid`,`qm_anonymous`,`qm_reply_time`)
AS SELECT 
sm.id,
sm.job_type,
sm.start_settlement_date,
CAST(sm.create_time AS CHAR(20)),
CAST(sm.update_time AS CHAR(20)),
sm.service_dep,
sm.pergana,
sm.zone,
sm.dealer_code,
sm.dealer_name,
sm.job_num,
sm.sname,
sm.sphone,
sm.vin,
sm.type,
qm.sms_push,
qm.sms_push_time,
qm.wechat_push,
qm.wechat_push_time,
qm.sessionid,
qm.anonymous,
qm.reply_time
FROM csi_survey_members sm,csi_questionnaire_member qm
where sm.job_num = qm.job_num
GROUP BY  sm.id;
```
[![](https://micuu.com/data/avatar/20211011/ff9d8dfa590a8daa0c4bf3e8f24014bc.png)](https://micuu.com/data/avatar/20211011/ff9d8dfa590a8daa0c4bf3e8f24014bc.png)

修改数据类型的话建议重新创建视图。

注:原创不易,转载请注明出处( https://micuu.com/new/2205.html )，本站所有资源来源于网络收集，如有侵权请联系QQ245557979进行清除。

数据库多表创建视图实例，修改视图的数据类型

留言反馈