去重

1、select distinct *from teachers

2、select  teacher_id,

             min(name),

             min(id_no)

      from teachers  group by teachers_id

3、select *from  teacher a

      where (a.teacher_id, a.id_no) in

      (

      select b.teacher_id,MIN(b.id_no) id_no

       from teacher b

       group by  b.teacher_id

)

 

 

 

以日期GROUP BY分组查询并作为条件关联3个子查询，子查询分别对应T1/T2/T3，日期是唯一关联条件，一环扣一环，但是如果出现其中某个表没有某一天的数据，而其他表有，比如某一天T1、T3有结果值，但是T2是个空集，链条就断了，就会同一天出现两条数据即重复日期的结果。这时只要在查询结果外面再加一层查询（如红色标记），就能很好的解决FULL OUTER JOIN语句的去重问题了！

SELECT substr(st.sdd,0,8),sum(st.sbks),sum(st.sbs),sum(st.sje),sum(st.sjc)
FROM ( SELECT
(T1.dd||T2.dd||T3.dd) sdd, T1.bks sbks, T2.bs sbs, T2.je sje, T3.jc sjc
FROM ( SELECT

create_date dd, COUNT(*) bks
FROM TBL_TCPAY WHERE
create_date >='20180806' AND create_date<='20180816'
AND status = '12' GROUP BY create_date ORDER BY 1) T1
FULL OUTER JOIN
( SELECT
localdate dd, COUNT(*) bs, SUM(amount)/100 je
FROM TBL_ATPAY WHERE

localdate >='20180801' AND localdate <='20180905'
AND status = '0' GROUP BY localdate ORDER BY 1) T2
ON T1.dd = T2.dd
FULL OUTER JOIN
( SELECT
bind_date dd , COUNT(*) jc
FROM TBL_COMPAY WHERE

bind_date >='20180806' AND bind_date <='20180816'
AND comresource='2' GROUP BY bind_date ORDER BY 1 ) T3
ON T2.dd = T3.dd ORDER BY 1 ) st
GROUP BY substr(st.sdd,0,8) ORDER BY 1;