SQL 语句
mysql 循环插入数据
BEGIN #Routine body goes here... DECLARE n bigint; set n=1; while n < 501 DO INSERT into user(user_id) VALUES(n); set n=n+1; end while; END
复制表数据
Insert into table2 select * from table1; 如果表不存在可以使用 create table table2 as select * from table1;
oracle
CREATE OR REPLACE procedure proc_100million as begin for i in 1006..1008 loop INSERT into SG_USER(USER_ID,CUST_ID,CREATE_TIME,USER_TYPE) VALUES(i,i,'2017-10-30 19:58:43',0); end loop; end proc_100million;
复制列并拼接字符
UPDATE "SCOTT"."SG_USER" set WEIXIN_NICKNAME=USER_ID ||'nickname' WHERE USER_ID=565;
使用随机数 Oracle
UPDATE "SCOTT"."SG_USER_copy" set WEIXIN_NICKNAME=trunc(DBMS_RANDOM.VALUE(1,10000));
使用rownum筛选范围
select * from (SELECT ROWNUM a,SCORE_RULE_ID,SCORE,USER_ID,SCORE_TYPE FROM "SCOTT"."SCORE_20W") WHERE a=75950;
单个用户可用积分(对null) SELECT g.score-h.score from (SELECT sum(c.sumscore) score from (SELECT b.score_rule_id,b.score*b.cnt sumscore,b.score_type from (SELECT DISTINCT a.*,cnts.cnt FROM (SELECT score_rule_id,score,user_id,score_type FROM "SCORE_DETAIL" WHERE user_id=103) a LEFT JOIN (SELECT a.score_rule_id,count(*) cnt from (SELECT score_rule_id,score,user_id,score_type FROM "SCORE_DETAIL" WHERE user_id=103) a GROUP BY score_rule_id) cnts on a.score_rule_id=cnts.score_rule_id) b) c WHERE score_type=1) g,(SELECT nvl(sum(c.sumscore),0) score from (SELECT b.score_rule_id,b.score*b.cnt sumscore,b.score_type from (SELECT DISTINCT a.*,cnts.cnt FROM (SELECT score_rule_id,score,user_id,score_type FROM "SCORE_DETAIL" WHERE user_id=103) a LEFT JOIN (SELECT a.score_rule_id,count(*) cnt from (SELECT score_rule_id,score,user_id,score_type FROM "SCORE_DETAIL" WHERE user_id=103) a GROUP BY score_rule_id) cnts on a.score_rule_id=cnts.score_rule_id) b) c WHERE score_type=2) h;
SELECT g.score-h.score from
(SELECT sum(c.sumscore) score from
(SELECT b.score_rule_id,b.score*b.cnt sumscore,b.score_type from
(SELECT DISTINCT a.*,cnts.cnt FROM
(SELECT score_rule_id,score,user_id,score_type FROM "SCORE_DETAIL" WHERE user_id=103) a
LEFT JOIN
(SELECT a.score_rule_id,count(*) cnt from
(SELECT score_rule_id,score,user_id,score_type FROM
"SCORE_DETAIL" WHERE user_id=103) a GROUP BY score_rule_id) cnts on a.score_rule_id=cnts.score_rule_id) b) c WHERE score_type=1) g,(SELECT nvl(sum(c.sumscore),0) score from
(SELECT b.score_rule_id,b.score*b.cnt sumscore,b.score_type from (SELECT DISTINCT a.*,cnts.cnt FROM (SELECT score_rule_id,score,user_id,score_type FROM
"SCORE_DETAIL" WHERE user_id=103) a LEFT JOIN (SELECT a.score_rule_id,count(*) cnt from
(SELECT score_rule_id,score,user_id,score_type FROM "SCORE_DETAIL" WHERE user_id=103) a
GROUP BY score_rule_id) cnts on a.score_rule_id=cnts.score_rule_id) b) c WHERE score_type=2) h;
流程:单个用户积分规则出现次数-》次数*分数——》分数加减->可用积分
查询单用户积分 SELECT 4 * add20 + 8 * add21 + 12 * add22 - 2 * add23 - 3 * add24 - 5 * add25 obtain_score FROM (SELECT COUNT (*) add20 FROM "SYSTEM"."SCORE_DETAIL" WHERE USER_ID = 535 AND SCORE_RULE_ID = 2020) A, (SELECT COUNT (*) add21 FROM "SYSTEM"."SCORE_DETAIL" WHERE USER_ID = 535 AND SCORE_RULE_ID = 2021) B, (SELECT COUNT (*) add22 FROM "SYSTEM"."SCORE_DETAIL" WHERE USER_ID = 535 AND SCORE_RULE_ID = 2022) C, (SELECT COUNT (*) add23 FROM "SYSTEM"."SCORE_DETAIL" WHERE USER_ID = 535 AND SCORE_RULE_ID = 2023) D, (SELECT COUNT (*) add24 FROM "SYSTEM"."SCORE_DETAIL" WHERE USER_ID = 535 AND SCORE_RULE_ID = 2024) E, (SELECT COUNT (*) add25 FROM "SYSTEM"."SCORE_DETAIL" WHERE USER_ID = 535 AND SCORE_RULE_ID = 2025) F; 查询多用户积分 SELECT a.USER_ID,4 * add20+8 * add21+12*add22-2*add23-3*add24-5*add25 obtain_score from (SELECT USER_ID,count(*) add20 FROM "SYSTEM"."SCORE_DETAIL" where SCORE_RULE_ID=2020 GROUP BY USER_ID ORDER BY USER_ID asc) a LEFT JOIN (SELECT USER_ID,count(*) add21 FROM "SYSTEM"."SCORE_DETAIL" where SCORE_RULE_ID=2021 GROUP BY USER_ID ORDER BY USER_ID asc) b on a.USER_ID=b.USER_ID LEFT JOIN (SELECT USER_ID,count(*) add22 FROM "SYSTEM"."SCORE_DETAIL" where SCORE_RULE_ID=2022 GROUP BY USER_ID ORDER BY USER_ID asc) c on b.USER_ID=c.USER_ID LEFT JOIN (SELECT USER_ID,count(*) add23 FROM "SYSTEM"."SCORE_DETAIL" where SCORE_RULE_ID=2023 GROUP BY USER_ID ORDER BY USER_ID asc) d on c.USER_ID=d.USER_ID LEFT JOIN (SELECT USER_ID,count(*) add24 FROM "SYSTEM"."SCORE_DETAIL" where SCORE_RULE_ID=2024 GROUP BY USER_ID ORDER BY USER_ID asc) e on d.USER_ID=e.USER_ID LEFT JOIN (SELECT USER_ID,count(*) add25 FROM "SYSTEM"."SCORE_DETAIL" where SCORE_RULE_ID=2025 GROUP BY USER_ID ORDER BY USER_ID asc) f on e.USER_ID=f.USER_ID;
Keep going