啊 sql语句写的过长，失去控制了，如何统计字段的值啊

SELECT  L.Account_Receivable_List_No,L.Charge_Date,L.Remark,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.Remark,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.Remark,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.Remark,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），如何操作啊？！

42 个解决方案

#1

#2

引用 1 楼 guguda2008 的回复:

大家快来啊，来者有份啊

#3

彪悍的代码不需要解释！~ 

#4

楼主威武 楼主荡漾

#5

逻辑太复杂，而且不易读，也没有测试数据和业务逻辑说明

#6

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

#7

只能膜拜大牛了。太恐怖了。

#8

标题看着就很有意思
进来以后
彻底就被震住了

#9

引用 5 楼 guguda2008 的回复:

逻辑太复杂，而且不易读，也没有测试数据和业务逻辑说明

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

#10

自己顶啊啊，人呢

#11

#12

积极性大受受打击，排好第一段，发现后面还有卅UNION ALL

SELECT
	L.Account_Receivable_List_No,
	L.Charge_Date,
	L.Remark,
	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)

#13

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

#14

人啊，人呢

#15

你自己排版一下吧，好好理一下自己的思路，最起码你自己清楚需要什么样的数据，别人根本不知道，这么多表，这么多UNION。

“我想用SUM()来统计这些字段的值：ccount_Receivable_Amount（int），Fact_Amount （int）”

我告诉你：那就SUM这两个字段，按照语法要求，其它列出的字段都放到最后GROUP BY

你觉得靠谱吗？我自己都觉得不靠谱。

#16

引用 15 楼 sqlcenter 的回复:

你自己排版一下吧，好好理一下自己的思路，最起码你自己清楚需要什么样的数据，别人根本不知道，这么多表，这么多UNION。

“我想用SUM()来统计这些字段的值：ccount_Receivable_Amount（int），Fact_Amount （int）”

我告诉你：那就SUM这两个字段，按照语法要求，其它列出的字段都放到最后GROUP BY

你觉得靠谱吗？我自己都觉得不靠谱。

思路啊大概是这样啊 从一些表取出这些字段值啊，总共这些表:
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 捏它们到一块来了。突然我呢现在就想再统计两个字段啊，就这样，没啥好思路啊。

#17

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

#18

那个，高手快来啊，分不够另加啊

#19

该回复于2010-08-13 16:35:46被版主删除

#20

看了半天。。。。你的代码太长了
我整理了半天,终于有了些思路。就是把这些代码看成一个嵌套表。。

SQL2000版本

select 
Account_Receivable_List_No,
Charge_Date,
Remark,
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 
(
SELECT 
L.Account_Receivable_List_No,
L.Charge_Date,
L.Remark,
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.Remark,
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.Remark,
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.Remark,
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,
Remark,
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 

 

#21

SQL2005:

with tb as 
(
SELECT 
L.Account_Receivable_List_No,
L.Charge_Date,
L.Remark,
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.Remark,
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.Remark,
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.Remark,
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,
Remark,
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,
Remark,
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  

 

#22

with TB as 
(
SELECT 
L.Account_Receivable_List_No,
L.Charge_Date,
L.Remark,
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.Remark,
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.Remark,
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.Remark,
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,
Remark,
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,
Remark,
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  

 

#23

#24

膜拜
膜拜
膜拜
膜拜
膜拜

#25

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

#26

开眼界了。。。

#27

其实内容很简单，就是代码长了点，只要思路理清了就可以了

#28

引用 27 楼 duanzhi1984 的回复:

其实内容很简单，就是代码长了点，只要思路理清了就可以了

这个得顶

#29

建议使用临时表

#30

我以为我牛，原来牛外有牛啊

#31

感谢各位大大们，我另辟了一条小路解决了。还是感谢你们哦。

#32

God ...厉害...

#33

my
 god
太厉害了

#34

如此强大彪悍的SQL！

#35

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

#36

很好很好，治疗睡眠不足症，看一看包“ 药到病除”

#37

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

#38

God ...厉害死了呀啊...

#39

我嘞个去啊，，连个临时表都不用

#40

學習了

#41

楼主，太可怕了，您弄瞎我的眼睛啦~~~

#42

膜拜楼主 膜拜(杜安)