MySQL数据库把两个表的统计值合到一块

[叁金Coder]

这个是为了做报表展示用的，展示登记操作、入库操作、跟出库操作所占的比例（我在这里用in是因为还有审批的状态）
表一为当前状态（p_cases_goods）：ID 、goods_name、state、people
表二为操作历史记录（p_goodstag）：ID、goods_state、people

SELECT
		(
			SELECT
				(
					(
						SELECT
							COUNT(state) AS goods_State
						FROM
							p_cases_goods
					) + (
						SELECT
							COUNT(goods_state) AS goods_State
						FROM
							p_goodstag
					)
				)
		) AS goods_States,
		(
			SELECT
				(
					(
						SELECT
							COUNT(state) AS goods_State_Storage
						FROM
							p_cases_goods
						WHERE
							state IN (
								'登记',
                                                                '登记审批中'
							)
					) + (
						SELECT
							COUNT(goods_state) AS goods_State_Storage
						FROM
							p_goodstag
						WHERE
							goods_state IN (
								'登记',
                                                     '登记审批中'
							)
					)
				)
		) AS goods_State_Storages,
                (
			SELECT
				(
					(
						SELECT
							COUNT(state) AS goods_State_Disposal
						FROM
							p_cases_goods
						WHERE
							state IN (
								'入库'
							)
					) + (
						SELECT
							COUNT(goods_state) AS goods_State_Disposal
						FROM
							p_goodstag
						WHERE
							goods_state IN (
								'入库'
							)
					)
				)
		) goods_State_Disposals,
		(
			SELECT
				(
					(
						SELECT
							COUNT(state) AS goods_State_Return
						FROM
							p_cases_goods
						WHERE
							state IN ('已出库')
					) + (
						SELECT
							COUNT(goods_state) AS goods_State_Return
						FROM
							p_goodstag
						WHERE
							goods_state IN ('已出库')
					)
				)
		) AS goods_State_Returns;

小弟在此请教大家有什么能够优化的好方法吗？

[二月十六]

把统计计算的，先都统计出来，然后再关联相加，不要每次统计一次，类似这样的

SELECT
    COUNT(state) AS goods_State,
    SUM(   CASE
               WHEN state IN (
                                 '登记', '登记审批中'
                             )
                   THEN 1
               ELSE
                   0
           END
       ) AS goods_State_Storage,
	SUM(   CASE
               WHEN state IN (
                                 '入库'
                             )
                   THEN 1
               ELSE
                   0
           END
       ) AS goods_State_Disposals
	   ..........
FROM
    p_cases_goods;