- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- CTE中如何避免重复值 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=116981)
-- 发布时间：2018/4/3 19:41:00
-- CTE中如何避免重复值
WITH CTE
AS (SE LECT a.产品编码,
CAST(NULL AS NVARCHAR(20)) AS 父件编码,
CAST(1 AS FLOAT) AS 用量,
CAST(0 AS FLOAT) AS 损耗,
0 AS Levle,
CAST(产品编码 AS VARCHAR(MAX)) AS path
FROM (SE LECT DISTINCT 产品编码 FROM MPS01) a
UNION ALL
SE LECT BOM.产品编码,
BOM.父件编码,
BOM.用量,
BOM.损耗,
c.Levle + 1 AS Levle,
CAST(c.path + \'\\\' + BOM.产品编码 AS VARCHAR(MAX)) AS path
FROM CTE AS c
INNER JOIN BOM
ON c.产品编码 = BOM.父件编码
WHERE NOT EXISTS (SE LECT * FROM CTE WHERE CTE.产品编码 = bom.产品编码 and CTE.父件编码 = bom.父件编码)
)
上述where条件的作用就是每次循环时产品编码及父件编码相同的值就不要加入进去,因为CTE不能第二次引用 ,要如何写才能实现这个功能?
谢谢!
-- 发布时间：2018/4/3 20:31:00
--
麻烦导一部分数据过来测试
-- 发布时间：2018/4/4 16:50:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:a01.rar |
两个表MPS01及BOM的带数据脚本如附件,
目的就是想用MPS01中的唯一产品编码按BOM表递归展开,要求CTE查询结果中的产品编码与父件编码的组合值唯一,即把重复值过虑掉
此主题相关图片如下：a01.jpg
谢谢!
-- 发布时间：2018/4/4 17:54:00
--
WITH CTE
AS (SELECT a.产品编码,
CAST(NULL AS NVARCHAR(20)) AS 父件编码,
CAST(1 AS FLOAT) AS 用量,
CAST(0 AS FLOAT) AS 损耗,
0 AS Levle,
CAST(产品编码 AS VARCHAR(MAX)) AS path
FROM (SELECT DISTINCT 产品编码 FROM MPS01) a
UNION ALL
SELECT BOM.产品编码,
BOM.父件编码,
BOM.用量,
BOM.损耗,
c.Levle + 1 AS Levle,
CAST(c.path + \'\\\' + BOM.产品编码 AS VARCHAR(MAX)) AS path
FROM CTE AS c
INNER JOIN BOM
ON c.产品编码 = BOM.父件编码
)
select * from (SELECT *,ROW_NUMBER() over(partition by 产品编码,父件编码 order by levle desc) as rowNum from cte) as a where rowNum = 1
-- 发布时间：2018/4/4 20:46:00
--
这种方法是先生成再筛选出来,,如果直接在CTE中的递归中加入条能否实现?还是CTE中根本不能那样用?
谢谢!
-- 发布时间：2018/4/4 20:51:00
--
无法做到你说的去重复。
-- 发布时间：2021/1/30 11:31:00
--
CTE 在 狐表中怎样调用
-- 发布时间：2021/1/30 12:02:00
--
和普通的sql一样用：http://www.foxtable.com/webhelp/topics/0696.htm