如何在MySQL中合并表 [英] How to merge table in MySQL
本文介绍了如何在MySQL中合并表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我只想将这些表合并/组合成一个查询,但它不起作用.实际上有太多的表和列,但对于这个例子,我只是确定了.
I just want to merge/combine this tables into one query, but it not working. Actually there is too many tables and column but for this example I just put certain only.
$insertSQL_sr1 = "INSERT INTO sr1_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr1`";
$insertSQL_sr2 = "INSERT INTO sr2_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr2`";
$insertSQL_sr3 = "INSERT INTO sr3_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr3`";
$insertSQL_full = $insertSQL_sr1.";".$insertSQL_sr2.";".$insertSQL_sr3;
mysql_query($insertSQL_full);
mysql_select_db($database_pods, $pods);
$Result1 = mysql_query($insertSQL_full, $pods) or die(mysql_error());
出现错误:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO sr2_full (`date`, `total_pending`, `appt_today`, `percent_appt_today' at line 1
我的第二次
$insertSQL = "INSERT INTO sr1_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr1`; INSERT INTO sr2_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr2`; INSERT INTO sr3_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr3`";
mysql_select_db($database_pods, $pods);
$Result1 = mysql_query($insertSQL, $pods) or die(mysql_error());
出现错误:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO sr2_full (`date`, `total_pending`, `appt_today`, `percent_appt_today' at line 1
推荐答案
$insertSQL1 = "INSERT INTO sr1_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr1`";
$insertSQL2 = "INSERT INTO sr2_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr2`";
$insertSQL3 = "INSERT INTO sr3_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr3`";
添加;
来组合两条SQL语句.
Add ;
to combine two SQL statements.
您可以像这样组合这些查询.
You can combine these queries like this.
$insertSQL_Single = $insertSQL1.";".$insertSQL2.";".$insertSQL3;
结果查询:
INSERT INTO sr1_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr1`;INSERT INTO sr2_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr2`;INSERT INTO sr3_full (`date`, `total_pending`, `appt_today`, `percent_appt_today`) SELECT `date`, `total_pending`, `appt_today`, `percent_appt_today` FROM `sr3`
我已经用 PhpMyAdmin 试过了.查询成功
I've tried this using PhpMyAdmin. query was success
它不能被 mysql_query() 执行.让我们找到一些其他的技巧.我们需要单独执行它.
it can't be executed by mysql_query(). lets find some other tricks. we need to execute it separate.
使用mysqli来执行这个
Use mysqli for executing this
$mysqli = new mysqli("host","username","password", "databasename");
if (!$mysqli->multi_query($insertSQL_Single)) {
echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
else
{
echo "success";
}
这篇关于如何在MySQL中合并表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文