SQL查询中的投影/复制?
问题描述:
我的SQL是一个有点生疏 - 有一个SQL的方式来预计,看起来像这样的输入表:SQL查询中的投影/复制?
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(插槽)的枚举(插槽),并且这些'插槽'中的每一个的和应当反映SlotValues的总和投射到'*'的位置。在病理情况下:
Name SlotValue Slots
---- --------- -----
ABC 4 3
我们应该得到:
Name SlotSum Slot
---- ------- ----
ABC 4 1
ABC 4 2
ABC 4 3
总和逻辑是非常简单的 - 每个项目中SlotValue出槽的数量:
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)
更新:最后,我在存储过程中使用了LOCALGHOST方法的变体。我希望有一种方法可以在没有循环的情况下做到这一点。
答
我不确定你能否在视图中做到这一点。你必须使用一个程序。 您也可以在过程中使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
答
在这里,你去。这将使目前的形式达到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
啊我仍然不明白,你所说的“项目” – Shawn 2008-10-15 03:47:41
意思好吧,我想我现在就买下 – Shawn 2008-10-15 03:49:51