SELECT MOCTA.TA033 as MOCTATA033,MOCTA.TA001 as MOCTATA001,MOCTA.TA002 as MOCTATA002,MOCTA.TA006 as MOCTATA006,MOCTA.TA007 as MOCTATA007,MOCTA.TA015 as MOCTATA015,MOCTA.TA009 as MOCTATA009,MOCTA.TA017 as MOCTATA017,MOCTA.UDF10 as MOCTAUDF10,MOCTG.TG001 as MOCTGTG001,MOCTG.TG002 as MOCTGTG002,MOCTG.TG003 as MOCTGTG003,MOCTG.TG011 as MOCTGTG011,convert(nvarchar,convert(decimal(18,2),(select (select count(MOCTA.TA006) from MOCTA where TA001+TA002+TA006 in (SELECT MOCTA.TA001+MOCTA.TA002+MOCTA.TA006 FROM MOCTA as MOCTA Left JOIN MOCTG as MOCTG On MOCTA.TA002=MOCTG.TG015 and MOCTA.TA001=MOCTG.TG014 and MOCTA.TA006=MOCTG.TG004 Left JOIN MOCTF as MOCTF On MOCTG.TG001=MOCTF.TF001 AND MOCTG.TG002=MOCTF.TF002 where TA033 ='20190422001') and MOCTA.TA009 >= MOCTA.UDF10 )*1.00 /(select count(MOCTA.TA006) from MOCTA where TA001+TA002+TA006 in (SELECT MOCTA.TA001+MOCTA.TA002+MOCTA.TA006 FROM MOCTA as MOCTA Left JOIN MOCTG as MOCTG On MOCTA.TA002=MOCTG.TG015 and MOCTA.TA001=MOCTG.TG014 and MOCTA.TA006=MOCTG.TG004 Left JOIN MOCTF as MOCTF On MOCTG.TG001=MOCTF.TF001 AND MOCTG.TG002=MOCTF.TF002 where TA033 ='20190422001'))*1.00))*100)+'%' as jishilv FROM MOCTA as MOCTA Left JOIN MOCTG as MOCTG On MOCTA.TA002=MOCTG.TG015 and MOCTA.TA001=MOCTG.TG014 and MOCTA.TA006=MOCTG.TG004 Left JOIN MOCTF as MOCTF On MOCTG.TG001=MOCTF.TF001 AND MOCTG.TG002=MOCTF.TF002 where TA033 ='20190422001’
请问能将嵌套查询里面的TA033=‘20190422001’跟外面的最后一行的最后一个条件TA033对应起来吗? 我做的是鼎捷ERP的自定义查询报表,发现必须要将红色部分的TA033作为变量另外输入才行,实际上我是要只输入一个查询条件TA033,就可出来结果。
请问能将嵌套查询里面的TA033=‘20190422001’跟外面的最后一行的最后一个条件TA033对应起来吗? 我做的是鼎捷ERP的自定义查询报表,发现必须要将红色部分的TA033作为变量另外输入才行,实际上我是要只输入一个查询条件TA033,就可出来结果。
WITH t AS (SELECT TA.TA033 AS MOCTATA033,
TA.TA001 AS MOCTATA001,
TA.TA002 AS MOCTATA002,
TA.TA006 AS MOCTATA006,
TA.TA007 AS MOCTATA007,
TA.TA015 AS MOCTATA015,
TA.TA009 AS MOCTATA009,
TA.TA017 AS MOCTATA017,
TA.UDF10 AS MOCTAUDF10,
TG.TG001 AS MOCTGTG001,
TG.TG002 AS MOCTGTG002,
TG.TG003 AS MOCTGTG003,
TG.TG011 AS MOCTGTG011,
CASE WHEN TA.TA009 >= TA.UDF10 THEN 1 ELSE 0 END AS Filter1
FROM MOCTA AS TA
LEFT JOIN MOCTG AS TG ON TA.TA002 = TG.TG015
AND TA.TA001 = TG.TG014
AND TA.TA006 = TG.TG004
LEFT JOIN MOCTF AS TF ON TG.TG001 = TF.TF001
AND TG.TG002 = TF.TF002
WHERE TA.TA033 = '20190422001')
SELECT *,
CAST(CAST((SELECT COUNT(MOCTA.TA006)
FROM MOCTA
WHERE TA001 + TA002 + TA006 IN(SELECT MOCTATA001
+ MOCTATA002
+ MOCTATA006 FROM t)
AND t.Filter1 = 1) * 1.00
/ (SELECT COUNT(MOCTA.TA006)
FROM MOCTA
WHERE TA001 + TA002 + TA006 IN(SELECT MOCTATA001
+ MOCTATA002
+ MOCTATA006 FROM t)) AS DECIMAL(18, 2)) AS VARCHAR(20))
+ '%'
FROM t;