复杂的统计语句-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_39522272/article/details/119540652

该查询联合多个子查询，分别统计了不同类型的国家、城镇、村庄的数量，以及低电压用户数、办公区数量和组织数量。通过对dc_cumulative_outage_list_detail表、dc_desk_area_info表和dc_cumulative_outage_list表的数据进行过滤和聚合，得到详细的国家区域信息统计结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select ttt.country,sum(ttt.townNum),sum(ttt.villageNum),sum(ttt.accountNum),sum(ttt.deskAreaNum),sum(ttt.orgNum) from (SELECT
			tt.country,
			count(DISTINCT tt.town) AS townNum,
			count(DISTINCT tt.village) AS villageNum,
			0 as accountNum,
	    0 as deskAreaNum,
      0 as orgNum
		
		FROM
			(
				SELECT
					t2.country,
					t2.town,
					t2.village
				FROM
					dc_cumulative_outage_list_detail t1
				INNER JOIN dc_desk_area_info t2 ON t1.desk_area_id = t2.desk_area_id
				INNER JOIN dc_cumulative_outage_list t3
				ON t1.cumulative_outage_list_id=t3.id
				WHERE
					t1.is_deleted = 0
				AND t2.is_deleted = 0
				AND (
					t1.type = 3
					or
					(t1.type = 4
				 	AND t1.is_power_failure='是')))tt
			GROUP BY
			tt.country

   UNION 

		SELECT
			t2.country,
      0 as townNum,
	    0 as villageNum,
			sum(t2.low_voltage_users) as accountNum,
			0 as deskAreaNum,
      0 as orgNum
		FROM
			dc_cumulative_outage_list_detail t1
		INNER JOIN dc_desk_area_info t2 ON t1.desk_area_id = t2.desk_area_id
		INNER JOIN dc_cumulative_outage_list t3
		ON t1.cumulative_outage_list_id=t3.id
		WHERE
			t1.is_deleted = 0
		AND t2.is_deleted = 0
		AND (
			t1.type = 3 or t1.type=2
		  or (t1.type=4 and t1.is_power_failure='是')
		)

    GROUP BY t2.country

    UNION
		
    SELECT
			t2.country,
      0 as accountNum,
	    0 as deskAreaNum,
			0 as accountNum,
			count(DISTINCT t1.desk_area_id) as deskAreaNum,
      count(DISTINCT t1.org) as orgNum
		FROM
			dc_cumulative_outage_list_detail t1
		INNER JOIN dc_desk_area_info t2 ON t1.desk_area_id = t2.desk_area_id
		INNER JOIN dc_cumulative_outage_list t3
		ON t1.cumulative_outage_list_id=t3.id
		WHERE
			t1.is_deleted = 0
		AND t2.is_deleted = 0
		AND (
			t1.type = 3
		  or (t1.type=4 and t1.is_power_failure='是')
		)

    	GROUP BY t2.country) ttt GROUP BY ttt.country