一条Mysql直接统计出总金额

需求：查询出符合条件的台账下的费用，然后根据币制统计出费用的总金额。

SELECT
	temp.*,
	SUM( sumAmountRmb ) rmbAmountSum,
	SUM( sumAmountUsd ) myAmountSum 
FROM
	(
	SELECT
	IF
		( b.CURR = 'CNY', b.AMOUNT, 0 ) sumAmountRmb,
	IF
		( b.CURR = 'USD', b.AMOUNT, 0 ) sumAmountUsd,
		a.ACCOUNT_NO,
		a.TYPE accountType,
		a.ACCOUNT_DATE,
		a.ASSIGNEE_NAME,
		b.SETTLEMENT_NAME,
		c.CUSTOMER_DESC,
		a.OPER_NAME,
		a.CHECK_PERSON,
		c.M_BILL_NO,
		c.HBL,
		c.DECLARATION_NO,
		c.DECLARATION_DATE,
		c.TRADE_TYPE,
		c.INSIDE_M_NO,
		c.CONTRACTS,
		c.MEMO,
		c.PACKS,
		c.CHARGED_WEIGHT,
		c.GROSSWT,
		c.ARRIVAL_PORT,
		c.DESTINATION_CITY,
		c.DES_PORT,
		c.ARRIVAL_PORT_CITY,
		c.DES_PORT_CITY,
		c.ARRIVAL_AREA,
		c.TRANSIT_NO,
		c.SHIPPING_TYPE,
		a.INVENTORY_DATE,
		a.OUT_DATE,
		c.INVT_NO,
		c.CURRENCY,
		c.TOTAL_AMOUNT,
		c.COMMISSIONED_MATTERS,
		c.CIQ_NO,
		c.CIQ_DATE,
		c.SECOND_CIQ_NO,
		c.SECOND_CIQ_DATE,
		c.DEC_ITEMS,
		c.CONTAINER_QUANTITY,
		c.IM_SIGN,
		a.MEMO memoAccount,
		a.LINK_NAME,
		a.REGULATORY_LIBRARY,
		c.AIRCOMPANY,
		c.AIRLINE,
		c.FLIGHT,
		c.ARRIVAL_DATE,
		c.VOLUME,
		c.DELIVERY_DAY,
	CASE
			
			WHEN ( b.MEMO IS NOT NULL AND b.MEMO != '' ) THEN
			b.MEMO ELSE b.`DESC` 
		END moneyName,
	b.AMOUNT,
	b.TYPE feiyongType 
FROM
	`ACCOUNT` a
	LEFT JOIN ACCOUNT_EXPENSE b ON a.ACCOUNT_NO = b.ACCOUNT_NO
	LEFT JOIN APPLY c ON c.ACCOUNT_NO = a.ACCOUNT_NO 
WHERE
	a.RBAC_IDS LIKE CONCAT( '%', '1310053881811587074', '%' ) 
	AND ( b.`TYPE` = 1 OR ( SELECT COUNT( 1 ) FROM ACCOUNT_EXPENSE WHERE ACCOUNT_NO = a.ACCOUNT_NO ) = 0 ) 
	) temp 
GROUP BY
	ACCOUNT_NO,
	SETTLEMENT_NAME
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

通过两次sum函数，可以方便的统计出人民币和美元的总金额。查询结果：