代码之家 › 专栏 › 技术社区 › lesscode

SQL查询中的投影/复制?

tsql sql

lesscode · 技术社区 · 17 年前

Name                SlotValue              Slots
----                ---------              -----
ABC                 3                      1
ABC                 4                      2
ABC                 6                      5

Name                SlotSum                 Slot
----                -------                 ----
ABC                 13                      1
ABC                 10                      2
ABC                 6                       3
ABC                 6                       4
ABC                 6                       5

换言之,结果集应包含数量等于MAX(插槽)的行,从1到MAX(插槽)枚举(插槽),每个“插槽”的总和应反映投影到“插槽”位置的插槽值总和。对于病理病例:

Name               SlotValue                Slots
----               ---------                -----
ABC                4                        3

我们应该得到:

Name               SlotSum                  Slot
----               -------                  ----
ABC                4                        1
ABC                4                        2
ABC                4                        3

SlotValue         SlotValue        SlotValue       Slot             Sum
---------         ---------        ---------       ----             ---
3                 4                6               1                13 (3+4+6)
0                 4                6               2                10 (0+4+6)
0                 0                6               3                6 (0+0+6)
0                 0                6               4                6 (0+0+6)
0                 0                6               5                6 (0+0+6)

2 回复 | 直到 17 年前

Shawn 17 年前

我不确定你是否能在一个视野中做到这一点。你必须使用一个程序。您也可以在过程中将ProjectedTable设置为临时/变量表。我真的很想看看你是如何把它放到一个视图中的,因为你需要动态地生成一系列的数字。

declare @maxSlot int 
set @maxSlot = select max(slots) from SlotTable

truncate ProjectedTable
while @i > 0 
    begin
    insert into ProjectedTable (
          SlotSum
          ,Slot
    ) values (
          (select sum(slotValue) from SlotTable where slots >= @maxSlot)
         ,@maxSlot
    )
    set @maxSlot = @maxSlot - 1 
end
select SlotSum, Slot from ProjectedTable

Taryn Frank Pearson 12 年前

干得好。在其当前形式下,最多可使用100个插槽。你可以运用你的想象力来容纳更多的人。

DECLARE @SLOT TABLE
    (
    SlotName varchar(25) NOT NULL,
    SlotValue int NOT NULL,
    Slot int NOT NULL
    )

INSERT INTO @SLOT (SlotName, SlotValue, Slot)
    SELECT 'ABC', 3, 1
    UNION
    SELECT 'ABC', 4, 2
    UNION
    SELECT 'ABC', 6, 5

SELECT
    CASE
        WHEN SLOT.SlotName IS NOT NULL THEN SLOT.SlotName
        ELSE 
            COALESCE(
            (SELECT TOP 1 SL.SlotName FROM @SLOT AS SL WHERE SL.Slot < SLOT_PROJECT.Slot ORDER BY SL.Slot DESC),
            (SELECT TOP 1 SL.SlotName FROM @SLOT AS SL WHERE SL.Slot > SLOT_PROJECT.Slot ORDER BY SL.Slot ASC)
            )
    END AS SlotName,
    (
    SELECT
        SUM(SLOT10.SlotValue)
    FROM
        @SLOT AS SLOT10
    WHERE
        SLOT10.Slot >= SLOT_PROJECT.Slot
    ) AS SlotSum,
    SLOT_PROJECT.Slot
FROM
    (
    SELECT
        (TENS.Seq + ONES.Seq) AS Slot
    FROM
        (
        SELECT 1 AS Seq
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL 
        SELECT 7
        UNION ALL
        SELECT 8
        UNION ALL
        SELECT 9
        ) AS ONES
        CROSS JOIN
        (
        SELECT 0 AS Seq
        UNION ALL
        SELECT 10
        UNION ALL
        SELECT 20
        UNION ALL
        SELECT 30
        UNION ALL
        SELECT 40
        UNION ALL
        SELECT 50
        UNION ALL
        SELECT 60
        UNION ALL 
        SELECT 70
        UNION ALL
        SELECT 80
        UNION ALL
        SELECT 90
        ) AS TENS
    WHERE
        (TENS.Seq + ONES.Seq) <= (SELECT MAX(Slot) FROM @SLOT)
    ) AS SLOT_PROJECT
    LEFT JOIN @SLOT AS SLOT ON
        SLOT.Slot = SLOT_PROJECT.Slot