SQL如下
select
BS_CASE_FILE.OID, BS_CASE_FILE.CASE_SOURCE, BS_CASE_FILE.CASE_TYPE, BS_CASE_FILE.OPERATOR, BS_CASE_FILE.BIG_TYPE, a.TYPE_NAME as BIG_TYPE_NAME,
BS_CASE_FILE.SMALL_TYPE, b.TYPE_NAME as SML_TYPE_NAME, BS_CASE_FILE.CASE_AREA, BS_CASE_FILE.STREET, BS_CASE_FILE.COMMUNITY, BS_COMMUNITY_STREET.NAME as COMMUNITY_NAME, BS_CASE_FILE.GRID, BS_CASE_FILE.POS_DESC, BS_CASE_FILE.STAND_POS_DESC, BS_CASE_FILE.PROB_DESC, BS_CASE_FILE.FINDER, BS_CASE_FILE.CONTACT,
BS_CASE_FILE.STAT, BS_CASE_FILE.POS_X, BS_CASE_FILE.POS_Y, BS_CASE_FILE.REPORT_TIME, BS_CASE_FILE.COMPID, BS_CASE_FILE.USERID, BS_CASE_FILE.VERIFY_DESC, BS_STREET.STREET_NAME,
BS_CASE_FILE.DAMG_DESC, BS_CASE_FILE.TASK_NUM, BS_CASE_FILE.VERIFY_START_TIME, BS_CASE_FILE.VERIFY_END_TIME, BS_DUTY_AREA.AREA_NAME, FDT_CASE_REPORT_FILE.FDT_CASE_REPORT_FILE_ID,
FDT_CASE_REPORT_FILE.HANDLE_OPINITION, FDT_CASE_REPORT_FILE.CHECK_END_TIME, FDT_CASE_REPORT_FILE.CHECK_BEGIN_TIME, FDT_CASE_REPORT_FILE.CHECK_FLAG, FDT_CASE_REPORT_FILE.CHECK_MEMO
from BS_CASE_FILE
left join BS_TYPE a on a.ID=BS_CASE_FILE.BIG_TYPE and a.CASE_FLAG=BS_CASE_FILE.CASE_TYPE
left join BS_TYPE b on b.ID=BS_CASE_FILE.SMALL_TYPE and b.CASE_FLAG=BS_CASE_FILE.CASE_TYPE
left join BS_GRID on BS_GRID.UNIT_CODE=BS_CASE_FILE.GRID
left join BS_DUTY_AREA on BS_DUTY_AREA.OID=BS_GRID.BS_DUTY_AREA_OID
left join BS_COMMUNITY_STREET on
BS_COMMUNITY_STREET.ID=BS_CASE_FILE.COMMUNITY left join BS_STREET on BS_STREET.ID=BS_CASE_FILE.STREET
left join BS_CITY_AREA on BS_CITY_AREA.AREA_CODE=BS_CASE_FILE.CASE_AREA
left join FDT_CASE_REPORT_FILE on FDT_CASE_REPORT_FILE.REPORT_OID=BS_CASE_FILE.OID
where 0=0 and BS_CASE_FILE.CASE_SOURCE='1'
select
BS_CASE_FILE.OID, BS_CASE_FILE.CASE_SOURCE, BS_CASE_FILE.CASE_TYPE, BS_CASE_FILE.OPERATOR, BS_CASE_FILE.BIG_TYPE, a.TYPE_NAME as BIG_TYPE_NAME,
BS_CASE_FILE.SMALL_TYPE, b.TYPE_NAME as SML_TYPE_NAME, BS_CASE_FILE.CASE_AREA, BS_CASE_FILE.STREET, BS_CASE_FILE.COMMUNITY, BS_COMMUNITY_STREET.NAME as COMMUNITY_NAME, BS_CASE_FILE.GRID, BS_CASE_FILE.POS_DESC, BS_CASE_FILE.STAND_POS_DESC, BS_CASE_FILE.PROB_DESC, BS_CASE_FILE.FINDER, BS_CASE_FILE.CONTACT,
BS_CASE_FILE.STAT, BS_CASE_FILE.POS_X, BS_CASE_FILE.POS_Y, BS_CASE_FILE.REPORT_TIME, BS_CASE_FILE.COMPID, BS_CASE_FILE.USERID, BS_CASE_FILE.VERIFY_DESC, BS_STREET.STREET_NAME,
BS_CASE_FILE.DAMG_DESC, BS_CASE_FILE.TASK_NUM, BS_CASE_FILE.VERIFY_START_TIME, BS_CASE_FILE.VERIFY_END_TIME, BS_DUTY_AREA.AREA_NAME, FDT_CASE_REPORT_FILE.FDT_CASE_REPORT_FILE_ID,
FDT_CASE_REPORT_FILE.HANDLE_OPINITION, FDT_CASE_REPORT_FILE.CHECK_END_TIME, FDT_CASE_REPORT_FILE.CHECK_BEGIN_TIME, FDT_CASE_REPORT_FILE.CHECK_FLAG, FDT_CASE_REPORT_FILE.CHECK_MEMO
from BS_CASE_FILE
left join BS_TYPE a on a.ID=BS_CASE_FILE.BIG_TYPE and a.CASE_FLAG=BS_CASE_FILE.CASE_TYPE
left join BS_TYPE b on b.ID=BS_CASE_FILE.SMALL_TYPE and b.CASE_FLAG=BS_CASE_FILE.CASE_TYPE
left join BS_GRID on BS_GRID.UNIT_CODE=BS_CASE_FILE.GRID
left join BS_DUTY_AREA on BS_DUTY_AREA.OID=BS_GRID.BS_DUTY_AREA_OID
left join BS_COMMUNITY_STREET on
BS_COMMUNITY_STREET.ID=BS_CASE_FILE.COMMUNITY left join BS_STREET on BS_STREET.ID=BS_CASE_FILE.STREET
left join BS_CITY_AREA on BS_CITY_AREA.AREA_CODE=BS_CASE_FILE.CASE_AREA
left join FDT_CASE_REPORT_FILE on FDT_CASE_REPORT_FILE.REPORT_OID=BS_CASE_FILE.OID
where 0=0 and BS_CASE_FILE.CASE_SOURCE='1'
如果数据是
1 123
1 123
才会有效,如果是
1 123
1 234
一样是无效的。
要看看你要如何去重了,是要一个字段去重还是全部都要不同。
法一:如果只用一条语句实现,通过group by,可能还要结合子查询
法二:如果只用一条语句实现,通过distinct,可能还要结合子查询
法三:通过存储过程,先插入不重复的关键字段,然后分别更新其它字段
1.你可以适当的用Distinct关键字.
2.你可以用分析函数row_number() over(...),rank() over(...),dense_rank() over(...);
e.g:--example1
select distinct a.job,a.deptno
from scott.emp a;
--example2
select a.job,a.deptno
from scott.emp a
group by a.job,a.deptno;
--example3
select b.job,b.deptno
from(
select a.job,a.deptno,row_number() over(partition by a.job,a.deptno order by a.empno) rn
from scott.emp a
) b
where b.rn=1;
--result as follows:
3.你还可以用group by.