在sql中，如何使用nvarchar中的强制转换对行号进行排序 [英] In sql,How to sort row number using cast in nvarchar

问题描述

declare @Company_Code int=5
declare @i int =1
--while @i<5991
--begin
--insert into test values('test')
SELECT distinct @Company_Code,
       --CustomerID,
       'bc'+ cast(row_number()over(order by customerID) as varchar)as BillCode,
       AddressLine1,
       AddressLine2,
       City_Suburb,
       StateAbbreviation,
       Zip_Postcode,
       CC.Country_Code,       
       Phone ,
       Phone as phone_ext,	   
       Email,
       '' as Fax,
       GETDATE() as create_date_time,
       'SYSTEM' as create_user_id,
       GETDATE() as last_update_date_time,
       'SYSTEM' as last_update_user_id
from MTrackDataImport..rawCustomerSISU C
	LEFT OUTER JOIN CD_country CC
	ON (CC.Company_Code = @Company_Code
	AND C.Country = CC.Country_Name)

这是我的查询。我收到的结果却没有排序。所以请帮助我。只有排序不起作用。那么如何排序请告诉我。

This is my query. I am getting results but it not get sorted. So plz help me. Only sorting is not working. So how to sort please tell me.

推荐答案

- 方法1：你可以通过customerID而不是BillCode来做到这一点（因为前缀中没有差异）



SELECT distinct @Company_Code，

--CustomerID，

'bc'+ cast（row_number（）over（通过customerID订购）作为varchar）作为BillCode，

AddressLine1，

AddressLine2，

City_Suburb，

StateAbbreviation，

Zip_Postcode，

CC.Country_Code，

电话，

手机为phone_ext，

电子邮件，

''作为传真，

GETDATE（）为create_date_time，

'SYSTEM'为create_user_id，

GETDATE（）as last_update_date_time，

'SYSTEM'as last_update_user_id

from MTrackDataImport..rawCustomerSISU C

LEFT OUTER JOIN CD_country CC

ON（CC.Company_Code = @Company_Code

AND C.Country = CC.Country_Name）



按customerID订购



- 方法2：你可以通过普通的表格表达来做到这一点CTE





; CTE为（SELECT distinct @Company_Code，

--CustomerID，

'bc'+ cast（row_number（）over（orderID by customerID）as varchar）作为BillCode，

AddressLine1，

AddressLine2，

City_Suburb，

StateAbbreviation，

Zip_Postcode，

CC.Country_Code，

电话，

电话为phone_ext，

电子邮件，

''作为传真，

GETDATE（）为create_date_time，

'SYSTEM'为create_user_id，

GETDATE（ ）作为last_update_date_time，

'FEC'作为last_update_user_id
来自MTrackDataImport的
。 .rawCustomerSISU C

LEFT OUTER JOIN CD_country CC

ON（CC.Company_Code = @Company_Code

AND C.Country = CC.Country_Name）< br $> b $ b

）

从BillCode的cte订单中选择*

-- method 1 : you can do this by customerID instead of BillCode (because there is no diff in prefix)

SELECT distinct @Company_Code,
--CustomerID,
'bc'+ cast(row_number()over(order by customerID) as varchar)as BillCode,
AddressLine1,
AddressLine2,
City_Suburb,
StateAbbreviation,
Zip_Postcode,
CC.Country_Code,
Phone ,
Phone as phone_ext,
Email,
'' as Fax,
GETDATE() as create_date_time,
'SYSTEM' as create_user_id,
GETDATE() as last_update_date_time,
'SYSTEM' as last_update_user_id
from MTrackDataImport..rawCustomerSISU C
LEFT OUTER JOIN CD_country CC
ON (CC.Company_Code = @Company_Code
AND C.Country = CC.Country_Name)

order by customerID

-- method 2 : you can do this by common table expression CTE


;with CTE as (SELECT distinct @Company_Code,
--CustomerID,
'bc'+ cast(row_number()over(order by customerID) as varchar)as BillCode,
AddressLine1,
AddressLine2,
City_Suburb,
StateAbbreviation,
Zip_Postcode,
CC.Country_Code,
Phone ,
Phone as phone_ext,
Email,
'' as Fax,
GETDATE() as create_date_time,
'SYSTEM' as create_user_id,
GETDATE() as last_update_date_time,
'SYSTEM' as last_update_user_id
from MTrackDataImport..rawCustomerSISU C
LEFT OUTER JOIN CD_country CC
ON (CC.Company_Code = @Company_Code
AND C.Country = CC.Country_Name)

)
select * from cte order by BillCode

这篇关于在sql中，如何使用nvarchar中的强制转换对行号进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！