如何在sql server 2008中使用不同的子字符串

My query is

我的查询

select distinct PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
order by  PoNo

It shows like as follows order

它显示如下顺序

But I need PO0001, Po0002, PO0003, PO0004.....

但是我需要PO0001, Po0002, PO0003, PO0004…

So, i am using

所以,我使用

select distinct PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
order by SUBSTRING(PoNo,5,1)

But it shows an error like

但它显示了一个类似的错误

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Help me

如果指定了select DISTINCT，则必须在select列表中显示项的顺序。帮助我

3 个解决方案

#1

You can use GROUP BY instead:

你可以用GROUP BY代替:

SELECT PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
GROUP BY PoNo, PoDate, SupplierId, BillNo
ORDER BY SUBSTRING(PoNo, 5, 1)

#2

You can also try following one:

你也可以试试下面的方法:

SELECT PoNo, PoDate, SupplierId, BillNo
FROM (
    select distinct CAST(SUBSTRING(PoNo,2,LEN(PoNo)-2) AS INT) AS Pid, PoNo, PoDate, SupplierId, BillNo
    FROM Purchase_Product
) AS tbl
order by  Pid

#3

SELECT PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
GROUP BY PoNo, PoDate, SupplierId, BillNo
ORDER BY Cast(SUBSTRING(PoNo, 3, len(PoNo)) as int)

Extract the number from PoNo and orderding by that number.

从PoNo中提取编号并按该编号排序。

#1