Sql 多表查询

Question

我使用 SQL 2000 和数据库有以下表格

LandParcel (Table Name)
BlockID  ParcelNo NameofOnwe
11001    1056     Chandana
11001    1078     Sisil
11001    1158     Kumara
11078    105      SK
11078    245      Shantha

Actions (Table)
Blockid  ParcelNo  ActionTaken
11001    1056      Received
11001    1078      Received
11001    1158      Received
11078    105       Received
11078    245       Received
11001    1056      Send To LR
11001    1078      Send to LR
11078    105       Send To LT

我想要以下查询

Blockid  ActionTaken   ParcelNos
11001    Received      1056, 1078, 1158
11078    Received      105, 245
11001    Send To LR    1056, 1078
11078    Send To LR    105

请帮助我 旃陀那

Answer 1

我不太喜欢光标，但我找不到其他解决方案。我稍后可能会尝试在没有光标的情况下执行此操作。

 /* Sample data.*/


create TABLE #LandParcel (BlockID INT,  ParcelNo INT, NameofOnwe VARCHAR(50))

insert INTo #LandParcel select 11001   , 1056 ,   'Chandana'
insert INTo #LandParcel select 11001  ,  1078   , 'Sisil'
insert INTo #LandParcel select 11001   , 1158  ,  'Kumara'
insert INTo #LandParcel select 11078   , 105  ,   'SK'
insert INTo #LandParcel select 11078  ,  245   ,   'Shantha'


CREATE TABLE #Actions (Blockid INT, ParcelNo  INT, ActionTaken VARCHAR(50))

insert INTo #Actions select 11001 ,   1056    ,'Received'
insert INTo #Actions select 11001  ,  1078   , 'Received'
insert INTo #Actions select 11001   , 1158  ,  'Received'
insert INTo #Actions select 11078   , 105    , 'Received'
insert INTo #Actions select 11078  ,  245    , 'Received'
insert INTo #Actions select 11001  ,  1056    ,  'Send To LR'
insert INTo #Actions select 11001   , 1078    ,  'Send to LR'
insert INTo #Actions select 11078  ,  105     ,  'Send To LT'

/* End sample data */

/* Update query*/

CREATE TABLE #temp (BlockId INT, ActionTaken VARCHAR(50), ParcelNumbers VARCHAR(100))

INSERT  INTO #temp
SELECT  l.blockid, a.ActionTaken, NULL
FROM    #LandParcel l INNER JOIN #Actions a on l.blockid = a.blockid
GROUP BY l.blockid, a.actiontaken

DECLARE @blockId INT
DECLARE @actionTaken VARCHAR(50)
DECLARE @parcel VARCHAR(100)

SET @parcel = ''

DECLARE @cursorParcel CURSOR
    SET     @cursorParcel = CURSOR FAST_FORWARD
    FOR
    SELECT  blockid, ActionTaken FROM #temp

    OPEN    @cursorParcel

    FETCH   NEXT    FROM    @cursorParcel
    INTO    @blockId,
            @actionTaken
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @parcel = CASE @parcel 
                WHEN '' THEN convert(VARCHAR(10), a.ParcelNo )
                ELSE @parcel + ', ' + convert(VARCHAR(10), a.ParcelNo ) 
                END
        from #temp t INNER JOIN #Actions a on t.blockid = a.blockid and t.actiontaken = a.actiontaken
        where t.BlockId = @blockId
        AND     t.ActionTaken = @actionTaken

        UPDATE  #temp SET ParcelNumbers = @parcel
        WHERE   BlockId = @blockId
        AND     ActionTaken = @actionTaken

        SET @blockId        = NULL
        SET @actionTaken    = NULL
        SET @parcel         = ''

        FETCH   NEXT    FROM    @cursorParcel
        INTO    @blockId,
                @actionTaken

    END


CLOSE       @cursorParcel
DEALLOCATE  @cursorParcel

SELECT * FROM #temp

DROP TABLE #temp
DROP TABLE #LandParcel
DROP TABLE #Actions

Answer 2

您可以使用 SQL Server 2000 中的 FOR XML RAW 来模拟 SQL Server 2005 的 FOR XML PATH 行为：

SELECT
  a.BlockID,
  a.ActionTaken,
  REPLACE(
  REPLACE(
  REPLACE(
  (SELECT ParcelNo
   FROM Actions
   WHERE BlockID = a.BlockID
   AND ActionTaken = a.ActionTaken
   ORDER BY ParcelNo
   FOR XML RAW), '"/><row ParcelNo="', ', '),
   '<row ParcelNo="', ''),
   '"/>', '')  AS ParcelNos
FROM Actions a 
INNER JOIN LandParcel l 
  ON a.BlockID = l.BlockID
  AND a.ParcelNo = l.ParcelNo
GROUP BY 
  a.BlockID,
  a.ActionTaken
ORDER BY 
  a.ActionTaken