insert into.. select 语句

Question

desc tb_oa_leave;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| levid            | int(20)      | NO   | PRI | NULL    | auto_increment |
| orgid            | char(20)     | YES  |     | NULL    |                |
| parentid         | char(20)     | NO   |     | NULL    |                |
| staffid          | char(20)     | NO   |     | NULL    |                |
| leavetype        | char(20)     | YES  |     | NULL    |                |
| apptime          | datetime     | YES  |     | NULL    |                |
| begindate        | date         | YES  |     | NULL    |                |
| enddate          | date         | YES  |     | NULL    |                |
| status           | char(1)      | YES  |     | NULL    |                |
| approvingofficer | char(20)     | YES  |     | NULL    |                |
| approvaltime     | datetime     | YES  |     | NULL    |                |
| comments         | char(200)    | YES  |     | NULL    |                |
| reason           | varchar(100) | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
这是我的表 现在我想在这个表里插入一条记录，parentid是从tb_oa_organization里根据orgid select出来的
其他的字段的值都有就是parentid要从tb_oa_organization查出来再插到tb_oa_leave 表里 
---------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| orgid    | char(20)     | NO   | PRI | NULL    |       |
| orgcode  | char(100)    | NO   |     | NULL    |       |
| parentid | char(20)     | NO   |     | NULL    |       |
| status   | char(1)      | YES  |     | NULL    |       |
| optrid   | varchar(20)  | YES  |     | NULL    |       |
| opttime  | datetime     | YES  |     | NULL    |       |
| re   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+这条语句应该怎么写呢？在线等

Answer 1

insert into tb_oa_leave (orgid,parentid,leavetype) select orgid,parentid,'年假' from tb_oa_organization where orgid='人事部';其余字段自己加上吧。

Answer 2

INSERT INTO tb_oa_leave
SELECT leave.levid,leave.orgid,org.parentid,leave.staffid......tb_oa_leave剩下字段
FRPM tb_oa_leave leave,tb_oa_organization org
WHERE leave.orgid=org.orgid;

Answer 3

leavetype字段并不在tb_oa_orgnazation里

Answer 4

levid orgid parentid.... tb_oa_leave剩下字段都是实际的值 肯定要有into values（）吧？

Answer 5

楼上的懂jsp吗 那些个字段都是通过request获取的 所以还想问一下 其他的字段改怎么写呢 ，你们这样的写法 其他的值都没插入进去啊

Answer 6

根本不知道你从JSP会得到哪些数据，你插入的数据是什么样？ 比如你的这个 leavetype 从哪来我们都不知道，只能提供给你一个思路。通过你的java生成这么一个字符串 sql = "insert into xxx (fd1,fd2,fd3) select ff1,ff9,'constant' from bbb where id=123";

Answer 7

#levid,orgid....都是从表单提交的
INSERT INTO tb_oa_leave
SELECT levid,orgid,org.parentid,staffid......
FROM tb_oa_leave leave,tb_oa_organization org
WHERE leave.orgid=orgid;

Answer 8

                        String orgid= req.getParameter("orgid");
//String parentid= req.getParameter("parentid");
String staffId= req.getParameter("staffid");
String leaveType= req.getParameter("leavetype");
String beginDate= req.getParameter("begindate");
String endDate= req.getParameter("enddate");
String reason=req.getParameter("reason");

Answer 9

INSERT INTO tb_oa_leave
SELECT levid,orgid,org.parentid,staffid......
FROM tb_oa_organization org
WHERE org.orgid=orgid;
#常量直接写到select后面