TASK#29114 【共享库的慢sql】慢SQL优化 / 系统管理部 - 系统服务优化

任务描述

1. 问题分析

温林生，共享库的慢sql，执行完成需76秒

分析发现缺少索引

添加索引：IX_E_Amazon_BrowseTree_Node_V2_ID_Country_BrowseNodeID_BrowseNodeName

添加索引后还是慢，注释掉Replace(Stuff( 等函数，只查询temp表的数据1秒出结果

2.解决方案

建议将temp查询的数据转移到应用程序里计算，应用程序里的资源可以保证，数据库计算当资源被其他业务使用过多是，很慢。

3. sql

IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01')
AND type = 'U')
DROP TABLE #tblbrowsePathByIDTemp01;

CREATE TABLE #tblbrowsePathByIDTemp01
(
BrowsePathByID VARCHAR(400) PRIMARY KEY
);

INSERT INTO #tblbrowsePathByIDTemp01
SELECT item AS BrowsePathByID
FROM Charsplitforin(
'165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011'
, '|');

WITH Temp
AS (SELECT *,
0 AS Leavel
FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK)
WHERE EXISTS (SELECT 1
FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK)
WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID)
UNION ALL
SELECT a.*,
tmp.Leavel + 1
FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK)
INNER JOIN Temp tmp
ON a.BrowsePathByID = tmp.ParentBrowsePathByID
AND a.HasChildren = 1
AND a.ChildNodesCount > 0
AND a.Country = tmp.Country
WHERE EXISTS (SELECT 1
FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK)
WHERE b.BrowsePathByID = a.BrowsePathByID))

select * from Temp AS tmp; -- 建议将返回的Temp 数据到应用程序里计算
--SELECT tmp.BrowsePathByID,
-- tmp.Country,
-- BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '('
-- + Cast(ChildNodesCount AS VARCHAR(10)) + ')'
-- + '(' + BrowseNodeNameCn + ')'
-- FROM (SELECT tmp.*,
-- cct.BrowsePathByID AS groupBrowsePathByID,
-- Row_number()
-- OVER (
-- PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel
-- ORDER BY tmp.Leavel ) AS RN
-- FROM Temp AS tmp
-- INNER JOIN Temp AS cct (NOLOCK)
-- ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t
-- WHERE t.RN = 1
-- AND t.groupBrowsePathByID = tmp.BrowsePathByID
-- ORDER BY t.Leavel DESC
-- FOR XML PATH('')), 1, 1, ''), ',', '>>')
--FROM Temp AS tmp
--WHERE tmp.HasChildren = 0;

4.优化后的效果验证在次跟踪记录

历史记录

2022-07-01 09:21:08, 由 陈少军 创建。
2022-07-01 09:21:39, 由 陈少军 编辑。
修改了 截止日期，旧值为 "2022-06-30"，新值为 "2022-07-07"。
修改了 任务描述，区别为：
012- IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01;CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY );INSERT INTO #tblbrowsePathByIDTemp01SELECT item AS BrowsePathByIDFROM Charsplitforin('165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011', '|');WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID))SELECT tmp.BrowsePathByID, tmp.Country, BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' + Cast(ChildNodesCount AS VARCHAR(10)) + ')' + '(' + BrowseNodeNameCn + ')' FROM (SELECT tmp.*, cct.BrowsePathByID AS groupBrowsePathByID, Row_number() OVER ( PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel ORDER BY tmp.Leavel ) AS RN FROM Temp AS tmp INNER JOIN Temp AS cct (NOLOCK) ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t WHERE t.RN = 1 AND t.groupBrowsePathByID = tmp.BrowsePathByID ORDER BY t.Leavel DESC FOR XML PATH('')), 1, 1, ''), ',', '>>')FROM Temp AS tmpWHERE tmp.HasChildren = 0;
012+ IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01;CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY );INSERT INTO #tblbrowsePathByIDTemp01SELECT item AS BrowsePathByIDFROM Charsplitforin('165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011', '|');WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID))SELECT tmp.BrowsePathByID, tmp.Country, BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' + Cast(ChildNodesCount AS VARCHAR(10)) + ')' + '(' + BrowseNodeNameCn + ')' FROM (SELECT tmp.*, cct.BrowsePathByID AS groupBrowsePathByID, Row_number() OVER ( PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel ORDER BY tmp.Leavel ) AS RN FROM Temp AS tmp INNER JOIN Temp AS cct (NOLOCK) ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t WHERE t.RN = 1 AND t.groupBrowsePathByID = tmp.BrowsePathByID ORDER BY t.Leavel DESC FOR XML PATH('')), 1, 1, ''), ',', '>>')FROM Temp AS tmpWHERE tmp.HasChildren = 0;
013-
013+

012- IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01; CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY ); INSERT INTO #tblbrowsePathByIDTemp01 SELECT item AS BrowsePathByID FROM Charsplitforin( '165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011' , '|'); WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID)) SELECT tmp.BrowsePathByID, tmp.Country, BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' + Cast(ChildNodesCount AS VARCHAR(10)) + ')' + '(' + BrowseNodeNameCn + ')' FROM (SELECT tmp.*, cct.BrowsePathByID AS groupBrowsePathByID, Row_number() OVER ( PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel ORDER BY tmp.Leavel ) AS RN FROM Temp AS tmp INNER JOIN Temp AS cct (NOLOCK) ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t WHERE t.RN = 1 AND t.groupBrowsePathByID = tmp.BrowsePathByID ORDER BY t.Leavel DESC FOR XML PATH('')), 1, 1, ''), ',', '>>') FROM Temp AS tmp WHERE tmp.HasChildren = 0; <div> </div>
012+ IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01; CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY ); INSERT INTO #tblbrowsePathByIDTemp01 SELECT item AS BrowsePathByID FROM Charsplitforin( '165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011' , '|'); WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID)) SELECT tmp.BrowsePathByID, tmp.Country, BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' + Cast(ChildNodesCount AS VARCHAR(10)) + ')' + '(' + BrowseNodeNameCn + ')' FROM (SELECT tmp.*, cct.BrowsePathByID AS groupBrowsePathByID, Row_number() OVER ( PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel ORDER BY tmp.Leavel ) AS RN FROM Temp AS tmp INNER JOIN Temp AS cct (NOLOCK) ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t WHERE t.RN = 1 AND t.groupBrowsePathByID = tmp.BrowsePathByID ORDER BY t.Leavel DESC FOR XML PATH('')), 1, 1, ''), ',', '>>') FROM Temp AS tmp WHERE tmp.HasChildren = 0;
013-
013+ <div> </div>
2022-07-01 10:55:30, 由 陈少军 编辑。
修改了 任务描述，区别为：
006- ~~添加索引后还是慢，注释掉Replace(Stuff( 等函数后1秒出结果~~
006+ 添加索引后还是慢，注释掉Replace(Stuff( 等函数，只查询temp表的数据1秒出结果
009- 2.
009+ 2.解决方案
010-
010+ 建议将temp查询的数据转移到应用程序里计算，应用程序里的资源可以保证，数据库计算当资源被其他业务使用过多是，很慢。
011- ~~3. sql~~
011+
012- IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01;CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY );INSERT INTO #tblbrowsePathByIDTemp01SELECT item AS BrowsePathByIDFROM Charsplitforin('165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011', '|');WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID))SELECT tmp.BrowsePathByID, tmp.Country, BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' + Cast(ChildNodesCount AS VARCHAR(10)) + ')' + '(' + BrowseNodeNameCn + ')' FROM (SELECT tmp.*, cct.BrowsePathByID AS groupBrowsePathByID, Row_number() OVER ( PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel ORDER BY tmp.Leavel ) AS RN FROM Temp AS tmp INNER JOIN Temp AS cct (NOLOCK) ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t WHERE t.RN = 1 AND t.groupBrowsePathByID = tmp.BrowsePathByID ORDER BY t.Leavel DESC FOR XML PATH('')), 1, 1, ''), ',', '>>')FROM Temp AS tmpWHERE tmp.HasChildren = 0;
012+ 3. sql
013-
013+ IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01;CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY );INSERT INTO #tblbrowsePathByIDTemp01SELECT item AS BrowsePathByIDFROM Charsplitforin('165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011', '|');WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID))select * from Temp AS tmp; -- 建议将返回的Temp 数据到应用程序里计算--SELECT tmp.BrowsePathByID,-- tmp.Country,-- BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '('-- + Cast(ChildNodesCount AS VARCHAR(10)) + ')'-- + '(' + BrowseNodeNameCn + ')'-- FROM (SELECT tmp.*,-- cct.BrowsePathByID AS groupBrowsePathByID,-- Row_number()-- OVER (-- PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel-- ORDER BY tmp.Leavel ) AS RN-- FROM Temp AS tmp-- INNER JOIN Temp AS cct (NOLOCK)-- ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t-- WHERE t.RN = 1-- AND t.groupBrowsePathByID = tmp.BrowsePathByID-- ORDER BY t.Leavel DESC-- FOR XML PATH('')), 1, 1, ''), ',', '>>')--FROM Temp AS tmp--WHERE tmp.HasChildren = 0;4.优化后的效果验证在次跟踪记录
014-

006- ~~ 添加索引后还是慢，注释掉Replace(Stuff( 等函数后1秒出结果~~
006+ 添加索引后还是慢，注释掉Replace(Stuff( 等函数，只查询temp表的数据1秒出结果
009- ~~2.~~
009+ 2.解决方案
010- ~~ ~~
010+ 建议将temp查询的数据转移到应用程序里计算，应用程序里的资源可以保证，数据库计算当资源被其他业务使用过多是，很慢。
011- ~~3. sql~~
011+ 
012- IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01; CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY ); INSERT INTO #tblbrowsePathByIDTemp01 SELECT item AS BrowsePathByID FROM Charsplitforin( '165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011' , '|'); WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID)) SELECT tmp.BrowsePathByID, tmp.Country, BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' + Cast(ChildNodesCount AS VARCHAR(10)) + ')' + '(' + BrowseNodeNameCn + ')' FROM (SELECT tmp.*, cct.BrowsePathByID AS groupBrowsePathByID, Row_number() OVER ( PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel ORDER BY tmp.Leavel ) AS RN FROM Temp AS tmp INNER JOIN Temp AS cct (NOLOCK) ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t WHERE t.RN = 1 AND t.groupBrowsePathByID = tmp.BrowsePathByID ORDER BY t.Leavel DESC FOR XML PATH('')), 1, 1, ''), ',', '>>') FROM Temp AS tmp WHERE tmp.HasChildren = 0;
012+ 3. sql
013- ~~<div> </div>~~
013+ IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = Object_id(N'tempdb..#tblbrowsePathByIDTemp01') AND type = 'U') DROP TABLE #tblbrowsePathByIDTemp01; CREATE TABLE #tblbrowsePathByIDTemp01 ( BrowsePathByID VARCHAR(400) PRIMARY KEY ); INSERT INTO #tblbrowsePathByIDTemp01 SELECT item AS BrowsePathByID FROM Charsplitforin( '165793011,165795011,166027011,2491218011|1055398,1063498,284507,510136,9768326011,9833720011|1055398,1063498,284507,510136,9768326011,3744131|16310091,16310161,256409011,19568045011,393263011|2972638011,3238155011,3610851,3480694011,11117584011|228013,468240,3754161,680350011,3754321,6808092011|1064954,1084128,1069242,490540011,1069388,490704011|1055398,1063498,284507,13162311,289728,17867731011,2566771011|1055398,1063498,284507,13162311,367165011,17913622011,367107011,9298947011|1055398,1063498,284507,1063916,3742001|2972638011,3238155011,3610851,3480694011,3742401|7141123011,7141124011,7147440011,1040660,9522931011,14333511,2364767011,2364772011|3760911,11055981,11062741,11062781,23627246011|1055398,1063498,284507,289754,3737191,2469549011|2617941011,2617942011,12896081,12896121|1055398,1063498,284507,13162311,9298963011,9298966011,9302388011|1055398,1063498,284507,13162311,13217501,17867735011,13218391|3760901,3760931,3775161,8624314011,3776631,8626645011,8626659011|2619533011,2619534011,2975221011,2975234011,2975235011|1055398,1063498,1063278,14087351,14087331|2619533011,2619534011,2975241011,2975302011|228013,468240,511228,2380871011,511246|16310091,16310161,317971011,3310326011,3310331011|11091801,11965861,11970241,11970681,8882495011,4972150011|1055398,1063498,284507,289754,760384,289737,678499011|2617941011,2617942011,12896081,8090808011,8090810011|1064954,1084128,1069242,1069514,1069534,490602011|3760911,11055981,17242866011,11063461,11063611|1055398,1063498,284507,289754,16439831,289761|1055398,1063498,284507,13162311,367165011,9298959011,13217521|3375251,3375301,706814011,3400371,3401301,3401371,21222469011|228013,468240,495224,495236,553784,14193171|1055398,1063498,284507,13162311,13218891,367312011,19190625011|1055398,1063498,284507,510136,9768326011,12560614011|3760911,11055981,11057241,11057971,3784401|165796011,165797011,239226011,239229011|1055398,1063498,3610841,2423187011,695481011,11601913011|3760911,11055981,11057241,10676449011,7706130011,234949011|7141123011,7141124011,7147440011,2474936011,2475896011|1055398,1063498,284507,915194,3737031|228013,468240,3754161,680340011,6810575011,3754181,3754691|1055398,1063498,284507,289851,289860|2619533011,2619534011,2975312011,2975351011,17592601011,17602455011|1055398,1063498,1063278,3745451|7141123011,7141124011,7147440011,7192394011,7454927011,7454934011|7141123011,7141124011,7147440011,2474936011,2474945011|1055398,1063498,1063278,3735061,3735081|3760901,3760931,15342811,15342841,15342991,15750751|3375251,3375301,706813011,13364359011,13364382011,3294117011,3307775011|228013,468240,511228,2380871011,511260|1055398,1063498,284507,289814,289833|165793011,165795011,166164011,166177011|16310101,16310211,2255571011,2255577011|3760911,11055981,11062741,11062751,11062781|15684181,15690151,15706941,15706951,15706961|1055398,1063498,1063278,3735061,3736021|3760911,11055981,3778591,13269990011,3779191,3779241|3760911,11055981,11057241,11057971,3784391|1055398,1063498,1063278,3734741,13980701,3734771|1064954,1084128,1069242,1068972,1069694,1069696|1055398,1063498,284507,289814,19343857011,10762650011,289832|165793011,165795011,166118011,3245220011,166145011|16310091,16310161,8297370011,8297374011,13052841|7141123011,7141124011,7147440011,679337011,679425011,10509650011|1055398,1063498,284507,13162311,13217501,16861787011,13217791|228013,468240,511228,511388,511396|2617941011,2617942011,378733011,262607011|15684181,15690151,15706941,15708851,15708871|2619533011,2619534011,2975520011,2975535011,17602467011|1055398,1063498,284507,13162311,13218891,367259011,367305011,367300011|2972638011,3238155011,3610851,23539045011,14252911|1055398,1063498,284507,289754,3737191,289804|1055398,1063498,1063278,1063282,3734391,3734481|1055398,1063498,1063278,3735061,15363431|1055398,1063498,284507,289754,3737191,13840241|7141123011,7141124011,7586165011,14194713011,14194714011,14194715011|2619533011,2619534011,2975446011,2975469011,2975472011|3760901,3760931,10787321,16025511,16023581|1055398,1063498,284507,289754,3737191,300196|165793011,165795011,166092011,166093011|165796011,165797011,17720255011,17720257011,166740011,322266011|1055398,1063498,284507,289814,3737201|3760901,3760931,3760941,8627102011,8627104011|1055398,1063498,901590,14530987011|2619533011,2619534011,2975241011,2975243011,17602459011,2975248011|2619533011,2619534011,2975241011,2975303011,2975305011|1055398,1063498,3736081,381142011|3375251,3375301,706814011,3403201,3403211,3403251|228013,468240,495224,5486429011,3736651|228013,468240,495266,6497674011,495326|1055398,1063498,284507,13162311,13218891,367312011,367314011|1055398,1063498,284507,289754,16439841,289771|165793011,165795011,166027011,17238448011,23564877011|2619533011,2619534011,2975312011,2975413011,2975415011|2617941011,2617942011,378733011,262715011,262716011|1055398,1063498,284507,510136,9768326011,695504011|1055398,1063498,1063236,1063238,3731931,85963011|16310101,16310211,16310221,6492272011,6492274011,10844426011|1055398,1063498,1063278,510236,3735981|3760901,3760931,15342811,15342841,15343011,15757581|1055398,1063498,284507,289754,760384,289737,678498011|228013,468240,228899,2445485011|16310091,16310161,317970011,7301207011,318038011,318043011|1055398,1063498,284507,289814,289823|7141123011,7141124011,7147440011,1040660,9522931011,14333511,1044964,1045016|2619533011,2619534011,2975241011,2975251011,3024137011|1055398,1063498,3610841,2423187011,3744481,16353561|1055398,1063498,3610841,2423187011,3744481,16353511|2619533011,2619534011,2975312011,2975313011,2975321011|3375251,3375301,3394801,3395091|1055398,1063498,3610841,2423187011,3743931|1055398,1063498,1063278,1063302,3736141,3736151|7141123011,7141124011,7147441011,3887881,3888121|1055398,1063498,284507,510136,9768326011,5264732011|2619533011,2619534011,2975446011,2975448011,2975450011|2617941011,2617942011,2237329011,12897241,262617011|2619533011,2619534011,2975312011,2975313011,3024175011|1055398,1063498,284507,13162311,367165011,23593202011,367201011,678547011|2972638011,3238155011,553788,553802,14008351|2617941011,2617942011,12899091,12899361,262709011|1055398,1063498,284507,510136,23691552011,3743971,3744101|1055398,1063498,284507,510136,3743971,13750001|2972638011,3238155011,3610851,553958,553962,3480704011|3375251,3375301,706814011,3400371,3401541|2619533011,2619534011,2975312011,2975434011,2975436011|1055398,1063498,13679381,13679421|3375251,3375301,706814011,3403201,2371051011,2419332011,2420102011|1055398,1063498,284507,289754,3737191,3737151|1055398,1063498,284507,289754,16439891,3737161|1055398,1063498,284507,289814,19343857011,10762650011,289831|3760901,3760931,3775161,3775541,8619098011,5006371011|1055398,1063498,284507,13162311,367165011,16188243011|2335752011,2335753011,2407755011,21209100011,11139610011|228013,468240,495224,3736551,5486424011|2619533011,2619534011,2975241011,2975243011,3024130011|1055398,1063498,1063278,1063282,3734591|1055398,1063498,1063236,1063238,3731931,85961011|16310101,16310211,16322721,6524481011,6524482011|1055398,1063498,1063278,3735061,3735181,21238288011|1055398,1063498,3610841,3744341,3744371|1055398,1063498,1063278,1063282,3734591,3734671|2617941011,2617942011,12896081,12896161|2619533011,2619534011,2975241011,2975259011,17592594011,2975261011|2619533011,2619534011,2975520011,2975523011|1055398,1063498,284507,13162311,13217501,367125011,367142011|2972638011,3238155011,3610851,3480694011,553798|2617941011,2617942011,378733011,262714011,8090892011|1055398,1063498,284507,13162311,367165011,367220011,367228011|1055398,1063498,284507,1063916,3741971,13468731|1055398,1063498,284507,13162311,17913622011,367147011,9298958011|16310101,16310211,18787303011,16310221,6492272011,10844428011|3760911,11055981,11062741,11063411|2619533011,2619534011,2975312011,2975420011,2975425011,3024230011|2617941011,2617942011,12899091,262690011,12899431,2237330011|2619533011,2619534011,2975312011,2975405011,2975411011,3052413011|7141123011,7141124011,7586146011,9616098011,3743851,16350291|165793011,165795011,166420011,166431011,1234183011|1055398,1063498,1063278,3736091|1055398,1063498,284507,289754,16439851,289783|2972638011,3238155011,3610851,553958,553966|3375251,3375301,706814011,11051400011,10184604011|1055398,1063498,1063278,14087351,17833726011|228013,468240,328182011,551238,553174,553176|2619533011,2619534011,2975446011,2975448011,2975453011|1055398,1063498,284507,915194,3118170011,3737181,10179245011|2619533011,2619534011,2975221011,2975240011|1055398,1063498,3610841,2422453011,3744051|1055398,1063498,284507,13162311,367165011,17913622011,367147011,367155011|1055398,1063498,1063278,1063282,3734311,3734341|1055398,1063498,1063278,3736371,3736411|1055398,1063498,1063278,10133745011,3734721|1055398,1063498,1063278,3734741,13980701,3734831|3760911,11055981,11062741,13861652011,13861662011|1055398,1063498,284507,289668,289727,2231402011|2972638011,3238155011,3610851,14106461,3480674011|3375251,3375301,10971181011,706813011,3222111011,6152953011|1064954,1084128,1069242,1069784,12901051,490674011|2619533011,2619534011,2975520011,2975535011,2975538011|1055398,1063498,284507,289814|2972638011,3238155011,553824,13749551,13981291|165793011,165795011,276729011,276799011|2972638011,3238155011,3610851,3480694011,3480695011|1055398,1063498,1063278,3735061,1095304|3760901,3760931,3760941,3779501,3779531|16310091,16310161,317970011,7301207011,2616713011,2620830011|2619533011,2619534011,2975241011,2975259011,2975262011|16310091,16310161,317970011,318031011,2618631011,393414011|3375251,3375301,10971181011,706810011,3415011,3415021,3415141|1055398,1063498,1063278,21557027011,1063286,3735611|228013,468240,495224,3736531,5486435011|165793011,165795011,166269011,166326011,166327011|3375251,3375301,10971181011,706809011,3412421,5680890011,13447181|1055398,1063498,1063278,3735061,3295676011,3735111|2619533011,2619534011,2975520011,2975540011|1055398,1063498,284507,289754,760358,3737071|2617941011,2617942011,378733011,262710011,262712011|2972638011,3238155011,553760,13299251,12759051|172282,493964,10048700011,7939901011|1055398,1063498,284507,1267449011,9425880011,9425898011|16310101,16310211,16322461,6558922011|1055398,1063498,284507,289754,289789,1254736011|2972638011,3238155011,553788,553632,4532514011,553634|1055398,1063498,284507,13162311,367165011,367220011,367229011|228013,468240,495224,5486429011,3736721|165793011,165795011,1266203011|2619533011,2619534011,2975312011,2975313011,3024171011|3760911,11055981,11062741,13861652011,18065340011,15197040011|1055398,1063498,284507,289754,289785,289787,678508011|1055398,1063498,284507,979832011,979833011,979837011|2619533011,2619534011,2975446011,2975448011,2975452011|1064954,1084128,1069242,1069784,1069786,6156509011,705370011|1055398,1063498,284507,289913,289929,289931|228013,468240,495224,3736561,10727221|228013,468240,495224,5486428011,3736681|1055398,1063498,1063278,1063282,3734591,3734611|3760911,11055981,11057241,11057971,23623509011,3784401|2619533011,2619534011,2975520011,2975529011,2975530011|3760901,3760931,3775161,8624314011,3776241,3776271|1055398,1063498,284507,13162311,13217501,16861787011,13218541|2619525011,2619526011,3741181,3741201,2232357011|2619533011,2619534011,2975481011,3407521,3085138011|172282,493964,281407,172532,172540,597260|1064954,1084128,1069242,1069454,9887000011,1069456|1055398,1063498,284507,289754,16439891,16439921|7141123011,7141124011,7147441011,3887881,9539904011|3375251,3375301,706814011,3403201,3405541,3405581|3760911,11055981,10079992011|228013,468240,328182011,13400691,553578|165793011,165795011,166057011,166064011,166070011|1055398,1063498,1063278,3735061,3735191,3736031|7141123011,7141124011,7147445011,12035955011,9103696011,9056921011,9056958011,9056959011|2619533011,2619534011,2975446011,2975469011,2975475011|1064954,1084128,1069242,490540011,490718011|15684181,15690151,15857501,15735121|1055398,1063498,284507,289668,2231404011' , '|'); WITH Temp AS (SELECT *, 0 AS Leavel FROM [E_Amazon_BrowseTree_Node_V2] WITH (NOLOCK) WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = E_Amazon_BrowseTree_Node_V2.BrowsePathByID) UNION ALL SELECT a.*, tmp.Leavel + 1 FROM [E_Amazon_BrowseTree_Node_V2] a WITH (NOLOCK) INNER JOIN Temp tmp ON a.BrowsePathByID = tmp.ParentBrowsePathByID AND a.HasChildren = 1 AND a.ChildNodesCount > 0 AND a.Country = tmp.Country WHERE EXISTS (SELECT 1 FROM #tblbrowsePathByIDTemp01 b WITH (NOLOCK) WHERE b.BrowsePathByID = a.BrowsePathByID)) select * from Temp AS tmp; -- 建议将返回的Temp 数据到应用程序里计算 --SELECT tmp.BrowsePathByID, -- tmp.Country, -- BrowseNodeName = Replace(Stuff((SELECT ',' + BrowseNodeName + '(' -- + Cast(ChildNodesCount AS VARCHAR(10)) + ')' -- + '(' + BrowseNodeNameCn + ')' -- FROM (SELECT tmp.*, -- cct.BrowsePathByID AS groupBrowsePathByID, -- Row_number() -- OVER ( -- PARTITION BY tmp.BrowseNodeName, tmp.ChildNodesCount, tmp.BrowseNodeNameCn, tmp.Leavel -- ORDER BY tmp.Leavel ) AS RN -- FROM Temp AS tmp -- INNER JOIN Temp AS cct (NOLOCK) -- ON cct.BrowsePathByID LIKE '%' + tmp.BrowsePathByID + '%') t -- WHERE t.RN = 1 -- AND t.groupBrowsePathByID = tmp.BrowsePathByID -- ORDER BY t.Leavel DESC -- FOR XML PATH('')), 1, 1, ''), ',', '>>') --FROM Temp AS tmp --WHERE tmp.HasChildren = 0; 4.优化后的效果验证在次跟踪记录
014- ~~ ~~
2022-07-01 10:56:26, 由 陈少军 指派给 温林生。
修改了 指派给 ，旧值为 "chenshaojun"，新值为 "wenlinsheng"。
修改了 预计剩余，旧值为 "0"，新值为 "2"。

参考解决方案优化
2022-07-01 17:18:15, 由 温林生 完成。
修改了 总计消耗，旧值为 "0"，新值为 "1"。
修改了 实际完成，旧值为 ""，新值为 "2022-07-01 17:17:34"。
修改了 任务状态，旧值为 "wait"，新值为 "done"。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-07-01 17:18:15"。
修改了 预计剩余，旧值为 "2"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "wenlinsheng"。

这个是我在共享库跑数据给用户用的，一次性的东西
2022-07-01 17:18:29, 由 温林生 关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "wenlinsheng"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "wenlinsheng"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-07-01 17:18:28"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	系统管理部 - 系统服务优化
所属模块	/
相关需求	暂无
指派给	Closed 于 2022-07-01 17:18:28
任务类型	后端
任务标签	初始
开发组
任务状态	已关闭
进度	100%
优先级	P2
关键词
抄送给

由谁创建	陈少军于 2022-07-01 09:21:08
由谁完成	温林生于 2022-07-01 17:17:34
由谁取消	暂无
由谁关闭	温林生于 2022-07-01 17:18:28
关闭原因	已完成
最后编辑	温林生于 2022-07-01 17:18:28

团队	最初预计	总计消耗	预计剩余

工时信息

最初预计	0工时
总计消耗	1工时
预计剩余	0工时
预计开始	2022-06-30
实际开始	2022-07-01
截止日期	2022-07-07