这样写sql语句会有问题吗？

Question

SELECT L.Account_Receivable_List_No,L.Charge_Date,L.Re,Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') WHEN 1 THEN 
(RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,DESex=CASE WHEN S.Sex=0 THEN '男' ELSE '女' 
END,E.Authorize_No,E.Charge_Standard,R.Residence_Item_Name,L.Charge_Item_Code,I.Charge_Item_Name,L.Accounts,S.Advice_Note,S.Name,S.Sex,C.Class_Code,C.Class_Name,L.Account_Receivable_Amount,L.Derate_Amount,L.Abate_Amount,L.Refund_Amount,L.Fact_Amount,(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount) 
AS Arrearage_Amount FROM Account_Receivable_List L,Charge_Item I,Charge_Icence E,Residence_Item R,Student S,Class C WHERE (L.Charge_Icence_No IS NOT NULL AND L.Charge_Icence_No<>0) AND 
(L.Residence_Item_No IS NOT NULL AND L.Residence_Item_No<>0) AND L.Charge_Icence_No=E.Charge_Icence_No AND L.Residence_Item_No=R.Residence_Item_No AND SUBSTRING(L.Charge_Date,1,4)='2006' AND 
L.Charge_Item_Code=I.Charge_Item_Code AND L.Accounts=S.Accounts AND S.Class_No=C.Class_No AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 AND 
S.Accounts LIKE '2030304110%' UNION ALL SELECT L.Account_Receivable_List_No,L.Charge_Date,L.Re,Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) WHEN 0 THEN 
(RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,DESex=CASE WHEN S.Sex=0 THEN 
'男' ELSE '女' END,E.Authorize_No,E.Charge_Standard,' ' AS 
Residence_Item_Name,L.Charge_Item_Code,I.Charge_Item_Name,L.Accounts,S.Advice_Note,S.Name,S.Sex,C.Class_Code,C.Class_Name,L.Account_Receivable_Amount,L.Derate_Amount,L.Abate_Amount,L.Refund_Amount,L.Fact_Amount,(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount) 
AS Arrearage_Amount FROM Account_Receivable_List L,Charge_Item I,Charge_Icence E,Student S,Class C WHERE (L.Charge_Icence_No IS NOT NULL AND L.Charge_Icence_No<>0) AND (L.Residence_Item_No IS 
NULL OR L.Residence_Item_No=0) AND L.Charge_Icence_No=E.Charge_Icence_No AND SUBSTRING(L.Charge_Date,1,4)='2006' AND L.Charge_Item_Code=I.Charge_Item_Code AND L.Accounts=S.Accounts AND 
S.Class_No=C.Class_No AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 AND S.Accounts LIKE '2030304110%' UNION ALL SELECT 
L.Account_Receivable_List_No,L.Charge_Date,L.Re,Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') WHEN 1 THEN 
(RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,DESex=CASE WHEN S.Sex=0 THEN '男' ELSE '女' END,' ' AS Authorize_No,0 AS 
Charge_Standard,R.Residence_Item_Name,L.Charge_Item_Code,I.Charge_Item_Name,L.Accounts,S.Advice_Note,S.Name,S.Sex,C.Class_Code,C.Class_Name,L.Account_Receivable_Amount,L.Derate_Amount,L.Abate_Amount,L.Refund_Amount,L.Fact_Amount,(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount) 
AS Arrearage_Amount FROM Account_Receivable_List L,Charge_Item I,Residence_Item R,Student S,Class C WHERE (L.Charge_Icence_No IS NULL OR L.Charge_Icence_No=0) AND (L.Residence_Item_No IS NOT 
NULL AND L.Residence_Item_No<>0) AND L.Residence_Item_No=R.Residence_Item_No AND SUBSTRING(L.Charge_Date,1,4)='2006' AND L.Charge_Item_Code=I.Charge_Item_Code AND L.Accounts=S.Accounts AND 
S.Class_No=C.Class_No AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 AND S.Accounts LIKE '2030304110%' UNION ALL SELECT 
L.Account_Receivable_List_No,L.Charge_Date,L.Re,Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') WHEN 1 THEN 
(RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,DESex=CASE WHEN S.Sex=0 THEN '男' ELSE '女' END,' ' AS Authorize_No,0 AS 
Charge_Standard,' ' AS 
Residence_Item_Name,L.Charge_Item_Code,I.Charge_Item_Name,L.Accounts,S.Advice_Note,S.Name,S.Sex,C.Class_Code,C.Class_Name,L.Account_Receivable_Amount,L.Derate_Amount,L.Abate_Amount,L.Refund_Amount,L.Fact_Amount,(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount) 
AS Arrearage_Amount FROM Account_Receivable_List L,Charge_Item I,Student S,Class C WHERE (L.Charge_Icence_No IS NULL OR L.Charge_Icence_No=0) AND (L.Residence_Item_No IS NULL OR 
L.Residence_Item_No=0) AND SUBSTRING(L.Charge_Date,1,4)='2006' AND L.Charge_Item_Code=I.Charge_Item_Code AND L.Accounts=S.Accounts AND S.Class_No=C.Class_No AND 
(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 AND S.Accounts LIKE '2030304110%' ORDER BY C.Class_Code,L.Accounts,L.Charge_Date,L.Charge_Item_Code

Answer 1

楼主的sql 这样写，就不怕把自己也搞死了？我很好奇呀，楼主自己怎么看的？

Answer 2

SELECT L.Account_Receivable_List_No
      ,L.Charge_Date
      ,L.Re
      ,Charge_Date_Str = CASE RTRIM(SUBSTRING(L.Charge_Date ,5 ,2))
                              WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年')
                              WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年上学期')
                              ELSE (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年下学期')
                         END
      ,DESex = CASE 
                    WHEN S.Sex=0 THEN '男'
                    ELSE '女'
               END
      ,E.Authorize_No
      ,E.Charge_Standard
      ,R.Residence_Item_Name
      ,L.Charge_Item_Code
      ,I.Charge_Item_Name
      ,L.Accounts
      ,S.Advice_Note
      ,S.Name
      ,S.Sex
      ,C.Class_Code
      ,C.Class_Name
      ,L.Account_Receivable_Amount
      ,L.Derate_Amount
      ,L.Abate_Amount
      ,L.Refund_Amount
      ,L.Fact_Amount
      ,(
           L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
          -L.Fact_Amount
       ) AS Arrearage_Amount
FROM   Account_Receivable_List L
      ,Charge_Item I
      ,Charge_Icence E
      ,Residence_Item R
      ,Student S
      ,Class C
WHERE  (L.Charge_Icence_No IS NOT NULL AND L.Charge_Icence_No<>0)
       AND (L.Residence_Item_No IS NOT NULL AND L.Residence_Item_No<>0)
       AND L.Charge_Icence_No = E.Charge_Icence_No
       AND L.Residence_Item_No = R.Residence_Item_No
       AND SUBSTRING(L.Charge_Date ,1 ,4) = '2006'
       AND L.Charge_Item_Code = I.Charge_Item_Code
       AND L.Accounts = S.Accounts
       AND S.Class_No = C.Class_No
       AND (
               L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
              -L.Fact_Amount
           )>0
       AND S.Accounts LIKE '2030304110%' 
UNION ALL 
SELECT L.Account_Receivable_List_No
      ,L.Charge_Date
      ,L.Re
      ,Charge_Date_Str = CASE RTRIM(SUBSTRING(L.Charge_Date ,5 ,2))
                              WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年')
                              WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年上学期')
                              ELSE (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年下学期')
                         END
      ,DESex = CASE 
                    WHEN S.Sex=0 THEN '男'
                    ELSE '女'
               END
      ,E.Authorize_No
      ,E.Charge_Standard
      ,' ' AS Residence_Item_Name
      ,L.Charge_Item_Code
      ,I.Charge_Item_Name
      ,L.Accounts
      ,S.Advice_Note
      ,S.Name
      ,S.Sex
      ,C.Class_Code
      ,C.Class_Name
      ,L.Account_Receivable_Amount
      ,L.Derate_Amount
      ,L.Abate_Amount
      ,L.Refund_Amount
      ,L.Fact_Amount
      ,(
           L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
          -L.Fact_Amount
       ) AS Arrearage_Amount
FROM   Account_Receivable_List L
      ,Charge_Item I
      ,Charge_Icence E
      ,Student S
      ,Class C
WHERE  (L.Charge_Icence_No IS NOT NULL AND L.Charge_Icence_No<>0)
       AND (L.Residence_Item_No IS NULL OR L.Residence_Item_No=0)
       AND L.Charge_Icence_No = E.Charge_Icence_No
       AND SUBSTRING(L.Charge_Date ,1 ,4) = '2006'
       AND L.Charge_Item_Code = I.Charge_Item_Code
       AND L.Accounts = S.Accounts
       AND S.Class_No = C.Class_No
       AND (
               L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
              -L.Fact_Amount
           )>0
       AND S.Accounts LIKE '2030304110%' 
UNION ALL 
SELECT L.Account_Receivable_List_No
      ,L.Charge_Date
      ,L.Re
      ,Charge_Date_Str = CASE RTRIM(SUBSTRING(L.Charge_Date ,5 ,2))
                              WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年')
                              WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年上学期')
                              ELSE (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年下学期')
                         END
      ,DESex = CASE 
                    WHEN S.Sex=0 THEN '男'
                    ELSE '女'
               END
      ,' ' AS Authorize_No
      ,0 AS Charge_Standard
      ,R.Residence_Item_Name
      ,L.Charge_Item_Code
      ,I.Charge_Item_Name
      ,L.Accounts
      ,S.Advice_Note
      ,S.Name
      ,S.Sex
      ,C.Class_Code
      ,C.Class_Name
      ,L.Account_Receivable_Amount
      ,L.Derate_Amount
      ,L.Abate_Amount
      ,L.Refund_Amount
      ,L.Fact_Amount
      ,(
           L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
          -L.Fact_Amount
       ) AS Arrearage_Amount
FROM   Account_Receivable_List L
      ,Charge_Item I
      ,Residence_Item R
      ,Student S
      ,Class C
WHERE  (L.Charge_Icence_No IS NULL OR L.Charge_Icence_No=0)
       AND (L.Residence_Item_No IS NOT NULL AND L.Residence_Item_No<>0)
       AND L.Residence_Item_No = R.Residence_Item_No
       AND SUBSTRING(L.Charge_Date ,1 ,4) = '2006'
       AND L.Charge_Item_Code = I.Charge_Item_Code
       AND L.Accounts = S.Accounts
       AND S.Class_No = C.Class_No
       AND (
               L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
              -L.Fact_Amount
           )>0
       AND S.Accounts LIKE '2030304110%' 
UNION ALL 
SELECT L.Account_Receivable_List_No
      ,L.Charge_Date
      ,L.Re
      ,Charge_Date_Str = CASE RTRIM(SUBSTRING(L.Charge_Date ,5 ,2))
                              WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年')
                              WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年上学期')
                              ELSE (RTRIM(SUBSTRING(L.Charge_Date ,1 ,4))+ '年下学期')
                         END
      ,DESex = CASE 
                    WHEN S.Sex=0 THEN '男'
                    ELSE '女'
               END
      ,' ' AS Authorize_No
      ,0 AS Charge_Standard
      ,' ' AS Residence_Item_Name
      ,L.Charge_Item_Code
      ,I.Charge_Item_Name
      ,L.Accounts
      ,S.Advice_Note
      ,S.Name
      ,S.Sex
      ,C.Class_Code
      ,C.Class_Name
      ,L.Account_Receivable_Amount
      ,L.Derate_Amount
      ,L.Abate_Amount
      ,L.Refund_Amount
      ,L.Fact_Amount
      ,(
           L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
          -L.Fact_Amount
       ) AS Arrearage_Amount
FROM   Account_Receivable_List L
      ,Charge_Item I
      ,Student S
      ,Class C
WHERE  (L.Charge_Icence_No IS NULL OR L.Charge_Icence_No=0)
       AND (L.Residence_Item_No IS NULL OR L.Residence_Item_No=0)
       AND SUBSTRING(L.Charge_Date ,1 ,4) = '2006'
       AND L.Charge_Item_Code = I.Charge_Item_Code
       AND L.Accounts = S.Accounts
       AND S.Class_No = C.Class_No
       AND (
               L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount
              -L.Fact_Amount
           )>0
       AND S.Accounts LIKE '2030304110%'
ORDER BY
       C.Class_Code
      ,L.Accounts
      ,L.Charge_Date
      ,L.Charge_Item_Code
整理了一下,语法没问题.

Answer 3

SELECT 
L.Account_Receivable_List_No,
L.Charge_Date,
L.Re,
Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) 
WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') 
WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') 
ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,
DESex=CASE WHEN S.Sex=0 THEN '男' ELSE '女'  END,
E.Authorize_No,
E.Charge_Standard,
R.Residence_Item_Name,
L.Charge_Item_Code,
I.Charge_Item_Name,
L.Accounts,
S.Advice_Note,
S.Name,S.Sex,
C.Class_Code,
C.Class_Name,
L.Account_Receivable_Amount,
L.Derate_Amount,
L.Abate_Amount,
L.Refund_Amount,
L.Fact_Amount,
(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)  AS Arrearage_Amount 
FROM 
Account_Receivable_List L,
Charge_Item I,
Charge_Icence E,
Residence_Item R,
Student S,Class C 
WHERE (L.Charge_Icence_No IS NOT NULL AND L.Charge_Icence_No<>0) AND  
(L.Residence_Item_No IS NOT NULL AND L.Residence_Item_No<>0) 
AND L.Charge_Icence_No=E.Charge_Icence_No 
AND L.Residence_Item_No=R.Residence_Item_No 
AND SUBSTRING(L.Charge_Date,1,4)='2006' 
AND L.Charge_Item_Code=I.Charge_Item_Code 
AND L.Accounts=S.Accounts 
AND S.Class_No=C.Class_No 
AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 
AND S.Accounts LIKE '2030304110%' 
UNION ALL 
SELECT 
L.Account_Receivable_List_No,
L.Charge_Date,
L.Re,
Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) 
WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') 
WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') 
ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,
DESex=CASE WHEN S.Sex=0 THEN  '男' ELSE '女' END,
E.Authorize_No,
E.Charge_Standard,
' ' AS Residence_Item_Name,
L.Charge_Item_Code,
I.Charge_Item_Name,
L.Accounts,
S.Advice_Note,
S.Name,
S.Sex,
C.Class_Code,
C.Class_Name,
L.Account_Receivable_Amount,
L.Derate_Amount,
L.Abate_Amount,
L.Refund_Amount,
L.Fact_Amount,
(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)  AS Arrearage_Amount 
FROM 
Account_Receivable_List L,
Charge_Item I,
Charge_Icence E,
Student S,
Class C 
WHERE (L.Charge_Icence_No IS NOT NULL AND L.Charge_Icence_No<>0) 
AND (L.Residence_Item_No IS  NULL OR L.Residence_Item_No=0) 
AND L.Charge_Icence_No=E.Charge_Icence_No 
AND SUBSTRING(L.Charge_Date,1,4)='2006' 
AND L.Charge_Item_Code=I.Charge_Item_Code 
AND L.Accounts=S.Accounts 
AND S.Class_No=C.Class_No 
AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 
AND S.Accounts LIKE '2030304110%' 
UNION ALL 
SELECT  
L.Account_Receivable_List_No,
L.Charge_Date,
L.Re,Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) 
WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') 
WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') 
ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,
DESex=CASE WHEN S.Sex=0 THEN '男' ELSE '女' END,
' ' AS Authorize_No,
0 AS  Charge_Standard,
R.Residence_Item_Name,
L.Charge_Item_Code,
I.Charge_Item_Name,
L.Accounts,
S.Advice_Note,
S.Name,
S.Sex,
C.Class_Code,
C.Class_Name,
L.Account_Receivable_Amount,
L.Derate_Amount,
L.Abate_Amount,
L.Refund_Amount,
L.Fact_Amount,
(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount) AS Arrearage_Amount 
FROM 
Account_Receivable_List L,
Charge_Item I,
Residence_Item R,
Student S,
Class C 
WHERE (L.Charge_Icence_No IS NULL OR L.Charge_Icence_No=0) 
AND (L.Residence_Item_No IS NOT  NULL 
AND L.Residence_Item_No<>0) 
AND L.Residence_Item_No=R.Residence_Item_No 
AND SUBSTRING(L.Charge_Date,1,4)='2006' 
AND L.Charge_Item_Code=I.Charge_Item_Code 
AND L.Accounts=S.Accounts 
AND S.Class_No=C.Class_No 
AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 
AND S.Accounts LIKE '2030304110%' 
UNION ALL 
SELECT  
L.Account_Receivable_List_No,
L.Charge_Date,
L.Re,
Charge_Date_Str=CASE RTRIM(SUBSTRING(L.Charge_Date,5,2)) 
WHEN 0 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年') 
WHEN 1 THEN (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年上学期') 
ELSE (RTRIM(SUBSTRING(L.Charge_Date,1,4))+'年下学期') END,
DESex=CASE WHEN S.Sex=0 THEN '男' ELSE '女' END,
' ' AS Authorize_No,
0 AS  Charge_Standard,
' ' AS  Residence_Item_Name,
L.Charge_Item_Code,
I.Charge_Item_Name,
L.Accounts,
S.Advice_Note,
S.Name,
S.Sex,
C.Class_Code,
C.Class_Name,
L.Account_Receivable_Amount,
L.Derate_Amount,
L.Abate_Amount,
L.Refund_Amount,
L.Fact_Amount,
(L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount) AS Arrearage_Amount 
FROM 
Account_Receivable_List L,
Charge_Item I,
Student S,
Class C 
WHERE (L.Charge_Icence_No IS NULL OR L.Charge_Icence_No=0) 
AND (L.Residence_Item_No IS NULL OR  L.Residence_Item_No=0) 
AND SUBSTRING(L.Charge_Date,1,4)='2006' 
AND L.Charge_Item_Code=I.Charge_Item_Code 
AND L.Accounts=S.Accounts 
AND S.Class_No=C.Class_No 
AND (L.Account_Receivable_Amount-L.Derate_Amount-L.Abate_Amount-L.Refund_Amount-L.Fact_Amount)>0 
AND S.Accounts LIKE '2030304110%' 
ORDER BY C.Class_Code,L.Accounts,L.Charge_Date,L.Charge_Item_Code
看了半天,没看明白.