如何在sql server中使用union all的日期顺序? [英] How to use order by date with union all in sql server?
本文介绍了如何在sql server中使用union all的日期顺序?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文