如何在使用连接时使用order [英] How to use order by while using joins

问题描述

 选择 a.voucher_no，a.expensive_id，a.project_id， CONVERT （ nvarchar （ 10 ），a。 date ， 103 ） as   date ，a.paid_to，b.expensive_name，a.credit，a.debit，a.s_type，a.particulars，a.amount 
 来自 tbl_a a 内部 加入 tbl_c   c.paidTo = a.paid_to 
  inner   join  tbl_b b  on  a.expensive_id = b.expensive_id 其中 1 = 1 订单  by  a.voucher_no  ASC  

以上查询不在升序订单中，该订单基于 voucher_no 编号

解决方案

猜测 - 但我经常看到它并不好笑 - 它是有序的，但你的数据库设计有问题。

我认为你有voucher_no作为NVARCHAR或VARCHAR列，所以ORDER BY子句将使用字符串比较而不是数字比较。在字符串比较中，订购是根据第一个不同的字符完成的：

 1 
 10 
 100 
 11 
 12 
 ... 
 2 
 20 
 21 

如果你想要一个数字顺序，那么将数值存储在数字字段中，而不是字符串。

select a.voucher_no,a.expensive_id,a.project_id,CONVERT(nvarchar(10), a.date, 103) as date,a.paid_to,b.expensive_name,a.credit,a.debit,a.s_type,a.particulars,a.amount
from tbl_a a inner join tbl_c on c.paidTo=a.paid_to
inner join tbl_b b on a.expensive_id=b.expensive_id  where 1=1 order by a.voucher_no ASC

The above query not in Ascending Order which is based on voucher_no number

解决方案

At a guess - but I've seen this so often it's not funny - it is in order, but your DB design is faulty.
I think you have voucher_no as a NVARCHAR or VARCHAR column, so the ORDER BY clause will use a string comparison instead of a numeric one. And in a string comparison, the ordering is done based on the first different character:

1
10
100
11
12
...
2
20
21

If you want a numeric order, then store numeric values in a numeric field, not string.

这篇关于如何在使用连接时使用order的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！