工作需求第一次写千行SQL语句

  • 开发
  • 20

加油，新时代打工人！

查询某个县区数据，没有的数据用0补充
需求：全年12个月，每个月显示县区信息，为空的用0补充。
也就是把上面的sql拼接起来，第一个解决方案，用上面的sql，加上JAVA代码。
第二个解决方案下面的方式，一个sql完成，这样好处，在前端直接拿起来就用了。

(SELECT
             '1月' AS title,
             t.regionCode,
            t.regionName,
						t.cropName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 t4.title AS cropName,
                        FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
                          
														   <where>
	   t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 1
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
        </where>
       
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						 	 0 AS cropName,
                           0 AS	torganic,
             0 AS  tn,
            0 AS	op,
            0 AS	ok,
					  0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
														   <where>
	   t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 1
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
        </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
				union all(SELECT
             '2月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 2
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						  	 0 AS cropName,
                           0 AS	torganic,
             0 AS  tn,
            0 AS	op,
            0 AS	ok,
						            0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 2
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all(SELECT
             '3月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 3
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						  	 0 AS cropName,
                           0 AS	torganic,
             0 AS  tn,
            0 AS	op,
            0 AS	ok,
						            0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 3
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all
			(SELECT
			'4月' AS title,
		  t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
		FROM
			(
				(
					SELECT
						t1.`code` AS regionCode,
						t1.full_name AS regionName,
							 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
					FROM
						t_adminregion t1
							LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
							LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
							 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 4
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
					GROUP BY
					t1.`code`
					ORDER BY
					t1.`code`
			) UNION ALL
		SELECT
         t1.CODE,
             t1.full_name,
						  	 0 AS cropName,
              0 AS	torganic,
             0 AS  tn,
            0 AS	op,
            0 AS	ok,
						0 AS	slk,
            0 AS	ph
		FROM
			t_adminregion t1
				LEFT JOIN (
				SELECT
					t1.`code` AS regionCode,
					t1.full_name AS regionName,
						 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
				FROM
					t_adminregion t1
						LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
						LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
						 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 4
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
		GROUP BY
			t1.`code`
		ORDER BY
			t1.`code`
			) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
union all (SELECT
             '5月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 5
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						  0 AS cropName,
              0 AS	torganic,
             0 AS  tn,
            0 AS	op,
            0 AS	ok,
						0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 5
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all
			(SELECT
			'6月' AS title,
				t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
		FROM
			(
				(
					SELECT
						t1.`code` AS regionCode,
						t1.full_name AS regionName,
							 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
					FROM
						t_adminregion t1
							LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
							LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
							 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 6
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
					GROUP BY
					t1.`code`
					ORDER BY
					t1.`code`
			) UNION ALL
		SELECT
			t1.CODE,
			t1.full_name,
			 	 0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
		FROM
			t_adminregion t1
				LEFT JOIN (
				SELECT
					t1.`code` AS regionCode,
					t1.full_name AS regionName,
						 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
				FROM
					t_adminregion t1
						LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
						LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
						 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 6
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
		GROUP BY
			t1.`code`
		ORDER BY
			t1.`code`
			) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all
			(SELECT
             '7月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 7
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						  	 0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 7
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
				union all(SELECT
             '8月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 8
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						  	 0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 8
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all(SELECT
             '9月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
                          FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 9
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name, 	
							0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
                       FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
                        FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
                        FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
                        FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
                        FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
                        FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 9
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all
			(SELECT
			'10月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
		FROM
			(
				(
					SELECT
						t1.`code` AS regionCode,
						t1.full_name AS regionName,
							 t4.title AS cropName,
					 FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
						FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
						FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
						FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
						FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
						FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
					FROM
						t_adminregion t1
							LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
							LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
							 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 10
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
					GROUP BY
					t1.`code`
					ORDER BY
					t1.`code`
			) UNION ALL
		SELECT
			t1.CODE,
			t1.full_name,
			 	 0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
		FROM
			t_adminregion t1
				LEFT JOIN (
				SELECT
					t1.`code` AS regionCode,
					t1.full_name AS regionName,
						 t4.title AS cropName,
				 FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
					FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
					FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
					FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
					FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
					FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
				FROM
					t_adminregion t1
						LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
						LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
						 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 10
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
		GROUP BY
			t1.`code`
		ORDER BY
			t1.`code`
			) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
union all (SELECT
             '11月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
         FROM
             (
                 (
                     SELECT
                         t1.`code` AS regionCode,
                         t1.full_name AS regionName,
												 	 t4.title AS cropName,
				 FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
					FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
					FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
					FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
					FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
					FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                     FROM
                         t_adminregion t1
                             LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                             LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
														  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 11
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>

                     GROUP BY
                     t1.`code`
                     ORDER BY
                     t1.`code`
             ) UNION ALL
         SELECT
             t1.CODE,
             t1.full_name,
						  	 0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
         FROM
             t_adminregion t1
                 LEFT JOIN (
                 SELECT
                     t1.`code` AS regionCode,
                     t1.full_name AS regionName,
										 	 t4.title AS cropName,
				 FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
					FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
					FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
					FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
					FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
					FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
                 FROM
                     t_adminregion t1
                         LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
                         LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
												  LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 11
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
         GROUP BY
             t1.`code`
         ORDER BY
             t1.`code`
        ) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
			union all
			(SELECT
			'12月' AS title,
             t.regionCode,
            t.regionName,
            t.organic,
            t.tn,
            t.op,
            t.ok,
            t.slk,
            t.ph
		FROM
			(
				(
					SELECT
						t1.`code` AS regionCode,
						t1.full_name AS regionName,
							 t4.title AS cropName,
				 FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
					FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
					FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
					FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
					FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
					FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
					FROM
						t_adminregion t1
							LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
							LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
							 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 12
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
					GROUP BY
					t1.`code`
					ORDER BY
					t1.`code`
			) UNION ALL
		SELECT
			t1.CODE,
			t1.full_name,
			 	 0 AS cropName,
            0 AS	torganic,
            0 AS  tn,
            0 AS	op,
            0 AS	ok,
            0 AS	slk,
            0 AS	ph
		FROM
			t_adminregion t1
				LEFT JOIN (
				SELECT
					t1.`code` AS regionCode,
					t1.full_name AS regionName,
						 t4.title AS cropName,
				 FORMAT(IFNULL(AVG( t3.organic),0),2) as organic,
					FORMAT(IFNULL(AVG( t3.T_N ),0),2) as tn,
					FORMAT(IFNULL(AVG( t3.O_P ),0),2) as op,
					FORMAT(IFNULL(AVG( t3.O_K ),0),2) as ok,
					FORMAT(IFNULL(AVG( t3.SLK ),0),2) as slk,
					FORMAT(IFNULL(AVG( t3.ph ),0),2)  as ph
				FROM
					t_adminregion t1
						LEFT JOIN t_test_main t2 ON SUBSTRING( t2.village, 1, 6 ) = SUBSTRING( t1.`code`, 1, 6 )
						LEFT JOIN t_test_crops t3 ON t3.test_id = t2.id
						 LEFT JOIN t_param_crops t4 ON t.id = t3.crops_id
										 <where>
															t1.`level` = 22
                             AND t2.is_del = 0
                             AND MONTH ( t2.create_time ) = 12
            <if test="stt1tDate != null  and stt1tDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &gt;=  #{stt1tDate}</if>
            <if test="endDate != null  and endDate != ''"> and DATE_FORMAT(t2.real_test_time,'%Y-%m-%d') &lt;= #{endDate}</if>
                  </where>
		GROUP BY
			t1.`code`
		ORDER BY
			t1.`code`
			) a ON t1.CODE = a.regionCode
		WHERE
		       t1.`level` = 22
			) t
		GROUP BY
			t.`regionCode`
		ORDER BY
			t.`regionCode`)
阅读全部

相关推荐

  1. 工作需求第一次写千行SQL语句

    2024-07-13 10:44:08       21 阅读

  3. SQL 第一章 (准备工作)

    2024-07-13 10:44:08       31 阅读

  4. 千行 MySQL 学习笔记总结大全，语法大全

    2024-07-13 10:44:08       25 阅读

  5. 关于pr的第一次听写的笔记

    2024-07-13 10:44:08       35 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-07-13 10:44:08       67 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-07-13 10:44:08       72 阅读

  9. 在Django里面运行非项目文件

    2024-07-13 10:44:08       58 阅读

  19. Python语言-面向对象

    2024-07-13 10:44:08       69 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-07-13 10:44:08       64 阅读

热门阅读

  2. 项目管理开发实战

    2024-07-13 10:44:08       29 阅读

  6. 【AI原理解析】—知识图谱（KG）原理

    2024-07-13 10:44:08       18 阅读

  7. 0139__TCP协议

    2024-07-13 10:44:08       20 阅读

  8. sqlmap常用参数及示例

    2024-07-13 10:44:08       24 阅读

  15. 软件测试面试200问【答案+文档】

    2024-07-13 10:44:08       24 阅读

  17. C++之STL简介

    2024-07-13 10:44:08       23 阅读

  20. Linux——多路IO

    2024-07-13 10:44:08       25 阅读

  25. 【C++】C++中的extern用法

    2024-07-13 10:44:08       21 阅读

  26. 如何理解李彦宏说的“不要卷模型，要卷应用”

    2024-07-13 10:44:08       17 阅读

  27. 2024年，SEC对加密监管的格局将继续演变

    2024-07-13 10:44:08       20 阅读

  30. Python热门面试题一

    2024-07-13 10:44:08       20 阅读