如何在sql server中使用union all的日期顺序？ [英] How to use order by date with union all in sql server?

问题描述

  SELECT  
 DR.name  as  tradername， 
  convert （ varchar ，DR.Receiveddate， 103 ） as  recdate，
 DR.Recamount，
 DR.Chequeno，
 DR.Remark，
 DR.Updatedby 
  FROM  K_HM_ChicksaleDueReport DR 
 其中 
 DR。接收日期   @ fromdate  和  @ todate  
 和 DR.name=@name 
 
  union   all  
 
 选择 
 CS.name  as  tradername，
  convert （ varchar ，CS.chicksplaceddate， 103 ） as  recdate，
 CS.Recamount，
 ' '   as  chequeno，
 ' 直接' 为备注，
 CS.Updatedby 
 来自 K_HM_ChickSales CS 
 其中 
 CS.Recamount> 0 
 和 CS。 chicksplaceddate    @ fromdate  和  @ todate  
 和 CS.name=@name 
 订单 按 recdate  desc  

我想按日期顺序显示报告降序...但它不正确输出

我的输出如下：

 22/05/2014 
 21/05/2014 
 10/04 / 2014 
 5/03/2014 

提前感谢

解决方案

不确定你想要什么，但试试这个：

  SELECT < FieldList> 
  FROM （
  SELECT  ... 
  FROM  ... 
  UNION  所有 
  SELECT  ... 
  FROM  ... 
） AS  T 
  ORDER   BY   CONVERT （ DATETIME ，recdate） DESC  

从
中选择*（
 SELECT 
 DR.name作为tradername，
 convert（datetime） ，DR.Receiveddate，103）as recdate，
 DR.Recamount，
 DR.Chequeno，
 DR.Remark，
 DR.Updatedby 
 FROM K_HM_ChicksaleDueReport DR 
其中
 DR.Receiveddate @fromdate和@todate 
和DR.name=@name 
 
 union all 
 
选择
 CS.name作为tradername，
 convert（datetime，CS.chicksplaceddate，103）作为recdate，
 CS.Recamount，
''作为chequeno，
'Direct'作为备注，
 CS.Updated来自
来自K_HM_ChickSales CS 
其中
 CS.Recamount> 0 
和CS.chicksplaceddate之间@fromdate和@todate 
和CS.name=@name 
）Table1 
按表1排序。[recdate] desc 

< blockquote>试试这个

 选择 tradername， convert （ varchar ，recdate， 103 ） as  recdate，Recamount，chequeno，
备注，由从更新（
  SELECT  name  as  tradername，Receiveddate  as  recdate，Recamount，Chequeno，
备注，由 FROM  K_HM_ChicksaleDueReport 
  where  Receiveddate    @ fromdate  和  @ todate  
 和名称喜欢 ' ％' + @ name + ' ％' 
  union   all  
 选择 name  as  tradername，chicksplaceddate  as  recdate，Recamount，' '  as  chequeno，' 直接'  as 备注，更新来自 K_HM_ChickSales 
 其中 Recamount> 0 和 chicksplaceddate    @ fromdate  和  @ todate  和名称喜欢 ' ％' + @ name + ' ％'）订单 按 a .recdate  desc  
 
  end  
 
  else  
 选择 tradername， convert （  varchar ，recdate， 103 ） as  recdate，Recamount，chequeno，remark，updatedby  from （
  SELECT  name  as  tradername，Receiveddate  as  recdate，Recamount， Chequeno，
备注，更新来自
  FROM  K_HM_ChicksaleDueReport 其中 Receiveddate 介于  @ fromdate  
 和  @ todate  和 name = @ name 
  union  < span class =code-keyword> all  
 选择名称为 tradername ，chicksplaceddate  as  recdate，Recamount，' '  as  chequeno，
 '  Direct'  as 备注，更新来自 K_HM_ChickSales 
 其中重新计算> 0 和 chicksplaceddate    @ fromdate  和  @ todate  
 和 name = @ name）a  order   by  a.recdate 递减 

SELECT
  DR.name as tradername,
  convert(varchar,DR.Receiveddate,103) as recdate,
  DR.Recamount,
  DR.Chequeno,
  DR.Remark,
  DR.Updatedby 
FROM K_HM_ChicksaleDueReport DR 
where 
  DR.Receiveddate between @fromdate and @todate 
  and DR.name=@name

union all

Select 
  CS.name as tradername,
  convert(varchar,CS.chicksplaceddate,103) as recdate,
  CS.Recamount,
  '' as chequeno,
  'Direct' as Remark,
  CS.Updatedby 
from K_HM_ChickSales CS
where 
  CS.Recamount>0 
  and  CS.chicksplaceddate between @fromdate and @todate 
  and CS.name=@name 
order by recdate desc

I want to display report in Order by Date decending order..but it's not come correct output
my output like:

22/05/2014
21/05/2014
10/04/2014
 5/03/2014

thanks in advance

解决方案

Not sure what you want, but try this:

SELECT <FieldList>
FROM (
    SELECT ...
    FROM ...
    UNION ALL
    SELECT ...
    FROM ...
) AS T
ORDER BY CONVERT(DATETIME, recdate) DESC

select * from
(
SELECT
  DR.name as tradername,
  convert(datetime,DR.Receiveddate,103) as recdate,
  DR.Recamount,
  DR.Chequeno,
  DR.Remark,
  DR.Updatedby 
FROM K_HM_ChicksaleDueReport DR 
where 
  DR.Receiveddate between @fromdate and @todate 
  and DR.name=@name
 
union all
 
Select 
  CS.name as tradername,
  convert(datetime,CS.chicksplaceddate,103) as recdate,
  CS.Recamount,
  '' as chequeno,
  'Direct' as Remark,
  CS.Updatedby 
from K_HM_ChickSales CS
where 
  CS.Recamount>0 
  and  CS.chicksplaceddate between @fromdate and @todate 
  and CS.name=@name 
)Table1
order by Table1.[recdate] desc

Try this

select tradername,convert(varchar,recdate,103)as recdate,Recamount,chequeno,
remark,updatedby from (
SELECT  name as tradername,Receiveddate as recdate,Recamount,Chequeno,
Remark,Updatedby FROM K_HM_ChicksaleDueReport
where Receiveddate between @fromdate and @todate
and name like '%'+@name+'%'
union all
Select name as tradername,chicksplaceddate as recdate,Recamount,''as chequeno,'Direct' as Remark,Updatedby from K_HM_ChickSales
where Recamount>0 and  chicksplaceddate between @fromdate and @todate and name like '%'+@name+'%')a   order by a.recdate  desc

end

else
select tradername,convert(varchar,recdate,103)as recdate,Recamount,chequeno,remark,updatedby from(
SELECT name as tradername,Receiveddate as recdate,Recamount, Chequeno,
Remark,Updatedby
FROM K_HM_ChicksaleDueReport  where Receiveddate between @fromdate
and @todate and name=@name
union all
Select name as tradername,chicksplaceddate as recdate,Recamount,''as chequeno,
'Direct' as Remark,Updatedby from K_HM_ChickSales
where Recamount>0 and  chicksplaceddate between @fromdate and @todate
and name=@name)a order by a.recdate  desc

这篇关于如何在sql server中使用union all的日期顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！