预约视图与科室信息查询 SQL 语句示例

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW appointment_view AS select a.id AS id,a.user_id AS user_id,a.vaccinum_id AS vaccinum_id,a.doctor_id AS doctor_id,a.address AS address,a.app_date AS app_date,a.injected_time AS injected_time,a.status AS status,a.create_time AS create_time,u.name AS user_name,v.name AS vaccinum_name,d.name AS doctor_name from (((appointment a join user u on((u.id = a.user_id))) join vaccinum v on((v.id = a.vaccinum_id))) join doctor d on((d.id = a.doctor_id))) ;\n\nCREATE TABLE department (\n id int(11) NOT NULL AUTO_INCREMENT COMMENT '部门编号',\n name varchar(255) NOT NULL COMMENT '部门名称',\n remark varchar(555) DEFAULT NULL COMMENT '描述',\n status varchar(255) NOT NULL DEFAULT '1' COMMENT '0-禁止1-正常（可用情况）',\n PRIMARY KEY (id) USING BTREE\n) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='科室';\n\n写出从视图里查询出科室状态的sql语句内容：SELECT d.name, d.status\nFROM appointment_view av\nJOIN doctor d ON av.doctor_id = d.id\nJOIN department dp ON d.department_id = dp.id;