需求:使用一條sql陳述句查詢某欄位的資料總和，并列出表中的所有記錄
問題：執行的時候半天沒反應，感覺死回圈了一樣。該表進行過濾后的資料也就在50條的樣子，怎么會半天查不出呢，也不報錯。
請問是哪里出了問題呢，各位大神幫忙看看呢。
sql陳述句:
with szx as
(
select fID,BMonFM,path=cast(fID as varchar(8000)),total=cast(BMonFM as float) from PFunMaster
where XAudit='已審核' and XMark = 'PAYBOR' and
ProFidFM = '3EDA82C0FCD94082A81649EACBC146D4' and EntFidFM like '%3E85069351664628B0315E962DF01F92%' and StatFM !='完全核銷'
union all
select b.fID,b.BMonFM,a.path+'-'+rtrim(b.fID),a.total+b.BMonFM
from szx a join PFunMaster b on a.fID<b.fID and a.total<90000
)
select fid,BMonFM from PFunMaster,(select top 1 path from szx where total=90000 order by newid()) a
where charindex('-'+rtrim(fID)+'-','-'+path+'-')>0
uj5u.com熱心網友回復：
我自己模擬了一個相似的表資料：if object_id('[tb]') is not null drop table [tb]
go
create table [tb](ID varchar(100),[NUM] decimal(12,2))
insert [tb]
select '04CDA1A1EBB449A8A85164247CBC56CE',60000.00 union all
select '133BF79053FC46388D29FC582AA28126',30000.00 union all
select '1D357A18B62C4D01BB78334342412D03',20000.00 union all
select '24F9E6A3E3C449F5841C261C95E91044',50000.00 union all
select '2DEF71FF78BD408F8118B617EE8ABD3F',40000.00 union all
select '37C5CE0A88D742838337F51C4496ACA5',15000.00 union all
select '3EC5A731594B4114A49FCAA13C377816',1948375.03 union all
select '41D9AF4603274B1CB24C517D809B2116',100000.00 union all
select '48DEBC1463C74780809B3BB4A5139964',46130.00 union all
select '49E104A090694179ABFE9F9E98846113',70000.00 union all
select '4F9F72BB3A344102849EBA40007CF881',54750.00 union all
select '59EB864399B54D89A04C28EE8AA6C28B',-35900.00 union all
select '5DFD6A763C8C48E4968CF23460F2FB2C',16500.00 union all
select '5EB9FF5F52F94831A96D9EF92BD091F5',102750.00 union all
select '5F388CE8B6E64F7DBC21CAC119458168',10000.00 union all
select '683C82D967574DEEB05622D2C83E4E81',277142.00 union all
select '7291A40B0B12496BB79CD6098C81FB49',305905.00 union all
select '7610943B575245E4AFD1205D52928DEB',70000.00 union all
select '797F3B5C24624DAF8149B67C5A2C2D14',120000.00 union all
select '7B74FC974A70472C85AB15DE7027A729',37000.00 union all
select '7F019731546243A29DE063D31B95E28A',150000.00 union all
select '80D910A621684321B6DCF200A0FBBC73',150000.00 union all
select '85A6CF986123492EB968468ED83617E2',-20700.00
go
with szx as
(
select ID,NUM,path=cast(id as varchar(8000)),total=cast(num as float)from tb
union all
select b.ID,b.num,a.path+'-'+rtrim(b.ID),a.total+b.num
from szx a join tb b on a.id<b.id and a.total<90000
)
select id,num from tb,(select top 1 path from szx where total=90000 order by newid()) a
where charindex('-'+rtrim(id)+'-','-'+path+'-')>0
這個就沒問題，這是為什么呢？
uj5u.com熱心網友回復：
PFunMaster，這張表有多少資料呢uj5u.com熱心網友回復：
寫得啰嗦，資料量和計算量都增加許多，例如JOIN PFunMaster b ON a.fID < b.fID AND a.total < 90000這句，很多資料會重復，比如：
WITH t1 AS(SELECT 1 AS N UNION ALL SELECT 2 AS N UNION ALL SELECT 3 AS N UNION ALL SELECT 4 AS N),
cet AS (SELECT N FROM t1 WHERE t1.N=1 UNION ALL SELECT a.N FROM t1 a JOIN cet b ON a.N>b.N)
SELECT * FROM cet
結果是：
N
-----------
1
2
3
4
4
3
4
4
(8 行受影響)
uj5u.com熱心網友回復：
看代碼語法沒問題看看
select b.fID,b.BMonFM,a.path+'-'+rtrim(b.fID),a.total+b.BMonFM
from szx a join PFunMaster b on a.fID<b.fID and a.total<90000
單獨運行怎么樣
或者簡化某些地方，方便查找問題
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/113371.html
標籤：疑難問題
上一篇：新手，求大神幫忙
下一篇：SQL server