在sql server 2005中按日期与oder进行联合查询 [英] Union query with oder by date in sql server 2005

问题描述

大家好，



以下是我的查询，按日期顺序返回前5行

Hi all,

Below is my query, which returns top 5 rows order by date

select top 5 sd.sl_sub as topic,em.name as CreatedBy,sd.approved_date,'topic'as type  ,'' as comment_desc ,sd.sl_type
from wockhardt_022.sl_details sd
inner join dbo.employee_mapping map on map.user_id=sd.created_by  and effective_to_date is null
inner join  employee_master em   on map.employee_id=em.employee_id
where sd.div_code=@div_code
and sd.approved_flag=1
and sd.created_date < @last_date
--and approved_date > getdate() - 10

union

select top 5 sd.sl_sub as topic,em.name as CreatedBy,sc.approve_date as approved_date,'comment' as type ,sc.comment_desc,sd.sl_type
from wockhardt_022.sl_comment sc
inner join sl_details sd on sd.sl_id=sc.sl_id
inner join dbo.employee_mapping map on map.user_id=sc.comment_by and effective_to_date is null
inner join dbo.employee_master em on em.employee_id=map.employee_id
where sd.div_code=@div_code
and sc.approve_flag=1
and sc.approve_date < @last_date
order by approved_date  desc

但是上面的查询并没有按日期给出输出顺序。

这个查询有什么问题？

But above query doesnt give output order by date.
What is wrong in this query?

推荐答案

让'' s调用对一组函数进行排序： O（S）。让您的两个查询结果为 A 和 B 。现在，你的联合声明是 C = A U O（B），而且不会被命令。你需要的是 C = O（AUB）：第一个联合而不是排序。

Let''s call ordering a function over a set: O(S). Let your two query results be A and B. Now, your union statement is C=A U O(B), and that won''t be ordered. What you need is C=O(A U B): first union than sort.

SELECT * FROM
(
SELECT ... --first query
UNION
SELECT ... --second query
)
ORDER BY whatever

您好，



解决方案非常简单。在第一个select语句中，您不是根据日期排序，而是在按日期排序的第二个查询中，但在联合查询时，您找不到有序行。我建议你为整个结果集下订单声明而不是订购个别查询。



尝试如下。

Hi,

The solution is very simple. In your first select statement, you are not ordering the based on the date, but in the Second query you are ordering by date, but when union the queries, you could not find the ordered rows. I suggest you to put order statement for the overall result set rather ordering individual queries.

try like below.

select A.topic, A.CreatedBy, A.approved_date, A.type, A.comment_desc, A.sl_type
from
(
select top 5 sd.sl_sub as topic,em.name as CreatedBy,sd.approved_date,'topic'as type  ,'' as comment_desc ,sd.sl_type
from wockhardt_022.sl_details sd
inner join dbo.employee_mapping map on map.user_id=sd.created_by  and effective_to_date is null
inner join  employee_master em   on map.employee_id=em.employee_id
where sd.div_code=@div_code
and sd.approved_flag=1
and sd.created_date < @last_date
--and approved_date > getdate() - 10

union
 
select top 5 sd.sl_sub as topic,em.name as CreatedBy,sc.approve_date as approved_date,'comment' as type ,sc.comment_desc,sd.sl_type
from wockhardt_022.sl_comment sc
inner join sl_details sd on sd.sl_id=sc.sl_id
inner join dbo.employee_mapping map on map.user_id=sc.comment_by and effective_to_date is null
inner join dbo.employee_master em on em.employee_id=map.employee_id
where sd.div_code=@div_code
and sc.approve_flag=1
and sc.approve_date < @last_date
) as A
order by A.approved_date desc   -- ordering by date to the resultant record set

希望它有所帮助。

hope it helps.

请检查columns approved_date列的数据类型。

它应该是datetime类型而不是varchar。

please check the data type of column approved_date.
It should be datetime type and not varchar.

这篇关于在sql server 2005中按日期与oder进行联合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！