mysql 行转列之后插入其他表

最新推荐文章于 2023-07-16 20:41:04 发布

养家糊口的猫

最新推荐文章于 2023-07-16 20:41:04 发布

阅读量385

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_36608921/article/details/108278776

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

以下是mysql 将table2行转列之后插入 table1（a,b联合主键）中，且当某些字段不存在时MAX()返回null，配合IFNULL（）利用随机数填充数据。

insert into table1(a,b,c,d,e,f,g,h,i,g,k,l)
  (SELECT
      MAX(CASE FIELD_NAME WHEN 'rt' THEN (REVERSE(left(REVERSE(FIELD_VALUE),LOCATE('/',REVERSE(FIELD_VALUE))-1))) ELSE '' END) as 'a',
      MAX(CASE FIELD_NAME WHEN 'b' THEN FIELD_VALUE ELSE '' END) as 'b',
      MAX(CASE FIELD_NAME WHEN 'c' THEN
						CASE
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN  0 and 10  then 1
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN 11 and 20  then 2
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN 21 and 30  then 3
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN 31 and 40  then 4
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN 41 and 50  then 5
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN 51 and 60  then 6
								when (DATE_FORMAT(NOW(), '%Y') - SUBSTRING(FIELD_VALUE,1,4)) BETWEEN 61 and 110 then 7
						end
				ELSE 1
				END) as 'c',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'd' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*2 +1))) as 'd',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'e' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*7 +1))) as 'e',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'f' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*4 +1))) as 'f',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'g' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*3 +1))) as 'g',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'h' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*6 +1))) as 'h',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'i' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*3 +1))) as 'i',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'g' THEN FIELD_VALUE END),(SELECT FLOOR(RAND()*4 +1))) as 'j',
		MAX(CASE FIELD_NAME WHEN 'k' THEN FIELD_VALUE ELSE 0 END) as 'k',
		IFNULL(MAX(CASE FIELD_NAME WHEN 'l' THEN FIELD_VALUE END),(SELECT ROUND(rand()*2-1,2))) as 'l'
FROM table2
where id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
 #{item}
</foreach>
GROUP BY id)