mysql - msyql縱表轉(zhuǎn)化橫表問題
問題描述
構(gòu)造表sql語句:
DROP TABLE IF EXISTS `hr_new_sign_activities_field_data`;CREATE TABLE `hr_new_sign_activities_field_data` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `field_id` int(11) unsigned NOT NULL DEFAULT ’0’ COMMENT ’字段ID’, `value` varchar(255) DEFAULT NULL COMMENT ’提交過來的數(shù)據(jù)’, `acitvity_info_id` int(11) NOT NULL DEFAULT ’0’ COMMENT ’表hr_new_sign_activities_info的自增ID’, PRIMARY KEY (`id`), KEY `acitvity_info_id` (`acitvity_info_id`)) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT=’用戶提交的表單數(shù)據(jù)----這個(gè)主要是存儲(chǔ)的后臺(tái)設(shè)置的字段,用戶提交的字段數(shù)據(jù)’;-- ------------------------------ Records of hr_new_sign_activities_field_data-- ----------------------------INSERT INTO `hr_new_sign_activities_field_data` VALUES (’6’, ’38’, ’我的企業(yè)’, ’5’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’7’, ’39’, ’13985623562’, ’5’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’8’, ’40’, ’13985623562@qq.com’, ’5’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’15’, ’38’, ’我的企業(yè)哈’, ’8’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’16’, ’39’, ’13652356524’, ’8’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’17’, ’40’, ’wangjian@shixiba.com’, ’8’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’18’, ’38’, ’sasassasasa’, ’9’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’19’, ’39’, ’13985236523’, ’9’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’20’, ’40’, ’13985236523@qq.com’, ’9’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’24’, ’38’, ’我速度1’, ’11’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’25’, ’39’, ’13562354125’, ’11’);INSERT INTO `hr_new_sign_activities_field_data` VALUES (’26’, ’40’, ’1253652361@qq.com’, ’11’);
我現(xiàn)在的數(shù)據(jù)表是這樣的我現(xiàn)在期望轉(zhuǎn)化為目前我的sql語句是這樣的
select acitvity_info_id , CASE value when ’38’ then value else ’’ end as field_38, CASE value when ’39’ then value else ’’ end as field_39 , CASE value when ’40’ then value else ’’ end as field_40 from hr_new_sign_activities_field_data where acitvity_info_id in (5,8,9 , 11) GROUP BY acitvity_info_id
sql語句的效果如下:
我期望的最終效果如下:
問題解答
回答1:已經(jīng)找到解決辦法了
select m.acitvity_info_id ,CASE m.value when ’38’ then m.value else (select value from hr_new_sign_activities_field_data where acitvity_info_id =m.acitvity_info_id and field_id=38) end as field_38, CASE value when ’39’ then value else (select value from hr_new_sign_activities_field_data where acitvity_info_id =m.acitvity_info_id and field_id=39) end as field_39 , CASE value when ’40’ then value else (select value from hr_new_sign_activities_field_data where acitvity_info_id =m.acitvity_info_id and field_id=40) end as field_40 from hr_new_sign_activities_field_data m where acitvity_info_id in (5,8,9 , 11) GROUP BY acitvity_info_id
相關(guān)文章:
1. mac OSX10.12.4 (16E195)下Mysql 5.7.18找不到配置文件my.cnf2. mysql - 怎么生成這個(gè)sql表?3. mysql儲(chǔ)存json錯(cuò)誤4. php - 公眾號(hào)文章底部的小程序二維碼如何統(tǒng)計(jì)?5. mysql - 表名稱前綴到底有啥用?6. mysql - 數(shù)據(jù)庫表中,兩個(gè)表互為外鍵參考如何解決7. Navicat for mysql 中以json格式儲(chǔ)存的數(shù)據(jù)存在大量反斜杠,如何去除?8. 在mybatis使用mysql的ON DUPLICATE KEY UPDATE語法實(shí)現(xiàn)存在即更新應(yīng)該使用哪個(gè)標(biāo)簽?9. mysql - 數(shù)據(jù)庫建字段,默認(rèn)值空和empty string有什么區(qū)別 11010. sql語句 - 如何在mysql中批量添加用戶?
