sql中的order by子句，起始字符为新行

本文介绍了sql中的order by子句，起始字符为新行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好，

i希望查询sql中的Order by子句，并将起始字符作为新行。

ColumnName

澳大利亚

A1

科伦坡

巴西

OutPut：

ColumnName

A

A1

澳大利亚

B

巴西

C

科伦坡

选择添加新行的查询，其中包含起始字符A，B，C，D等

Hi all,

i want a query for Order by clause in sql with starting character as new row.

ColumnName
Austrilia
A1
Colombo
Brazil

OutPut:

ColumnName
A
A1
Austrilia
B
Brazil
C
Colombo

Select query for adding a new row with starting character "A,B,C,D etc"

推荐答案

WITH CTE
AS
(
    Select 'Austrilia' as Country
    UNION
    Select 'A1' as country
    UNION
    Select 'Colombo' as country
    UNION
    SELECT 'Brazil' as country
),

CTEModified
AS
(
    Select SUBSTRING(country, 1, 1) as country FROM CTE
    Union
    SElect country from CTE
)

SELECT * FROM CTEModified

DECLARE @tmp TABLE (Country VARCHAR(30))

INSERT INTO @tmp (Country)
VALUES ('Austrilia'), ('A1'), ('Colombo'), ('Brazil')

SELECT Country
FROM (
    SELECT Country
    FROM @tmp
    UNION ALL
    SELECT LEFT(Country,1) AS Country
    FROM @tmp
) AS T
ORDER BY Country

这篇关于sql中的order by子句，起始字符为新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！