工会声明中的子句排序

Question

我有一些来自 4 个表的记录，日期不是所有表中的公共字段，但我使用"as"。现在我需要按日期订购

select id,convert(varchar(20), SoldDate,3) as Date from sale 
union
select id,convert(varchar(20), PaymentDate,3) as Date from purchase 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date from payments 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date from orders    
order by Date desc

我需要按日期订购

Answer 1

您可以使用

CTE或subquery：

SELECT t.*
FROM ( <Query>
     ) t
ORDER BY r.Date DESC;

但是，我会争论约会对话，如果您只想约会，请使用cast(SoldDate as date)并将其转换为ddMMyy。

因此，更新后的查询将是：

SELECT t.id, CONVERT(VARCHAR(10), t.[Date], 3) AS [Date]
FROM (SELECT id, CAST(SoldDate AS DATE) AS [Date]
      FROM sale 
      UNION
      SELECT id, CAST(PaymentDate AS DATE) 
      FROM purchase  
      UNION
      . . . 
     ) t
ORDER BY t.[Date] DESC;

Answer 2

你的代码出了什么问题？ 据我所知，您在那里按日期订购。 如果要按日期顺序排序，请创建另一列，即日期，作为日期类型，例如

select id,convert(varchar(20), SoldDate,3) as Date,SoldDate as d2 from sale 
union
select id,convert(varchar(20), PaymentDate,3) as Date, PaymentDate as d2  from purchase 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date, PaymentClearedDate as d2 from payments 
union 
select id,convert(varchar(20), PaymentClearedDate,3) as Date, PaymentClearedDate as d2 from orders    
order by d2  desc

如果日期以其他文本格式存储，则可能需要将日期转换为键入日期例如

select id,convert(varchar(20), SoldDate,3) as Date,CAST(SoldDate as datetime2) as d2 from sale 
union
...etc
order by d2  desc