有表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.BididORDER BY CHARINDEX(SUBSTRING(A.BidName, 1, 1), '一二三四五六七八九十');