sql按中文数字排序

 

 

有表4张

建表和插入数据sql

DECLARE @p_Building TABLE
    (
      id INT ,
      BidName NVARCHAR(20)
    );
DECLARE @p_Room TABLE
    (
      id INT ,
      RoomNo INT ,
      RoomArea INT ,
      Bidid INT
    );
DECLARE @p_Customer TABLE
    (
      id INT ,
      CstName NVARCHAR(20) ,
      CstTel VARCHAR(20) ,
      CstSex NVARCHAR(5)
    );
DECLARE @p_Cst2Room TABLE
    (
      id INT ,
      RoomID INT ,
      CstID INT ,
      CstNO INT
    );
INSERT  INTO @p_Building( id, BidName ) VALUES  ( 1, N'四栋' );
INSERT  INTO @p_Building( id, BidName ) VALUES  ( 2, N'一栋' );
INSERT  INTO @p_Building( id, BidName ) VALUES  ( 3, N'二栋' );
INSERT  INTO @p_Building( id, BidName ) VALUES  ( 4, N'三栋' );

INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 1, 101, 80, 1)
INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 2, 102, 80, 2)
INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 3, 103, 99, 3)
INSERT INTO @p_Room( id, RoomNo, RoomArea, Bidid ) VALUES  ( 4, 104, 87, 4)

INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 1,N'张三','',N'')
INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 2,N'李四','',N'')
INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 3,N'王五','',N'')
INSERT INTO @p_Customer( id, CstName, CstTel, CstSex )VALUES  ( 4,N'赵六','',N'')

INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 1,1,1,1)
INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 2,1,2,2)
INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 3,1,3,3)
INSERT INTO @p_Cst2Room( id, RoomID, CstID, CstNO )VALUES  ( 4,1,4,4)

要得到下面结果：

执行以下sql：

SELECT  A.BidName AS [楼栋名称] ,
        B.RoomNo AS [房号] ,
        ( SELECT    C.CstName + ','
          FROM      @p_Customer AS C
                    INNER JOIN @p_Cst2Room AS D ON C.id = D.CstID
                                                   AND D.RoomID = B.id
        FOR
          XML PATH('')
        ) AS [客户名称] ,
        B.RoomArea AS [房间面积]
FROM    @p_Building AS A
        INNER JOIN @p_Room AS B ON A.id = B.Bidid; --AND A.id=1

要得到以下结果

SELECT  A.BidName AS [楼栋名称] ,
        CHARINDEX(SUBSTRING(A.BidName, 1, 1), '一二三四五六七八九十') AS OrderNum ,
        B.RoomNo AS [房号] ,
        B.RoomArea AS [房间面积]
FROM    @p_Building AS A
        INNER JOIN @p_Room AS B ON A.id = B.Bidid
ORDER BY CHARINDEX(SUBSTRING(A.BidName, 1, 1), '一二三四五六七八九十');