如何通过联合查询

问题描述：

我需要的UNION.I内使用顺序内把为了现在用的是下面的查询如何通过联合查询

SELECT blp_res_id as id, 
    blp_res_file_name as name, 
    blp_res_publisher as pname, 
    blp_res_in_date as startdate, 
    blp_res_modified_date as modifieddate, 
    TIMEDIFF(NOW(),blp_res_in_date) as timetaken 
FROM blp_result_info 
WHERE blp_res_proc_id = '16' and 
    DATE(blp_res_in_date) = DATE('2017-04-25') and 
    blp_res_lock_status = '0' 
UNION 
SELECT blp_res_id as id, 
    blp_res_file_name as name, 
    blp_res_publisher as pname, 
    blp_res_in_date as startdate, 
    blp_res_modified_date as modifieddate, 
    TIMEDIFF(NOW(),blp_res_in_date) as timetaken 
FROM blp_result_info 
WHERE blp_res_proc_id = '16' and 
    DATE(blp_res_in_date) = DATE('2017-04-25') and 
    blp_res_lock_status = '1' 
UNION 
SELECT blp_res_id as id, 
    blp_res_file_name as name, 
    blp_res_publisher as pname, 
    blp_res_in_date as startdate, 
    blp_res_modified_date as modifieddate, 
    TIMEDIFF(NOW(),blp_res_in_date) as timetaken 
FROM blp_result_info 
WHERE blp_res_proc_id = (
    select blp_proc_fix_id 
    from blp_process_info 
    where blp_proc_id =16 
    ) and 
    DATE(blp_res_in_date) = DATE('2017-04-25')

任何一个可以帮我请。

答

使用别名只在firts选择和最后选择，例如，添加顺序为：（按名称排序）

select 
    blp_res_id as id 
    ,blp_res_file_name as name 
    ,blp_res_publisher as pname 
    ,blp_res_in_date as startdate 
    ,blp_res_modified_date as modifieddate 
    ,TIMEDIFF(NOW(),blp_res_in_date) as timetaken 
FROM blp_result_info 
where blp_res_proc_id = '16' 
and DATE(blp_res_in_date) = DATE('2017-04-25') 
and blp_res_lock_status = '0' 

UNION 
select 
    blp_res_id 
    ,blp_res_file_name 
    ,blp_res_publisher 
    ,blp_res_in_date 
    ,blp_res_modified_date 
    ,TIMEDIFF(NOW(),blp_res_in_date) 
FROM blp_result_info 
where blp_res_proc_id = '16' 
and DATE(blp_res_in_date) = DATE('2017-04-25') 
and blp_res_lock_status = '1' 

UNION 
select 
    blp_res_id 
    ,blp_res_file_name 
    ,blp_res_publisher 
    ,blp_res_in_date 
    ,blp_res_modified_date 
    ,TIMEDIFF(NOW(),blp_res_in_date) 
FROM blp_result_info 
where blp_res_proc_id = (
     select blp_proc_fix_id 
     from blp_process_info 
     where blp_proc_id =16) 
and DATE(blp_res_in_date) = DATE('2017-04-25') 

ORDER BY name

Thanq这么多@scaisEdge – user123

@ user123以及如果我的回答（或另一个）是正确的，请标记这些答案之一是接受和/或有用......怎么看这里 http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – scaisEdge

答

一种方式做到这一点：

SELECT * 
FROM (select blp_res_id as id,blp_res_file_name....) 
ORDER BY some_column

Thanq这么多@zipa – user123

答

试试这个。您可以在不使用UNION的情况下编写查询，因为您在所有3个子查询中查询同一个表。

SELECT blp_res_id as id, 
    blp_res_file_name as name, 
    blp_res_publisher as pname, 
    blp_res_in_date as startdate, 
    blp_res_modified_date as modifieddate, 
    TIMEDIFF(NOW(),blp_res_in_date) as timetaken 
FROM blp_result_info 
WHERE 
(
    blp_res_proc_id = '16' and 
    DATE(blp_res_in_date) = DATE('2017-04-25') and 
    blp_res_lock_status = '0' 
) 
OR 
(
    blp_res_proc_id = '16' and 
    DATE(blp_res_in_date) = DATE('2017-04-25') and 
    blp_res_lock_status = '1' 
) 
OR 
(
    blp_res_proc_id = (
    select blp_proc_fix_id 
    from blp_process_info 
    where blp_proc_id =16 
    ) and 
    DATE(blp_res_in_date) = DATE('2017-04-25') 
) 
ORDER BY <YOUR_COLUMN_NAME>

Thanq so much much @shoaib – user123

如何通过联合查询

相关推荐