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

于 2024-07-13 10:05:16 发布
阅读量68 收藏
点赞数
分类专栏： Java 数据库 文章标签： sql java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45397785/article/details/140395279
版权
Java 同时被 2 个专栏收录
102 篇文章 2 订阅
订阅专栏
数据库
25 篇文章 0 订阅
订阅专栏

加油，新时代打工人！

查询某个县区数据，没有的数据用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`)
Hello World呀
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
sqlserver第一次语句
09-25
第一次slq语句保存。第一次slq语句保存第一次slq语句保存第一次slq语句保存第一次slq语句保存
写出高性能SQL语句的34条规则
01-31
本文我们主要就总结了34条写高性能SQL语句的原则，接下来就让我们一起来了解一下这部分内容吧。 在SQLServer数据库中，我们在写查询语句时，一定要遵循一定的原则才能能够使SQL语句执行起来更加的高效率。本文我们...
MySQL性能优化（一）MySQL中SQL语句是如何执行的？
热门推荐
歪桃的博客
12-06 1万+
该篇章将开始整理MySQL的优化，不过开始之前，我们想了解清楚那就是MySQL是怎么执行的。 文章目录1.MySQL驱动2.应用系统数据库连接池3.MySQL数据库连接池4.SQL执行过程4.1.线程监听：监听网络请求中的SQL语句4.2.SQL接口：负责处理接收到的SQL语句4.3.查询解析器：让MySQL能看懂SQL语句4.4.查询优化器：选择最优的查询路径4.5.存储引擎接口：真正执行SQL语句4.6.执行器：根据执行计划调用存储引擎的接口 1.MySQL驱动 大家都知道，我们如果要在Java系统中去
怎样评价一个几千行的 SQL 存储过程
wujiandao的专栏
02-10 1045
前两天在知乎逛街，看到有这么个话题，顺手回答了下，并分享在这里，喜欢的话就一起讨论讨论吧。 几千行的 SQL 存储过程，在比较老式的开发架构中常见，起源于 C/S 年代。通常是前端没有引入适当框架的设计，而将所有的应用逻辑一股脑儿丢给数据库开发造成的。当然这类设计的好处是上线快速，极短时间便可拿下项目。而弊端是扩展起来麻烦，尤其数据库开发一旦跳槽之后，留下的往往是看不懂的上古祖传代码...
面试官：MySQL中SQL语句是如何执行的？
AI研习社
04-06 513
开发者（KaiFaX）面向全栈工程师的开发者专注于前端、Java/Python/Go/PHP的技术社区来源 |业余草该篇章将开始整理MySQL的优化，不过开始之前，我们想了解清楚那就是MySQL是怎么执行的。文章目录MySQL驱动应用系统数据库连接池MySQL数据库连接池SQL执行过程线程监听：监听网络请求中的SQL语句SQL接口：负责处理接收到的SQL语句查询解析器：...
Oracle向MySQL改造：复杂SQL语句的转化和优化
weixin_40649680的博客
12-03 1906
Oracle向MySQL8+改造：复杂SQL语句的转化和性能优化
ThinkPHP框架获取最后一次执行SQL语句及变量调试简单操作示例
10-18
主要介绍了ThinkPHP框架获取最后一次执行SQL语句及变量调试简单操作,结合实例形式分析了thinkPHP获取最后一次执行SQL语句的getLastSql()及fetchSql()函数以及使用dump方法变量调试、E方法输出错误信息等操作技巧,...
sql语句sql语句sql语句sql语句.txt
06-01
sql语句sql语句sql语句sql语句sql语句
第一次实验语句.sql
10-18
第一次实验语句.sql
SQL性能优化策略
modelsetget的博客
07-08 487
你不得不知的SQL调优策略笔记
F4搜索帮助和按条件写sql
m0_60109224的博客
07-08 271
2.按条件读取刚刚写的sql数据，if sy-subrc 中可以继续读。3.搜索帮助用函数和sql实现。4.根据屏幕输入数据筛选实现。
如何解决 PostgreSQL 中由于索引不当导致的性能下降问题？
最新发布
技术笔记
07-12 395
比如说，有一个订单表，其中“订单日期”这个列经常被用于按时间范围查询订单，但却没有为其创建索引，这就会导致数据库在查询时需要遍历整个表，极大地降低了查询速度。命令，我们可以查看查询的执行计划。再举一个例子，如果我们发现某个表上存在多个类似的索引，比如“idx_age_1”和“idx_age_2”，并且它们的定义几乎相同，这时候就可以考虑删除其中一个冗余的索引，以减少维护成本和提高性能。然而，就像任何复杂的系统一样，它也可能会遇到性能方面的挑战，其中由于索引不当导致的性能下降问题是比较常见且令人头疼的。
[高频 SQL 50 题(基础版)]第一千七百五十七题，可回收且低脂产品
weixin_45201305的博客
07-10 257
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。没什么难度，作为基础题五十题的第一题练手，现在也开始陆续写一些SQL，作为记录，和leetcode一起，大家都加油。编写解决方案找出既是低脂又是可回收的产品编号。是该表的主键（具有唯一值的列）。
SQL面试题-留存率计算
xiao4816的博客
07-07 325
计算的是整段时间范围内，每一天为基准的所有的留存1、2、7天的用户数。计算的是用户首次登陆时间为基准时间，计算该基准时间之后的n日留存率。方法一的优势是可以一次性计算出，每天的不同时间范围的留存率。缺点：如果要计算n天留存需要增加代码量。但是不是很直观，并且计算量比较大。优点：代码直观好理解。
SQL Server设置端口：跨平台指南
招风的黑耳CSDN
07-09 586
在使用SQL Server时，设置或修改其监听的端口是确保数据库服务安全访问和高效管理的重要步骤。由于SQL Server可以部署在多种操作系统上，包括Windows、Linux和Docker容器等，因此设置端口的步骤和方法也会因平台而异。本文将为您提供一个跨平台的指南，帮助您在不同环境下设置SQL Server的端口。
数据如何查询
qq_46863837的博客
07-08 1219
只有学会DQL，才是真正的进入数据库的世界
怎样优化 PostgreSQL 中对 XML 数据的存储和查询？
技术笔记
07-08 873
在 PostgreSQL 中处理 XML 数据时，为了实现高效的存储和查询，需要采取一系列的优化策略。以下将详细探讨如何优化 PostgreSQL 中对 XML 数据的存储和查询，并提供相应的解决方案和示例代码。
PostgreSQL 中如何实现数据的增量更新和全量更新的平衡？
技术笔记
07-09 939
在数据库管理中，数据的更新操作是常见的任务。对于大型数据集或高并发的系统，选择合适的更新策略至关重要。增量更新和全量更新是两种常见的数据更新方式，如何在 PostgreSQL 中平衡这两种更新方式以确保数据的一致性、性能和可靠性是一个值得深入探讨的问题。
写一个sql语句，查询工资第二高的人
06-11
以下是一个查询工资第二高的人的 SQL 语句示例： ``` SELECT name, salary FROM employee WHERE salary = ( SELECT MAX(salary) FROM employee WHERE salary < ( SELECT MAX(salary) FROM employee ) ); ``` 上述 SQL 语句中，首先使用子查询获取工资最高的人的工资，接着在子查询中查询工资次高的人的工资，然后使用外部查询获取工资等于次高工资的人的姓名和工资。也就是说，查询结果是所有工资等于次高工资的人的姓名和工资，如果只需要一个人，则需要加上 LIMIT 1 子句。 需要注意的是，上述 SQL 语句仅供参考，实际情况下可能需要根据具体表结构和数据进行修改。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hello World呀

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值