ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 25164|回复: 3

[SQL] 递归CTE到层次查询的等价改写问题

[复制链接]
论坛徽章:
0
发表于 2017-2-25 16:36 | 显示全部楼层 |阅读模式
今天看到了一篇介绍MySQL实验分支上的递归CTE文章, 其中一条查询语句

-- Q1:
WITH my_cte(a) AS
        (
                SELECT a from t  
                UNION ALL
                SELECT 2+a FROM my_cte   WHERE a <10
    )
SELECT * FROM my_cte;
  
我想用层次查询进行改写,没能成功。论坛里搜了下相关文章,也没找到合适的答案。
也许前者功能本来就更为强大?

希望有高人来指点一二

附初始化脚本和我的改写语句

--Q1'
SELECT A + 2 * (LEVEL-1) FROM T CONNECT BY NOCYCLE   A+2*(LEVEL-1) < 10;
-- 完全不对
SELECT A+B FROM T, (SELECT 2 *(LEVEL – 1) B FROM DUAL CONNECT BY LEVEL < 10)
        WHERE A + (B-2)< 10;
-- 结果虽然正确,但过程不知所云...

-- Q2: 另一条容易被改写的语句
WITH   cte(category_id, name, depth) AS
(
       SELECT category_id, name, 0  FROM category WHERE parent IS NULL
       UNION ALL
       SELECT c.category_id, c.name, cte.depth+1 FROM category c JOIN cte ON cte.category_id=c.parent
  )
  SELECT * FROM cte ORDER BY depth;
--->
SELECT category_id, name, level-1 AS depth
FROM category
start with parent IS NULL
connect by prior category_id = parent
order by depth;

--初始化
drop table t;
CREATE TABLE t(a int);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(5);
commit;

drop table category;
CREATE TABLE category(
        category_id INT  ,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL);
INSERT INTO category VALUES(2,'TELEVISIONS',1);
INSERT INTO category VALUES(3,'TUBE',2);
INSERT INTO category VALUES(4,'LCD',2);
INSERT INTO category VALUES(5,'PLASMA',2);
INSERT INTO category VALUES(6,'PORTABLE ELECTRONICS',1);
INSERT INTO category VALUES(7,'MP3 PLAYERS',6);
INSERT INTO category VALUES(8,'FLASH',7);
INSERT INTO category VALUES(9,'CD PLAYERS',6);
INSERT INTO category VALUES(10,'2 WAY RADIOS',6);
commit;       


论坛徽章:
395
阿斯顿马丁
日期:2014-01-03 13:53:52马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07懒羊羊
日期:2015-02-21 22:03:31
发表于 2017-2-25 22:20 来自手机 | 显示全部楼层
结果正确是因为你的数据碰巧

使用道具 举报

回复
论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
发表于 2017-2-25 23:22 | 显示全部楼层
SELECT A + 2 * (LEVEL-1) FROM T CONNECT BY NOCYCLE   A+2*(LEVEL-1-1) < 10
AND A=PRIOR A AND PRIOR SYS_GUID() IS NOT NULL
;

CTE的功能强大了很多,如果有兴趣可以看我们写的书“剑破冰山”,还有ITPUB历届的SQL大赛题目。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-2-26 13:05 | 显示全部楼层
感谢二位回复~
itpub不常来,但二位的大名还是知道的。

昨天也搜到了@newkid 2010年的递归cte文章,叹为观止

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表