|
SQLServer 临时表、表变量、 CTE
?
本文原本是为了说明游标在某些特定环境下的用途，突然发现用来说明临时表、表变量和 CTE 的关系更为合理一些。
本文的例子给了一串数字，是为了求得同组中所有数字的乘积，包括累计叠加等等，这样的案例通过一般的分组方式很难办到，而通过游标更容易实现。
创建一个测试 表
|
CREATE TABLE groups
(
???????? groupid VARCHAR(10),
???????? val INT NULL
)
INSERT INTO groups VALUES ('a',2);
INSERT INTO groups VALUES ('a',3);
INSERT INTO groups VALUES ('a',1);
INSERT INTO groups VALUES ('b',4);
INSERT INTO groups VALUES ('b',6);
INSERT INTO groups VALUES ('b',1);
INSERT INTO groups VALUES ('c',10);
INSERT INTO groups VALUES ('c',3);
INSERT INTO groups VALUES ('d',7);
通过临时表和游标的方法加以实现
CREATE TABLE #result (groupid VARCHAR(10),product BIGINT);
?
DECLARE @groupid VARCHAR(10),@prvgroupid VARCHAR(10),@val INT,@product BIGINT;
DECLARE c CURSOR FAST_FORWARD FOR SELECT groupid,val FROM groups ORDER BY groupid;
OPEN c;
FETCH NEXT FROM c INTO @groupid,@val;
SELECT @prvgroupid=@groupid,@product=1;
WHILE @@fetch_status=0
BEGIN
???????? IF @groupid<>@prvgroupid
???????? BEGIN
?????????????????? INSERT INTO #result VALUES(@prvgroupid,@product);
?????????????????? SELECT @prvgroupid=@groupid,@product=1;
???????? END
???????? SET @product=@product*@val;
???????? FETCH NEXT FROM c INTO @groupid,@val;
END
IF @prvgroupid IS NOT NULL
???????? INSERT INTO #result VALUES(@prvgroupid,@product);
CLOSE c;
DEALLOCATE c;
SELECT groupid,product FROM #result
通过表变量和游标的方式加以实现，从这里看的话表变量和临时表基本雷同
DECLARE @groupid VARCHAR(10),@product=1;
WHILE @@fetch_status=0
BEGIN
???????? IF @groupid<>@prvgroupid
???????? BEGIN
???????? ? INSERT INTO #result VALUES(@prvgroupid,@product);
???????? ? SELECT @prvgroupid=@groupid,@val;
???????? END
IF @prvgroupid IS NOT NULL
???????? INSERT INTO #result VALUES(@prvgroupid,product FROM #result
通过 CTE 的方式加以实现
WITH all_but_val AS
(
SELECT a.groupid,a.val*b.val accum_val
? FROM groups a,groups b
? WHERE a.groupid=b.groupid and a.val>b.val
),
but_val AS
(
SELECT * FROM groups
? WHERE groupid IN (SELECT groupid
? FROM groups
? GROUP BY groupid
HAVING COUNT(*)=1)
),
all_val AS
(
? SELECT * FROM all_but_val
? UNION
? SELECT * FROM but_val
)
SELECT groupid,max(accum_val) FROM all_val
GROUP BY groupid
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
