如何通过联合查询
问题描述:
我需要的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
答
一种方式做到这一点:
SELECT *
FROM (select blp_res_id as id,blp_res_file_name....)
ORDER BY some_column
+0
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>
+0
Thanq so much much @shoaib – user123
Thanq这么多@scaisEdge – user123
@ user123以及如果我的回答(或另一个)是正确的,请标记这些答案之一是接受和/或有用......怎么看这里 http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – scaisEdge