Skip to content

Commit 9573b17

Browse files
hyhcoderhyhcoder
committed
Site updated: 2018-10-14 22:55:21
1 parent 70a2986 commit 9573b17

File tree

77 files changed

+482
-938
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

77 files changed

+482
-938
lines changed

‎2014/03/19/Oracle中删除重复记录整理/index.html

Lines changed: 8 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -90,7 +90,8 @@
9090
<meta property="og:url" content="https://www.hyhcoder.com/2014/03/19/Oracle中删除重复记录整理/index.html">
9191
<meta property="og:site_name" content="hyhcoder的博客">
9292
<meta property="og:description" content="Oracle中删除重复记录整理Oracle中经常会删除一些重复记录，整理一下以备用 举例(建立数据如下): 12345678910create table t_table (id NUMBER, name VARCHAR2(20) );insert into t_table values (1234, &apos;abc&apos;); insert into t_table values (1234, &apos;a">
93-
<meta property="og:updated_time" content="2017-06-18T08:56:40.911Z">
93+
<meta property="og:locale" content="zh-Hans">
94+
<meta property="og:updated_time" content="2017-06-18T08:56:40.912Z">
9495
<meta name="twitter:card" content="summary">
9596
<meta name="twitter:title" content="Oracle中删除重复记录整理">
9697
<meta name="twitter:description" content="Oracle中删除重复记录整理Oracle中经常会删除一些重复记录，整理一下以备用 举例(建立数据如下): 12345678910create table t_table (id NUMBER, name VARCHAR2(20) );insert into t_table values (1234, &apos;abc&apos;); insert into t_table values (1234, &apos;a">
@@ -329,7 +330,7 @@ <h1 class="post-title" itemprop="name headline">Oracle中删除重复记录整
329330

330331
<span class="post-meta-item-text">发表于</span>
331332

332-
<time title="创建于" itemprop="dateCreated datePublished" datetime="2014-03-19T16:28:36+08:00">
333+
<time title="创建于" itemprop="dateCreated datePublished" datetime="2014-03-19T16:28:36+09:00">
333334
2014-03-19
334335
</time>
335336

@@ -379,18 +380,18 @@ <h1 class="post-title" itemprop="name headline">Oracle中删除重复记录整
379380

380381
<h1 id="Oracle中删除重复记录整理"><a href="#Oracle中删除重复记录整理" class="headerlink" title="Oracle中删除重复记录整理"></a>Oracle中删除重复记录整理</h1><p>Oracle中经常会删除一些重复记录，整理一下以备用</p>
381382
<p>举例(建立数据如下):</p>
382-
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_table </div><div class="line">(<span class="keyword">id</span> <span class="built_in">NUMBER</span>, </div><div class="line"><span class="keyword">name</span> VARCHAR2(<span class="number">20</span>) </div><div class="line">);</div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">1234</span>, <span class="string">'abc'</span>); </div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">1234</span>, <span class="string">'abc'</span>); </div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">1234</span>, <span class="string">'abc'</span>); </div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">3456</span>, <span class="string">'bcd'</span>); </div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">3456</span>, <span class="string">'bcd'</span>); </div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">7890</span>, <span class="string">'cde'</span>);</div></pre></td></tr></table></figure>
383+
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_table </span><br><span class="line">(<span class="keyword">id</span> <span class="built_in">NUMBER</span>, </span><br><span class="line"><span class="keyword">name</span> VARCHAR2(<span class="number">20</span>) </span><br><span class="line">);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">1234</span>, <span class="string">'abc'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">1234</span>, <span class="string">'abc'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">1234</span>, <span class="string">'abc'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">3456</span>, <span class="string">'bcd'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">3456</span>, <span class="string">'bcd'</span>); </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">values</span> (<span class="number">7890</span>, <span class="string">'cde'</span>);</span><br></pre></td></tr></table></figure>
383384
<p>1 .<strong>第一种方法:</strong>适用于有少量重复记录的情况(临时表法)</p>
384385
<ul>
385386
<li>(建一个临时表用来存放重复的记录)</li>
386387
<li>(清空表的数据，但保留表的结构)</li>
387388
<li>(再将临时表里的内容反插回来)</li>
388389
</ul>
389-
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">create</span> <span class="keyword">table</span> tmp_table <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">distinct</span> * <span class="keyword">from</span> t_table; </div><div class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> t_table; </div><div class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">select</span> * <span class="keyword">from</span> tmp_table;</div></pre></td></tr></table></figure>
390+
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> tmp_table <span class="keyword">as</span> <span class="keyword">select</span> <span class="keyword">distinct</span> * <span class="keyword">from</span> t_table; </span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> t_table; </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_table <span class="keyword">select</span> * <span class="keyword">from</span> tmp_table;</span><br></pre></td></tr></table></figure>
390391
<p>2 .<strong>第二种方法:</strong>适用于有大量重复记录的情况</p>
391-
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">delete</span> t_table <span class="keyword">where</span> </div><div class="line">(<span class="keyword">id</span>,<span class="keyword">name</span>) <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">id</span>,<span class="keyword">name</span> </div><div class="line"><span class="keyword">from</span> t_table <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">id</span>,<span class="keyword">name</span> <span class="keyword">having</span> <span class="keyword">count</span>(*)&gt;<span class="number">1</span>) </div><div class="line"><span class="keyword">and</span> </div><div class="line"><span class="keyword">rowid</span> <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">min</span>(<span class="keyword">rowid</span>) </div><div class="line"><span class="keyword">from</span> t_table <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">id</span>,<span class="keyword">name</span> <span class="keyword">having</span> <span class="keyword">count</span>(*)&gt;<span class="number">1</span>);</div></pre></td></tr></table></figure>
392+
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> t_table <span class="keyword">where</span> </span><br><span class="line">(<span class="keyword">id</span>,<span class="keyword">name</span>) <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">id</span>,<span class="keyword">name</span> </span><br><span class="line"><span class="keyword">from</span> t_table <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">id</span>,<span class="keyword">name</span> <span class="keyword">having</span> <span class="keyword">count</span>(*)&gt;<span class="number">1</span>) </span><br><span class="line"><span class="keyword">and</span> </span><br><span class="line"><span class="keyword">rowid</span> <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">min</span>(<span class="keyword">rowid</span>) </span><br><span class="line"><span class="keyword">from</span> t_table <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">id</span>,<span class="keyword">name</span> <span class="keyword">having</span> <span class="keyword">count</span>(*)&gt;<span class="number">1</span>);</span><br></pre></td></tr></table></figure>
392393
<p>3 .<strong>第三种方法:</strong>适用于有少量重复记录的情况</p>
393-
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">delete</span> <span class="keyword">from</span> t_table a <span class="keyword">where</span> a.rowid!=(<span class="keyword">select</span> <span class="keyword">max</span>(b.rowid) </div><div class="line"><span class="keyword">from</span> t_table b <span class="keyword">where</span> a.id=b.id <span class="keyword">and</span> a.name=b.name);</div></pre></td></tr></table></figure>
394+
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> t_table a <span class="keyword">where</span> a.rowid!=(<span class="keyword">select</span> <span class="keyword">max</span>(b.rowid) </span><br><span class="line"><span class="keyword">from</span> t_table b <span class="keyword">where</span> a.id=b.id <span class="keyword">and</span> a.name=b.name);</span><br></pre></td></tr></table></figure>
394395

395396

396397
</div>
@@ -695,8 +696,6 @@ <h1 id="Oracle中删除重复记录整理"><a href="#Oracle中删除重复记录
695696

696697

697698

698-
699-
700699

701700

702701

@@ -737,11 +736,6 @@ <h1 id="Oracle中删除重复记录整理"><a href="#Oracle中删除重复记录
737736
<script type="text/javascript" src="//cdn.jsdelivr.net/fancybox/2.1.5/jquery.fancybox.pack.js"></script>
738737

739738

740-
741-
742-
<script type="text/javascript" src="/lib/canvas-nest/canvas-nest.min.js"></script>
743-
744-
745739

746740

747741

@@ -854,7 +848,7 @@ <h1 id="Oracle中删除重复记录整理"><a href="#Oracle中删除重复记录
854848
var isfetched = false;
855849
var isXml = true;
856850
// Search DB path;
857-
var search_path = "search.json";
851+
var search_path = "";
858852
if (search_path.length === 0) {
859853
search_path = "search.xml";
860854
} else if (/json$/i.test(search_path)) {

‎2014/05/15/Oracle数据库服务总结/index.html

Lines changed: 5 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,7 @@
9090
<meta property="og:url" content="https://www.hyhcoder.com/2014/05/15/Oracle数据库服务总结/index.html">
9191
<meta property="og:site_name" content="hyhcoder的博客">
9292
<meta property="og:description" content="Oracle数据库服务总结Oracle的数据库服务默认有5个看了几篇文章后,总结其作用如下:1 .OracleServiceORCL:数据库服务，这个服务会自动的启动和停止数据库。ORCL是Oracle的实例标识。此服务被默认的设置为开机启动。 必须启动，这是Oracle数据库的服务。 2 .OracleOraDb11g_home1TNSListener.监听器服务，服务只有在数据库需要远程访">
93+
<meta property="og:locale" content="zh-Hans">
9394
<meta property="og:updated_time" content="2017-06-18T08:55:42.903Z">
9495
<meta name="twitter:card" content="summary">
9596
<meta name="twitter:title" content="Oracle数据库服务总结">
@@ -329,7 +330,7 @@ <h1 class="post-title" itemprop="name headline">Oracle数据库服务总结</h1>
329330

330331
<span class="post-meta-item-text">发表于</span>
331332

332-
<time title="创建于" itemprop="dateCreated datePublished" datetime="2014-05-15T16:28:56+08:00">
333+
<time title="创建于" itemprop="dateCreated datePublished" datetime="2014-05-15T16:28:56+09:00">
333334
2014-05-15
334335
</time>
335336

@@ -410,11 +411,11 @@ <h2 id="特别注意"><a href="#特别注意" class="headerlink" title="特别
410411
<li>建立dbstart.cmd文件（开启）</li>
411412
<li>添加如下内容：</li>
412413
</ul>
413-
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">@echo　off </div><div class="line">net　　start　　OracleServiceORACLE </div><div class="line">net　　start　　OracleDBConsoleoracle </div><div class="line">net　　start　　OracleOraDb10g_home1iSQL*Plus </div><div class="line">net　　start　　OracleOraDb10g_home1TNSListener </div><div class="line">pause</div></pre></td></tr></table></figure>
414+
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">@echo　off </span><br><span class="line">net　　start　　OracleServiceORACLE </span><br><span class="line">net　　start　　OracleDBConsoleoracle </span><br><span class="line">net　　start　　OracleOraDb10g_home1iSQL*Plus </span><br><span class="line">net　　start　　OracleOraDb10g_home1TNSListener </span><br><span class="line">pause</span><br></pre></td></tr></table></figure>
414415
<ul>
415416
<li>同样我们可以建立关闭文件（dbstop.cmd）</li>
416417
</ul>
417-
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">@echo　off </div><div class="line">net　　stop　　OracleServiceORACLE </div><div class="line">net　　stop　　OracleDBConsoleoracle </div><div class="line">net　　stop　　OracleOraDb10g_home1iSQL*Plus </div><div class="line">net　　stop　　OracleOraDb10g_home1TNSListener </div><div class="line">pause</div></pre></td></tr></table></figure>
418+
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">@echo　off </span><br><span class="line">net　　stop　　OracleServiceORACLE </span><br><span class="line">net　　stop　　OracleDBConsoleoracle </span><br><span class="line">net　　stop　　OracleOraDb10g_home1iSQL*Plus </span><br><span class="line">net　　stop　　OracleOraDb10g_home1TNSListener </span><br><span class="line">pause</span><br></pre></td></tr></table></figure>
418419

419420

420421
</div>
@@ -723,8 +724,6 @@ <h2 id="特别注意"><a href="#特别注意" class="headerlink" title="特别
723724

724725

725726

726-
727-
728727

729728

730729

@@ -765,11 +764,6 @@ <h2 id="特别注意"><a href="#特别注意" class="headerlink" title="特别
765764
<script type="text/javascript" src="//cdn.jsdelivr.net/fancybox/2.1.5/jquery.fancybox.pack.js"></script>
766765

767766

768-
769-
770-
<script type="text/javascript" src="/lib/canvas-nest/canvas-nest.min.js"></script>
771-
772-
773767

774768

775769

@@ -882,7 +876,7 @@ <h2 id="特别注意"><a href="#特别注意" class="headerlink" title="特别
882876
var isfetched = false;
883877
var isXml = true;
884878
// Search DB path;
885-
var search_path = "search.json";
879+
var search_path = "";
886880
if (search_path.length === 0) {
887881
search_path = "search.xml";
888882
} else if (/json$/i.test(search_path)) {

0 commit comments

Comments
 (0)