MYSQL纵表转横表

偏执的太偏执、 2022-10-02 13:58 273阅读 0赞

### 一、表结构  ###

**（1）import\_genedata**

![20190619124526679.png][]

**（2）gene\_info**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dld2VlZWVlZWVl_size_16_color_FFFFFF_t_70][]

### 二、存储过程编写 ###

DROP PROCEDURE GeneInfoDeal;
    DROP PROCEDURE GeneInfoUniprotSelect;
    
    -- 每个不重复uniprot的处理
    CREATE PROCEDURE GeneInfoDeal(
    	uniprot VARCHAR(255)
    )
    BEGIN
    	DECLARE Gene_Name VARCHAR(255);
    	DECLARE NCBI_TaxID VARCHAR(255);
    	DECLARE RefSeq VARCHAR(255);
    	DECLARE MINT VARCHAR(255);
    	DECLARE GeneID VARCHAR(255);
    	DECLARE Gene_Synonym VARCHAR(255);
    	
    	-- DECLARE uniprot VARCHAR(255);
    	DECLARE mykey VARCHAR(255);
    	DECLARE myvalue VARCHAR(255);
    	declare flag int default true;
    
    		DECLARE set_values CURSOR FOR SELECT ig.`index`,ig.`xulie` FROM import_genedata_copy1 ig WHERE ig.uniprot = uniprot;
    		
    		declare continue handler for not found set flag = false;
    		
    		OPEN set_values;
    		REPEAT
    		
    			FETCH set_values INTO mykey,myvalue;
    			
    			if mykey = 'Gene_Name'
    				then set Gene_Name = myvalue;
    			elseif mykey = 'NCBI_TaxID'
    				then set NCBI_TaxID = myvalue;
    			elseif (mykey = 'RefSeq' or mykey = 'RefSeq_NT')
    				then set RefSeq = CONCAT_WS(',',RefSeq,myvalue);
    			elseif mykey = 'MINT'
    				then set MINT = uniprot;
    			elseif mykey = 'GeneID'
    				then set GeneID = myvalue;
    			elseif mykey = 'Gene_Synonym'
    				then set Gene_Synonym = myvalue;
    			end if;
    			
    		UNTIL flag = false END REPEAT;
    		CLOSE set_values;
    		
    		INSERT INTO `gene_info` (`Gene_Name`,`NCBI_TaxID`,`RefSeq`,`MINT`,`GeneID`,`Gene_Synonym`) VALUES
    		(Gene_Name,NCBI_TaxID,RefSeq,MINT,GeneID,Gene_Synonym);
    
    		
    END;
    
    -- 查询不充分uniprot
    CREATE PROCEDURE GeneInfoUniprotSelect()
    BEGIN
    	
    	DECLARE uniprot VARCHAR(255);
    	DECLARE flag INT DEFAULT TRUE;
    	
    	-- 游标
    	DECLARE set_uniprot CURSOR FOR SELECT DISTINCT ig.`uniprot` FROM import_genedata_copy1 ig;
    	
    	declare continue handler for not found set flag = false;
    	
    	OPEN set_uniprot;
    	REPEAT
    	
    		FETCH set_uniprot INTO uniprot;
    		
    		CALL GeneInfoDeal(uniprot);
    		
     	UNTIL flag = false END REPEAT;
    	CLOSE set_uniprot;
    		
    END;
    
    CALL GeneInfoDeal('P48347');
    CALL GeneInfoUniprotSelect();

[20190619124526679.png]: /images/20220112/7e1833433cd946e1911abec5dfbe47e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dld2VlZWVlZWVl_size_16_color_FFFFFF_t_70]: /images/20220112/821359ea8dc84032954e293b10aea300.png