在SQL函数中排序 [英] Sorting in SQL Functions

问题描述

我有返回表的功能.

I have function which returns a table.

Id      Name    Total   Status         
1	Kitu	565	SC
2	Kavya	576	FC
3	rajni	98	07/08/2011
4	devi	49	07/05/2010
5	laya	637	FC
6	gita	32	09/08/2011
7	guru	981	FCD
8	dev	63	07/05/2012
9	magi	45	09/10/2011
10	malya	562	SC
11	Kavi	56	09/10/2011
12	raki	89	SC
13	Rana	56	18/04/2011
14	rajiv	56	01/04/2011
15	ravi	56	09/09/2011
16	surya	56	26/04/2011
17	dev	56	06/05/2011
18	dina	56	12/1/2011

SELECT  * FROM [dbo].[fn_get_Student]() ORDER BY 
CASE
    WHEN ISDATE( [Status] ) = 1 THEN CONVERT( VARCHAR(50), CONVERT( DATETIME, [Status] ), 112 )
    ELSE [Status]
END

结果:

Result:

Id      Name    Total   Status
13	Rana	56	18/04/2011
4	devi	49	07/05/2010
14	rajiv	56	01/04/2011
17	dev	56	06/05/2011
3	rajni	98	07/08/2011
6	gita	32	09/08/2011
15	ravi	56	09/09/2011
9	magi	45	09/10/2011
11	Kavi	56	09/10/2011
18	dina	56	12/1/2011
8	dev	63	07/05/2012
16	surya	56	26/04/2011
5	laya	637	FC
2	Kavya	576	FC
7	guru	981	FCD
1	Kitu	565	SC
12	raki	89	SC
10	malya	562	SC

状态列由日期和nvarchar值组成.如您所见，状态日期栏的格式不正确.

Status Column Consists of both date and nvarchar values. As you can see the date of status column, which is not sorted in correct format.

Is there any other approch to achive the task?

推荐答案

即使结果来自函数，列也应始终排序良好.但是，可能的问题可能是返回列的数据类型错误，例如，当应使用double时为字符串等.

如果不是这种情况，请发布数据示例以及数据的排序方式(错误的结果)

Columns should always sort nicely even if the results come from a function. However a possible problem could be that the datatype of the returning column is wrong, for example string when double should be used etc.

If that''s not the case, please post an example of the data and how it''s sorted (wrong results)

此查询显示如何使用case语句进行排序.希望对您有所帮助.

This query shows how can sort using case statements. I hope this helps.

SET DATEFORMAT DMY

IF OBJECT_ID( 'tempdb..#TEMP' ) IS NOT NULL
    BEGIN
    DROP TABLE #TEMP
    END
CREATE TABLE #TEMP
    (
    [Id]        INT,
    [Name]      VARCHAR(50),
    [Total]     INT,
    [Status]    VARCHAR(50)
    
    )
INSERT INTO #TEMP([Id],[Name],[Total],[Status])
SELECT  1,  'Kitu',     565,    'SC'            UNION ALL
SELECT  2,  'Kavya',    576,    'FC'            UNION ALL
SELECT  3,  'rajni',    98,     '07/08/2011'    UNION ALL
SELECT  4,  'devi',     49,     '07/05/2010'    UNION ALL
SELECT  5,  'laya',     637,    'FC'            UNION ALL
SELECT  6,  'gita',     32,     '09/08/2011'    UNION ALL
SELECT  7,  'guru',     981,    'FCD'           UNION ALL
SELECT  8,  'dev',      63,     '07/05/2012'    UNION ALL
SELECT  9,  'magi',     45,     '09/10/2011'    UNION ALL
SELECT  10, 'malya',    562,    'SC'


SELECT  *
FROM    #TEMP
ORDER
BY      CASE
            -- when [Status] is date then converts date from MM/DD/YYYY format to YYYYMMDD FORMAT
            WHEN ISDATE( [Status] ) = 1 THEN CONVERT( VARCHAR(50), CONVERT( DATETIME, [Status] ), 112 )
            ELSE [Status]
        END

如果函数返回带有任何数据类型的RunningBalance列的表.可以与上述查询配合使用.

If the function return the table with RunningBalance column of any datatype . its working fine with above query.

这篇关于在SQL函数中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！