SELECT A.ID As ID1,E.Name KJWL,F.ZWMC SJRGJ,B.*,C.Number,C.NAME,
(CASE WHEN B.PPCC <> 'CC' THEN
(CASE WHEN IsNull(KHYZHJE,0)<>0 THEN (IsNull(KHYZHJE,0)-IsNull(ZWLSFJE,0))/IsNull(KHYZHJE,0) ELSE 0 END)
ELSE
(CASE WHEN IsNull(ZWLSFJE,0)<>0 THEN (IsNull(ZWLSFJE,0)-IsNull(KHYZHJE,0))/IsNull(ZWLSFJE,0) ELSE 0 END)
END) AS KZMLR,
(IsNull(KHYZHJE,0)-IsNull(ZWLSFJE,0)) AS KZML,
(IsNull(KHYZHJE,0)-IsNull(WLSFJE,0)) AS KFML,
(CASE WHEN B.PPCC <> 'PP' THEN
(IsNull(WLSFJE,0)-IsNull(ZWLSFJE,0))
Else
(IsNull(ZWLSFJE,0)-IsNull(WLSFJE,0)) End) AS KJML,
(CASE WHEN B.PPCC <> 'CC' THEN
(CASE WHEN IsNull(KHYZHJE,0)<>0 THEN (IsNull(KHYZHJE,0)-IsNull(WLSFJE,0))/IsNull(KHYZHJE,0) ELSE 0 END)
ELSE
(CASE WHEN IsNull(WLSFJE,0)<>0 THEN (IsNull(WLSFJE,0)-IsNull(KHYZHJE,0))/IsNull(WLSFJE,0) ELSE 0 END)
END) AS KFMLR
FROM W_KHYD A Inner JOIN w_TJB B ON A.InnerID=B.InnerID
Left JOIN b_KHZLZB C ON A.KHBM=C.InnerID --关键是这样.如果改成Inner Join时间会超过50秒
LEFT JOIN B_KJWL E ON A.KJWL=E.InnerID
LEFT JOIN B_GJB F ON A.SJRGJ=F.InnerID
WHERE
A.YLSJ>(Case WHEN CONVERT(int,IsNull(C.JZR,'26'))>31 THEN '2011-01-31 23:59:59'
WHEN CONVERT(int,IsNull(C.JZR,'26'))<1 THEN '2011-01-26 23:59:59'
ELSE DateAdd(Day,CONVERT(int,IsNull(C.JZR,'26'))-1,'2011-01-01 23:59:59') END)
AND A.YLSJ<=(Case WHEN CONVERT(int,IsNull(C.JZR,'26'))>28 THEN '2011-02-28 23:59:59'
WHEN CONVERT(int,IsNull(C.JZR,'26'))<1 THEN '2011-02-26 23:59:59'
mark一下..不知道是什么原因.Inner join 反倒没有Left Join 快