日期:2014-05-18  浏览次数:20435 次

我的视图效率很高,但是在它的基础上加上统计函数就慢了很多。
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