数字未按顺序排序

本文关键字：排序 顺序 数字 | 更新日期: 2023-09-27 18:35:47

我试图使用 asp.net 和 C# 在 sql 中按顺序（1 到 ...）对列进行排序。 在进行了一些研究之后，似乎我需要将 SQL Server 中的列类型更改为 int，但这是不可能的，因为该列存储的是门牌号，我最终可能会得到 10a 的门牌号（例如），所以目前设置为 varchar。 因此，它无法正确排序列。 我尝试将相关列转换为 int，但是在运行应用程序时，我在 gridview 中收到其绑定错误，我也尝试了其他作为 * 1，但在运行时仍然在绑定部分出现错误

SQL 语句

    SELECT DISTINCT tblcontact.ContactID, tblcontact.Forename, tblcontact.Surname, 
    tbladdress.[House Number], tbladdress.AddressLine1, tbladdress.AddressLine2, 
    tblcontact.[Business Name] FROM tblcontact INNER JOIN tbladdress ON tblcontact.AddressID = tbladdress.AddressID 
    LEFT OUTER JOIN tblDonate 
    ON tblcontact.ContactID = tblDonate.ContactID 
    WHERE (tbladdress.CollectionArea = @CollectionArea) AND 
(tbladdress.AddressLine1 = @drpCollectionStreet) 
    ORDER BY tbladdress.[House Number] ASC

网格视图标记

                                    <asp:TemplateField HeaderText="House Number">
                                        <EditItemTemplate>
                                            <asp:TextBox ID="txtHouseNum" runat="server" Text='<%# Bind("[House Number]") %>'></asp:TextBox>
                                        </EditItemTemplate>
                                        <ItemTemplate>
                                            <asp:Label ID="lblHouseNum" runat="server" Text='<%# Bind("[House Number]") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>

下面是其排序方式的示例

21
22
27
28
5
6
8
9

这就是我转换为 int 的方式，它在 SQL 中排序良好，但我在绑定上出现错误（上图）

SELECT DISTINCT tblcontact.ContactID, tblcontact.Forename, tblcontact.Surname, cast(tbladdress.[House Number] as int), 
tbladdress.AddressLine1, tbladdress.AddressLine2, tblcontact.[Business Name] 
FROM tblcontact INNER JOIN tbladdress ON tblcontact.AddressID = tbladdress.AddressID 
LEFT OUTER JOIN tblDonate ON tblcontact.ContactID = tblDonate.ContactID 
WHERE (tbladdress.CollectionArea = 'Queens Park') AND (tbladdress.AddressLine1 = 'Kings Road') 
ORDER BY cast(tbladdress.[House Number] as int)

运行时出错

这是

运行时的错误（这是最有意义的）

 "House Number is neither a DataColumn nor a DataRelation for table DefaultView."}

数字未按顺序排序

要按数字对街道编号进行排序，您需要将它们转换为数字。当然，麻烦的是像"10a"这样的字符串不能被转换为INT。答案是从[门牌号]列中提取数字字符，然后进行转换。下面对 ORDER BY 子句执行此操作：

... 
ORDER BY CAST(SUBSTRING(tbladdress.[House Number], PATINDEX('%[0-9]%', tbladdress.[House Number]),
                 1 + PATINDEX('%[0-9][^0-9]%', tbladdress.[House Number] + ' ') -
                 PATINDEX('%[0-9]%',tbladdress.[House Number])) AS INT)

有关复杂公式如何工作的解释，请参阅 http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/。

从选择column list中删除cast。只保留order by静止，它将对结果进行排序。

select A.ContactID,
                A.Forename,
                A.Surname,
                A.[House Number],
                A.AddressLine1,
                A.AddressLine2,
                A.[Business Name] from (
SELECT DISTINCT tblcontact.ContactID,
                tblcontact.Forename,
                tblcontact.Surname,
                tbladdress.[House Number],
                tbladdress.AddressLine1,
                tbladdress.AddressLine2,
                tblcontact.[Business Name]
FROM   tblcontact
       INNER JOIN tbladdress
               ON tblcontact.AddressID = tbladdress.AddressID
       LEFT OUTER JOIN tblDonate
                    ON tblcontact.ContactID = tblDonate.ContactID
WHERE  ( tbladdress.CollectionArea = 'Queens Park' )
       AND ( tbladdress.AddressLine1 = 'Kings Road' )) A
ORDER  BY Cast(tbladdress.[House Number] AS INT)

正确答案是JohnS和NoDisplayName答案的组合，这要归功于你们俩

select A.ContactID,
                A.Forename,
                A.Surname,
                A.[House Number],
                A.AddressLine1,
                A.AddressLine2,
                A.[Business Name] from (
SELECT DISTINCT tblcontact.ContactID,
                tblcontact.Forename,
                tblcontact.Surname,
                tbladdress.[House Number],
                tbladdress.AddressLine1,
                tbladdress.AddressLine2,
                tblcontact.[Business Name]
FROM   tblcontact
       INNER JOIN tbladdress
               ON tblcontact.AddressID = tbladdress.AddressID
       LEFT OUTER JOIN tblDonate
                    ON tblcontact.ContactID = tblDonate.ContactID
WHERE  ( tbladdress.CollectionArea = 'Queens Park' )
       AND ( tbladdress.AddressLine1 = 'Kings Road' )) A
       ORDER BY CAST(SUBSTRING(A.[House Number], PATINDEX('%[0-9]%', A.[House Number]),
                 1 + PATINDEX('%[0-9][^0-9]%', A.[House Number] + ' ') -
                 PATINDEX('%[0-9]%',A.[House Number])) AS INT)
相关文章：