国标行业分类sql

最新推荐文章于 2025-09-16 10:48:20 发布

原创最新推荐文章于 2025-09-16 10:48:20 发布 · 4.7k 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一种使用SQL语句批量导出行业分类数据的方法，通过复杂的嵌套查询和数组聚合函数，实现了将多级行业分类信息转换为CSV格式文件的功能。此方法适用于需要对大量行业分类数据进行整理和导出的场景。

搜到了行业分类代码 2017 最新版创建sql语句 https://blog.youkuaiyun.com/hr541659660/article/details/78322050/

copy (

 select t.fid,t.firstType
 ,concat('{"',t.secondType,'":')
 ,concat('["',code_array,'"]},')

  from 
	(
	select t.fid,t.firstType,t.secondType,array_to_string(ARRAY(SELECT unnest(array_agg(t.sid)) ),'","')as code_array from 
	(select 
		t1.industrycode as fid
		,t1.industryname as firstType
		,t2.industryname as secondType
		,COALESCE(t3.industryname,t2.industryname) as thirdType    
		,coalesce(t3.industrycode,t2.industrycode)as sid
	from industry t1
	 left JOIN industry t2
	on t1.industrycode = t2.parentid
	left join  industry t3
	on t2.industrycode = t3.parentid 
	where (t2.industryname is not null or t3.industryname is not null ) and length(t1.industrycode)=2
	ORDER BY t1.industrycode,t2.industrycode,t3.industrycode) t  GROUP BY t.firstType,t.secondType, t.fid ORDER BY t.fid) t

	  )TO '/tmp/code2.csv' WITH csv ;