SELECT DISTINCT
ISNULL(A.kaicode, 0) AS kaicode,
ISNULL(A.dennumb, 0) AS dennumb,
CONVERT(char(7), A.denymd, 120) AS denymdx,
ISNULL(A.ukecode, 0) AS autcode,
ISNULL(A.ukecode, 0) AS ukecode,
ISNULL(A.ukename, '') AS ukename,
CAST(CONVERT(char(10), A.denymd, 120) AS datetime) AS denymd,
ISNULL(c.kmkkubn, 0) AS kmkkubn,
ISNULL(A.gyou, 0) AS gyou,
ISNULL(A.kmkcode, 0) AS kmkcode,
ISNULL(A.hojcode, 0) AS hojcode,
ISNULL(A.bumcode2, 0) AS bumcode2,
ISNULL(A.kmkcode2, 0) AS kmkcode2,
ISNULL(A.hojcode2, 0) AS hojcode2,
ISNULL(A.tekname, '') AS tekname,
ISNULL(B.kainame, '') AS kainame,
B.kessans,
B.kessane,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekbhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(bumcode = A.bumcode) AND
(CONVERT(char(7), kymd, 120) = CONVERT(char(7), A.denymd, 120))), 0)
AS zenzanb,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(CONVERT(char(7), kymd,120) = CONVERT(char(7), A.denymd, 120))), 0) AS zenzan, ISNULL(A.bumcode, 0)
AS bumcode,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 1) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money1,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 2) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money2,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode)), '')
AS bumname,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode2)), '')
AS bumname2,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode)), '')
AS hojname,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode2) AND
(hojcode = A.hojcode2)), '')
AS hojname2,
ISNULL((SELECT ISNULL(kmkname, '') FROM kekcommst
WHERE (kmkcode = A.kmkcode)), '')
AS kmkname,
ISNULL ((SELECT ISNULL(kmkname, '') FROM kekcommst
WHERE (kmkcode = A.kmkcode2)), '')
AS kmkname2
FROM keksiwdat AS A LEFT OUTER JOIN
kekkaimst AS B ON A.kaicode = B.kaicode LEFT OUTER JOIN
kekhojmst AS D ON A.hojcode = D.hojcode AND A.hojcode2 = D.hojcode AND A.kaicode = D.kaicode LEFT OUTER JOIN
kekcommst AS E ON A.kmkcode = E.kmkcode AND A.kmkcode2 = E.kmkcode LEFT OUTER JOIN
kekbummst AS F ON A.bumcode = F.bumcode AND A.bumcode2 = F.bumcode AND A.kaicode = F.kaicode LEFT OUTER JOIN
kekauthead AS h ON A.kaicode = h.kaicode LEFT OUTER JOIN
kekcommst AS c ON c.kmkcode = A.kmkcode INNER JOIN
kekkmkmst AS I ON A.kaicode = I.kaicode AND A.kmkcode = I.kmkcode
WHERE (I.hojumu <> 0)这个视图叫做 view_0900
这个很快
然后我又在外边嵌套语句如下
效率很低这是为什么呢?
select * ,
isnull((select sum(isnull(money1,0)) from view_0900 where kaicode=t1.kaicode and bumcode=t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode and hojcode =t1.hojcode),0) as bsummoney1 ,
isnull((select sum(isnull(money2,0)) from view_0900 where kaicode=t1.kaicode and bumcode=t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode and hojcode =t1.hojcode ),0) as bsummoney2,
isnull((select sum(isnull(money1,0)) from view_0900 where kaicode=t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode) ,0) as summoney1 ,
isnull((select sum(isnull(money2,0)) from view_0900 where kaicode=t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode),0) as summoney2
from
view_0900 as t1
ISNULL(A.kaicode, 0) AS kaicode,
ISNULL(A.dennumb, 0) AS dennumb,
CONVERT(char(7), A.denymd, 120) AS denymdx,
ISNULL(A.ukecode, 0) AS autcode,
ISNULL(A.ukecode, 0) AS ukecode,
ISNULL(A.ukename, '') AS ukename,
CAST(CONVERT(char(10), A.denymd, 120) AS datetime) AS denymd,
ISNULL(c.kmkkubn, 0) AS kmkkubn,
ISNULL(A.gyou, 0) AS gyou,
ISNULL(A.kmkcode, 0) AS kmkcode,
ISNULL(A.hojcode, 0) AS hojcode,
ISNULL(A.bumcode2, 0) AS bumcode2,
ISNULL(A.kmkcode2, 0) AS kmkcode2,
ISNULL(A.hojcode2, 0) AS hojcode2,
ISNULL(A.tekname, '') AS tekname,
ISNULL(B.kainame, '') AS kainame,
B.kessans,
B.kessane,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekbhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(bumcode = A.bumcode) AND
(CONVERT(char(7), kymd, 120) = CONVERT(char(7), A.denymd, 120))), 0)
AS zenzanb,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(CONVERT(char(7), kymd,120) = CONVERT(char(7), A.denymd, 120))), 0) AS zenzan, ISNULL(A.bumcode, 0)
AS bumcode,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 1) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money1,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 2) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money2,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode)), '')
AS bumname,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode2)), '')
AS bumname2,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode)), '')
AS hojname,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode2) AND
(hojcode = A.hojcode2)), '')
AS hojname2,
ISNULL((SELECT ISNULL(kmkname, '') FROM kekcommst
WHERE (kmkcode = A.kmkcode)), '')
AS kmkname,
ISNULL ((SELECT ISNULL(kmkname, '') FROM kekcommst
WHERE (kmkcode = A.kmkcode2)), '')
AS kmkname2
FROM keksiwdat AS A LEFT OUTER JOIN
kekkaimst AS B ON A.kaicode = B.kaicode LEFT OUTER JOIN
kekhojmst AS D ON A.hojcode = D.hojcode AND A.hojcode2 = D.hojcode AND A.kaicode = D.kaicode LEFT OUTER JOIN
kekcommst AS E ON A.kmkcode = E.kmkcode AND A.kmkcode2 = E.kmkcode LEFT OUTER JOIN
kekbummst AS F ON A.bumcode = F.bumcode AND A.bumcode2 = F.bumcode AND A.kaicode = F.kaicode LEFT OUTER JOIN
kekauthead AS h ON A.kaicode = h.kaicode LEFT OUTER JOIN
kekcommst AS c ON c.kmkcode = A.kmkcode INNER JOIN
kekkmkmst AS I ON A.kaicode = I.kaicode AND A.kmkcode = I.kmkcode
WHERE (I.hojumu <> 0)这个视图叫做 view_0900
这个很快
然后我又在外边嵌套语句如下
效率很低这是为什么呢?
select * ,
isnull((select sum(isnull(money1,0)) from view_0900 where kaicode=t1.kaicode and bumcode=t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode and hojcode =t1.hojcode),0) as bsummoney1 ,
isnull((select sum(isnull(money2,0)) from view_0900 where kaicode=t1.kaicode and bumcode=t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode and hojcode =t1.hojcode ),0) as bsummoney2,
isnull((select sum(isnull(money1,0)) from view_0900 where kaicode=t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode) ,0) as summoney1 ,
isnull((select sum(isnull(money2,0)) from view_0900 where kaicode=t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode),0) as summoney2
from
view_0900 as t1
from view_0900 as t1
cross apply
(
select
sum(money1) as bsummoney1,
sum(money2) as bsummoney2
from view_0900
where
kaicode = t1.kaicode and
bumcode = t1.bumcode and
denymd between t1.kessans and t1.kessane and
kmkcode = t1.kmkcode and
hojcode = t1.hojcode
) t2
cross apply
(
select
sum(money1) as summoney1,
sum(money2) as summoney2
from view_0900
where
kaicode = t1.kaicode and
denymd between t1.kessans and t1.kessane and
kmkcode = t1.kmkcode
) t3