SQL语句感悟

在数量级不大的情况下，sql语句效率的差距不大。 这次面对百万规模的数据 sql的写法，存储过程的思路，都极为关键，严重关系到时间成本！

ORACLE

CREATE OR REPLACE
procedure isertID is
begin
	--统计每个班的人数，建立新表
  --新建游标suts
  Declare cursor suts is  SELECT distinct a.XX_BJXX_ID, a.XX_JBXX_ID,a.XX_NJXX_ID,b.COUNTS
FROM
(select XX_JBXX_ID,XX_NJXX_ID,XX_BJXX_ID from XS_JBXX_2018 where XX_BJXX_ID  is not null  )a
left join 
(select XX_BJXX_ID,count(*) COUNTS from XS_JBXX_2018 WHERE XX_BJXX_ID is not null group by XX_BJXX_ID)b
on a.XX_BJXX_ID=b.XX_BJXX_ID;
  begin
    --开始循环
    for sut in suts loop
      begin
insert into XX_MERGE_2018 (XX_JBXX_ID,XX_NJXX_ID,XX_BJXX_ID,COUNTS)
        values (sut.XX_JBXX_ID,sut.XX_NJXX_ID,sut.XX_BJXX_ID,sut.COUNTS);
      end;
    end loop;
    commit;
  end;
end;

这个是ORACLE数据库的存储过程
游标包含了左连接+去重复数据+统计数量，此种写法效率比begin里面select语句的堆砌效率高的多。

MYSQL

CREATE DEFINER=`root`@`%` PROCEDURE `test_script`()
BEGIN
-- 该变量用于标识是否还有数据需遍历
	DECLARE flag INT DEFAULT 0;
-- 创建一个变量用来存储遍历过程中的值
	DECLARE this_NJXX_ID VARCHAR(40);
-- 查询出需要遍历的数据集合
	DECLARE idList CURSOR FOR (SELECT XX_NJXX_ID FROM zxxs_xs_jbxx_merge WHERE RXNF is null ORDER BY id DESC );
-- 查询是否有下一个数据，没有将标识设为1，相当于hasNext
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
-- 打开游标
	OPEN idList;
-- 取值设置到临时变量中
		FETCH idList INTO this_NJXX_ID;
-- 遍历未结束就一直执行
		WHILE flag != 1 DO
			UPDATE zxxs_xs_jbxx_merge 
			SET 
			RXNF = (SELECT RXNF FROM zxxs_xx_njxx_1 WHERE XX_NJXX_ID=this_NJXX_ID )
			WHERE 
			RXNF is null	
			and
			XX_NJXX_ID = this_NJXX_ID;
				FETCH idList INTO this_NJXX_ID;
		END WHILE;
	CLOSE idList;
END

这个是MYSQL数据库的存储过程
效率不太行，循环语句块内部还有select，很慢

感觉自己对group by这种查询还不太行，组合的好效率还是不错的，继续学习！

select XX_BJXX_ID,count(*) COUNTS from XS_JBXX_2018 group by XX_BJXX_ID

统计数量，去重