sql 20170922
来源：互联网 发布：淘宝开店要交钱吗 编辑：程序博客网 时间：2024/06/04 00:27
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT t1.id, t1.ind_code, t1.ind_name, t1.ind_type, t1.sch_type, t1.ind_date, t1.data_code, t1.ind_unit, t1.trend_type, t1.interval_max, t1.interval_min, t1.ind_Target, t1.upper_limit, t1.lower_limit, t1.ind_formula, t1.ind_class_id, t1.remark, t1.is_drill, t2.ind_total, t2.ind_avg, t2.ind_max, t2.ind_min, t2.ind_last_year, t2.ind_rankFROM t_indicator_def t1, t_indicator_data_p t2 , t_indicator_auth t3WHERE t1.id = t3.indicator_id AND t3.user_id = '0'--#{userId,jdbcType=VARCHAR} AND t1.sch_type = '0'--#{schType,jdbcType=VARCHAR} AND t1.ind_type = '0' AND t1.ind_date = (SELECT max(ind_date) FROM T_INDICATOR_DEF) AND t1.ind_code = t2.ind_code(+) AND t1.ind_date = t2.ind_date(+) AND t1.sch_type = t2.sch_type(+) --AND t1.ind_name LIKE CONCAT(CONCAT('%', #{indName,jdbcType=VARCHAR}), '%') AND ind_class_id = '1'--#{indClassId,jdbcType=VARCHAR}ORDER BY t1.ind_code----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT id, ind_code, ind_name, ind_type, sch_type, ind_date, data_code, ind_unit, trend_type, interval_max, interval_min, IND_TARGET, upper_limit, lower_limit, ind_formula, ind_class_id, remark, is_drillFROM t_indicator_def WHERE ind_code IN(SELECT t1.ind_codeFROM t_indicator_def t1, t_indicator_data_p t2 , t_indicator_auth t3WHERE t1.id = t3.indicator_id AND t3.user_id = '0'--#{userId,jdbcType=VARCHAR} AND t1.sch_type = '0'--#{schType,jdbcType=VARCHAR} AND t1.ind_type = '0' AND t1.ind_date = (SELECT max(ind_date) FROM T_INDICATOR_DEF) AND t1.ind_code = t2.ind_code(+) AND t1.ind_date = t2.ind_date(+) AND t1.sch_type = t2.sch_type(+) AND ind_class_id = '1'--#{indClassId,jdbcType=VARCHAR})ORDER BY ind_code----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT ind_code, ind_total, ind_avg, ind_max, ind_min, ind_last_year, ind_rankFROM t_indicator_data_pWHERE ind_code IN(SELECT t1.ind_codeFROM t_indicator_def t1, t_indicator_data_p t2 , t_indicator_auth t3WHERE t1.id = t3.indicator_id AND t3.user_id = '0'--#{userId,jdbcType=VARCHAR} AND t1.sch_type = '0'--#{schType,jdbcType=VARCHAR} AND t1.ind_type = '0' AND t1.ind_date = (SELECT max(ind_date) FROM T_INDICATOR_DEF) AND t1.ind_code = t2.ind_code(+) AND t1.ind_date = t2.ind_date(+) AND t1.sch_type = t2.sch_type(+) AND ind_class_id = '1'--#{indClassId,jdbcType=VARCHAR})ORDER BY ind_code----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------UPDATE t_indicator_data_pSET ind_avg = ind_total * DBMS_RANDOM.VALUE, ind_max = ind_total * DBMS_RANDOM.VALUE, ind_min = ind_total * DBMS_RANDOM.VALUE, ind_last_year = ind_total * DBMS_RANDOM.VALUEWHERE ind_code IN( SELECT t1.ind_code FROM t_indicator_def t1, t_indicator_data_p t2 , t_indicator_auth t3 WHERE t1.id = t3.indicator_id AND t3.user_id = '0'--#{userId,jdbcType=VARCHAR} AND t1.sch_type = '0'--#{schType,jdbcType=VARCHAR} AND t1.ind_type = '0' AND t1.ind_date = (SELECT max(ind_date) FROM T_INDICATOR_DEF) AND t1.ind_code = t2.ind_code(+) AND t1.ind_date = t2.ind_date(+) AND t1.sch_type = t2.sch_type(+) AND ind_class_id = '1'--#{indClassId,jdbcType=VARCHAR})SELECT*FROM t_indicator_def t8, t_indicator_data_p t9 WHERE 1 = 1AND t8.ind_code = t9.ind_codeAND t8.ind_date = t9.ind_dateAND t8.sch_type = t9.sch_typeAND t8.ind_code IN( SELECT t1.ind_code FROM t_indicator_def t1, t_indicator_data_p t2 , t_indicator_auth t3 WHERE t1.id = t3.indicator_id AND t3.user_id = '0'--#{userId,jdbcType=VARCHAR} AND t1.sch_type = '0'--#{schType,jdbcType=VARCHAR} AND t1.ind_type = '0' AND t1.ind_date = (SELECT max(ind_date) FROM T_INDICATOR_DEF) AND t1.ind_code = t2.ind_code(+) AND t1.ind_date = t2.ind_date(+) AND t1.sch_type = t2.sch_type(+) AND ind_class_id = '1'--#{indClassId,jdbcType=VARCHAR})-----------------------------------------------------------------------------------------------UPDATE t_indicator_def t8SET t8.ind_target = (SELECT t.ind_avg FROM t_indicator_data_p t WHERE 1 = 1 AND t8.ind_code = t.ind_code AND t8.ind_date = t.ind_date AND t8.sch_type = t.sch_type), t8.upper_limit = (SELECT t.ind_max FROM t_indicator_data_p t WHERE 1 = 1 AND t8.ind_code = t.ind_code AND t8.ind_date = t.ind_date AND t8.sch_type = t.sch_type), t8.lower_limit = (SELECT t.ind_min FROM t_indicator_data_p t WHERE 1 = 1 AND t8.ind_code = t.ind_code AND t8.ind_date = t.ind_date AND t8.sch_type = t.sch_type)WHERE 1 = 1 AND t8.ind_code IN ( SELECT t1.ind_code FROM t_indicator_def t1, t_indicator_data_p t2 , t_indicator_auth t3 WHERE t1.id = t3.indicator_id AND t3.user_id = '0'--#{userId,jdbcType=VARCHAR} AND t1.sch_type = '0'--#{schType,jdbcType=VARCHAR} AND t1.ind_type = '0' AND t1.ind_date = (SELECT max(ind_date) FROM T_INDICATOR_DEF) AND t1.ind_code = t2.ind_code(+) AND t1.ind_date = t2.ind_date(+) AND t1.sch_type = t2.sch_type(+) AND ind_class_id = '1'--#{indClassId,jdbcType=VARCHAR} )-----------------------------------------------------------------------------------------------SELECT * FROM t_sys_user WHERE ACCOUNT IN ('manager', 'admin');SELECT * FROM t_indicator_auth WHERE user_id = '0';INSERT INTO t_indicator_auth SELECT id AS indicator_id, '0' AS user_id FROM t_indicator_def;INSERT INTO t_indicator_auth SELECT id AS indicator_id, '86561aa5a7ac49e7bff21bb05b7764fe' AS user_id FROM t_indicator_def;SELECT * FROM t_indicator_def WHERE ind_class_id = '1';UPDATE t_indicator_def SET ind_class_id = '1';SELECT * FROM t_indicator_def;SELECT * FROM t_indicator_data_p;SELECT * FROM t_indicator_auth;SELECT USERENV('language') FROM dual;阅读全文
0 0
