啊 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 ,Specialty P 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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19)
 
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 ,Specialty P
 
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN 
(19) 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 ,Specialty P 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 C.Specialty_No=P.Specialty_No 
AND P.Department_No IN (19) 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 ,Specialty P 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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) ORDER BY 
C.Class_Code,L.Accounts,L.Charge_Date,L.Charge_Item_Code 
我想用SUM()来统计这些字段的值： Account_Receivable_Amount（int），Fact_Amount （int），如何操作啊？！

Answer 1

这样的sql看都看不下去，怎么写出来的啊

Answer 2

这两个字段是int型的啊，分别求它们的和值。好像就加2个sum()。
另外
看这sql语句稀疏有序的结构，逻辑不是很复杂啊。

Answer 3

积极性大受受打击，排好第一段，发现后面还有卅UNION ALLSELECT
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 ,
Specialty P 
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
C.Specialty_No=P.Specialty_No AND
P.Department_No IN (19)

Answer 4

那句sql经过运行，得出如下结果：
上pp看能看到不？

Answer 5

你自己排版一下吧，好好理一下自己的思路，最起码你自己清楚需要什么样的数据，别人根本不知道，这么多表，这么多UNION。“我想用SUM()来统计这些字段的值：ccount_Receivable_Amount（int），Fact_Amount （int）”我告诉你：那就SUM这两个字段，按照语法要求，其它列出的字段都放到最后GROUP BY你觉得靠谱吗？我自己都觉得不靠谱。

Answer 6

思路啊大概是这样啊 从一些表取出这些字段值啊，总共这些表:
Account_Receivable_List L,Charge_Item I,Charge_Icence E,Residence_Item R,Student S,Class C ,Specialty P ，
 有的表可能用不到啊以及取数据条件的不一样(主要是 
    (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)  这些限制条件的改变
)的这种情况下，我呢于是用unionall 捏它们到一块来了。突然我呢现在就想再统计两个字段啊，就这样，没啥好思路啊。

Answer 7

图片挂了，可以在我的相册里看到先前的运行结果：图片1，2,3,4
相册地址是：http://hi.csdn.net/space-1888707-do-album-id-54011.html

Answer 8

看了半天。你的代码太长了
我整理了半天,终于有了些思路。就是把这些代码看成一个嵌套表。。SQL2000版本
select 
Account_Receivable_List_No,
Charge_Date,
Re,
Charge_Date_Str,
DESex,
Authorize_No,
Charge_Standard,
Residence_Item_Name,
Charge_Item_Code,
Charge_Item_Name,
Accounts,
Advice_Note,
Name,Sex,
Class_Code,Class_Name,
sum(Account_Receivable_Amount)Account_Receivable_Amount,
Derate_Amount,Abate_Amount,
Refund_Amount,
sum(Fact_Amount)Fact_Amount ,
Arrearage_AmountFROM 
(
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 ,Specialty P  
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) 
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 ,Specialty P
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN  (19)  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 ,Specialty P
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 C.Specialty_No=P.Specialty_No  AND P.Department_No IN (19) 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 ,Specialty P  
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) 
) AS TB  -- 嵌套表group by Account_Receivable_List_No,
Charge_Date,
Re,
Charge_Date_Str,
DESex,
Authorize_No,
Charge_Standard,
Residence_Item_Name,
Charge_Item_Code,
Charge_Item_Name,
Accounts,
Advice_Note,
Name,Sex,
Class_Code,Class_Name,
Derate_Amount,Abate_Amount,
Refund_Amount,Arrearage_Amount
ORDER BY  Class_Code,Accounts,Charge_Date,Charge_Item_Code  

Answer 9

SQL2005:
with tb as 
(
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 ,Specialty P  
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) 
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 ,Specialty P
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN  (19)  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 ,Specialty P
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 C.Specialty_No=P.Specialty_No  AND P.Department_No IN (19) 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 ,Specialty P  
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) 
) -- 嵌套表select 
Account_Receivable_List_No,
Charge_Date,
Re,
Charge_Date_Str,
DESex,
Authorize_No,
Charge_Standard,
Residence_Item_Name,
Charge_Item_Code,
Charge_Item_Name,
Accounts,
Advice_Note,
Name,Sex,
Class_Code,Class_Name,
sum(Account_Receivable_Amount)Account_Receivable_Amount,
Derate_Amount,Abate_Amount,
Refund_Amount,
sum(Fact_Amount)Fact_Amount ,
Arrearage_Amount
FROM TB
group by Account_Receivable_List_No,
Charge_Date,
Re,
Charge_Date_Str,
DESex,
Authorize_No,
Charge_Standard,
Residence_Item_Name,
Charge_Item_Code,
Charge_Item_Name,
Accounts,
Advice_Note,
Name,Sex,
Class_Code,Class_Name,
Derate_Amount,Abate_Amount,
Refund_Amount,Arrearage_Amount
ORDER BY  Class_Code,Accounts,Charge_Date,Charge_Item_Code   

Answer 10

with TB as 
(
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 ,Specialty P  
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) 
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 ,Specialty P
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN  (19)  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 ,Specialty P
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 C.Specialty_No=P.Specialty_No  AND P.Department_No IN (19) 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 ,Specialty P  
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 C.Specialty_No=P.Specialty_No AND P.Department_No IN (19) 
) -- 嵌套表select 
Account_Receivable_List_No,
Charge_Date,
Re,
Charge_Date_Str,
DESex,
Authorize_No,
Charge_Standard,
Residence_Item_Name,
Charge_Item_Code,
Charge_Item_Name,
Accounts,
Advice_Note,
Name,Sex,
Class_Code,Class_Name,
sum(Account_Receivable_Amount)Account_Receivable_Amount,
Derate_Amount,Abate_Amount,
Refund_Amount,
sum(Fact_Amount)Fact_Amount ,
Arrearage_Amount
FROM TB
group by Account_Receivable_List_No,
Charge_Date,
Re,
Charge_Date_Str,
DESex,
Authorize_No,
Charge_Standard,
Residence_Item_Name,
Charge_Item_Code,
Charge_Item_Name,
Accounts,
Advice_Note,
Name,Sex,
Class_Code,Class_Name,
Derate_Amount,Abate_Amount,
Refund_Amount,Arrearage_Amount
ORDER BY  Class_Code,Accounts,Charge_Date,Charge_Item_Code   

Answer 11

先用视图把每个表都整理成相同的结构再在存储里面处里吧。
其实基本上每个UNION ALL都是差不多的，但看上去太复杂了。

Answer 12

还好了，我上周写了一个400多行的SQL，就是一点点的组装而已啦

Answer 13

看到这样的SQL，就想起了我以前，想起了我以前，我就想吐。