sql 20170922

-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
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_rank
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 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_drill
FROM t_indicator_def 
WHERE 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}
)
ORDER BY ind_code


-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
SELECT
    ind_code,
    ind_total,
    ind_avg,
    ind_max,
    ind_min,
    ind_last_year,
    ind_rank
FROM t_indicator_data_p
WHERE 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}
)
ORDER BY ind_code

-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
UPDATE t_indicator_data_p
SET 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.VALUE
WHERE 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 = 1
	AND t8.ind_code = t9.ind_code
	AND t8.ind_date = t9.ind_date
	AND t8.sch_type = t9.sch_type
	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}
	)

-----------------------------------------------------------------------------------------------
UPDATE 
    t_indicator_def t8
SET 
    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;