国标行业分类sql

 

搜到了行业分类代码 2017 最新版 创建sql语句 https://blog.csdn.net/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 ;