首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >mySQL如何合并语句

问mySQL如何合并语句
EN

Stack Overflow用户
提问于 2012-02-01 10:06:36
回答 1查看 301关注 0票数 0
  1. I有两个语句

一个。

代码语言:javascript
复制
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.

代码语言:javascript
复制
$STH_e = $DBH_R->query("                         
    SELECT *                                                  
    FROM $table_name_2                                                  
    WHERE date_time = $today_date  ");   
while (($row_e = $STH_e -> fetch_assoc()) !== null) {   } 

  1. 查询1和查询2的源列是不同的
  2. 一列是相似的-> date_time,但是时间(Date_time)是不同的,日期(Date_time)将是相同的
  3. ,我必须合并成一个列表(ORDER (Date_time) ASC),以生成代码脚本

的源代码。

问:如何合并这两条语句?或者这两个问题?

//-------------------------------------------------------------更新-创建表语句

用于查询1的

  1. 表-这种类型接近30个表

如果不存在创建表,则创建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的表

代码语言:javascript
复制
$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
EN

回答 1

Stack Overflow用户

发布于 2012-02-01 11:42:19

代码语言:javascript
复制
  $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部件将两个查询的结果连接到一个结果中。但是,两个表都必须以相同的顺序拥有相同数量的列,否则会引发不兼容类型或错误数量的列错误。

相反,定义列和顺序要好得多。

代码语言:javascript
复制
  $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;"

}

编辑:

应用上述相同的逻辑,您可以处理表中的额外属性,但不能处理如下所示的其他属性:

代码语言:javascript
复制
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:

代码语言:javascript
复制
$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) {   }  
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/9093980

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档