一个。
while (($row_1 = $STH_1 -> fetch_assoc()) !== null){
$table_name = $row_1['table_name'];
$stmts_1[] = sprintf("
SELECT *
FROM $table_name
WHERE date_time = $today_date ");
}
$stmt_1 = implode("\nUNION\n", $stmts_1);
$stmt_1 .= "\nORDER BY date_time ASC";
$STH_5_2 = $DBH_R->query($stmt_1);
while (($row_5_2 = $STH_5_2 -> fetch_assoc()) !== null) { } b.
$STH_e = $DBH_R->query("
SELECT *
FROM $table_name_2
WHERE date_time = $today_date ");
while (($row_e = $STH_e -> fetch_assoc()) !== null) { } 的源代码。
问:如何合并这两条语句?或者这两个问题?
//-------------------------------------------------------------更新-创建表语句
用于查询1的
如果不存在创建表,则创建v_c_ei_9001 (
id int(11)无符号非空AUTO_INCREMENT,
ei_code int(10)非空默认值'0',
date_time datetime NULL默认值“0000-00-00:00:00”,
index_year varchar(10)非空默认值'ndnc',
index_period varchar(10)非空默认值'nd',
index_period_2 varchar(10)非空默认值'nd',
index_unit varchar(10)非空默认值'nd',
index_comment文本不为空,
主键(id)、键date_time (date_time) ) ENGINE=MyISAM默认CHARSET=utf8 AUTO_INCREMENT=608;查询2的
如果不存在创建表,则创建v_c_e (
id int(11)无符号非空AUTO_INCREMENT,
country_code int(10)非空默认值'0',
country_name varchar(50)非空默认值'ndnc',
date_time datetime NULL默认值“0000-00-00:00:00”,
event_title varchar(100)非空默认值'ndnc',
event_released int(10)非空默认值'0',
event_comment varchar(500)非空默认值'ndnc',
主键(id) ENGINE=MyISAM默认CHARSET=utf8 AUTO_INCREMENT=168 ;
brgs
//---------------------------------------------------更新2
预查询代码//来自dbase中的所有表,我们只查询名称为v_c_ei_9xxx的表
$STH_1 = $DBH_R->query("SELECT table_name
FROM information_schema.tables
WHERE table_name
LIKE 'v_c_ei\_9%'
");
$stmts_1 = array();
while (($row_1 = $STH_1 -> ..... // as above发布于 2012-02-01 11:42:19
$stmts_1[] = sprintf("
SELECT * FROM (
SELECT *
FROM $table_name tn
WHERE date_time = $today_date
UNION ALL
SELECT *
FROM $table_name_2 tn2
WHERE date_time = $today_date
)tmp;"
}这应该能做您想做的事情--我还没有测试它,因为这取决于所传递的变量,并且您还没有提供SQL或代码。这是一个子选择的查询。UNION ALL部件将两个查询的结果连接到一个结果中。但是,两个表都必须以相同的顺序拥有相同数量的列,否则会引发不兼容类型或错误数量的列错误。
相反,定义列和顺序要好得多。
$stmts_1[] = sprintf("
SELECT * FROM (
SELECT col1, col2, col3, col4...... coln
FROM $table_name tn
WHERE date_time = $today_date
UNION ALL
SELECT col1, col2, col3, col4...... coln
FROM $table_name_2 tn2
WHERE date_time = $today_date
)tmp;"}
编辑:
应用上述相同的逻辑,您可以处理表中的额外属性,但不能处理如下所示的其他属性:
SELECT * FROM(
SELECT
id as id,
ei_code as ei_code,
date_time as dt,
'' as country_name,
index_year as iy,
index_period as ip1,
index_period_2 as ip2,
index_unit as iu,
index_comment as ic,
'' as et,
'' as er,
'' as ec
FROM `v_c_ei_9001` vce1
UNION ALL
SELECT
id as id,
country_code as country_code,
date_time as date_time,
country_name as country_name,
'' as iy,
'' as ip1,
'' as ip2,
'' as iu,
'' as ic,
event_title as et,
event_released as er,
event_comment as ec
FROM `v_c_e` as vce2
)tmp我根据您发布的create表做了几个假设,因为输入掩码非常相似。如果在上下文中这些都是错误的,只需创建空白字段,就像我为ip1、ip2等所做的那样。我已经用MySQL中的create测试了上面的内容,使用了一些示例数据,它很好。
编辑2:
$start2 = "SELECT
id as id,
country_code as country_code,
date_time as date_time,
country_name as country_name,
'' as iy,
'' as ip1,
'' as ip2,
'' as iu,
'' as ic,
event_title as et,
event_released as er,
event_comment as ec
FROM ";
$count = 0;
$start = "SELECT
id as id,
ei_code as ei_code,
date_time as date_time,
'' as country_name,
index_year as iy,
index_period as ip1,
index_period_2 as ip2,
index_unit as iu,
index_comment as ic,
'' as et,
'' as er,
'' as ec
FROM ";
//Loop through all table names using the variable $start concatenated with $table_name to create the SQL statement you require
while (($row_1 = $STH_1 -> fetch_assoc()) !== null){
$table_name = $row_1['table_name'];
$stmts_1[] = sprintf("
$start $table_name
WHERE date_time = $today_date ");
$count = $count + 1;
}
//This statements adds an extra String to the above stmt_1 - the $count variable controls where the extra string is placed, should be at the end of the array. Shouldn't matter though.
//NOTE this is your second query the part 'b' of your above question: variable $table_name_2
$stmt_1[count] sprintf("
$start2 $table_name_2
WHERE date_time = $today_date ");
//Implode all arrays into one long array adding 'Carriage Return (\n) UNION Carriage Return (\n)' between each statement.
$stmt_1 = implode("\nUNION\n", $stmts_1);
//Add another line at the end for the order by
$stmt_1 .= "\nORDER BY date_time ASC";
$STH_5_2 = $DBH_R->query($stmt_1);
while (($row_5_2 = $STH_5_2 -> fetch_assoc()) !== null) { } https://stackoverflow.com/questions/9093980
复制相似问题