oracle数据库语句积累

1、从一个表选出数据更新另一个表（后面的exists一定要加）

update jqhdzt

   set shid =

       (select shid

          from v_plat_userjqinfo t

         where jqhdzt.jqbh = t.JQBH

           and jqhdzt.shid <> t.SHID)

 where shid = ''

   and exists (select 1

          from v_plat_userjqinfo t

         where jqhdzt.jqbh = t.JQBH

           and jqhdzt.shid <> t.SHID)

2、查询字段值相同的记录总数量

1）单个字段查询

select t.name,count(1) from tuser t   having count(1)>1  group by t.name

2）多个字段查询

SELECT t.spbh,t.spmc,t.shid,COUNT(*) FROM spxx t GROUP BY t.spbh,t.spmc,t.shid HAVING COUNT(*) > 1