entityMap|blocks|key|eah2q|text|您可以使用subquery：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|buiia|select+u.*
from+uniques+u
where+not+exists+(select+1+from+uniques+u1+where+u1.number+=+u.number+and+u1.desc+<>+u.desc);|code-block|syntax|javascript|fq2c1^0|5|8|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@$A|N|B|O|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|P|9|@]|E|@]|F|$J|K]]|$2|L|4|-4|6|7|8|Q|9|@]|E|@]|F|$]]]]

You can use <code>subquery</code> :

<pre><code>select u.*
from uniques u
where not exists (select 1 from uniques u1 where u1.number = u.number and u1.desc &lt;&gt; u.desc);
</code></pre>

entityMap|0|type|IMAGE|mutability|IMMUTABLE|data|imageUrl|https://i.stack.imgur.com/dkn9I.png|imageAlt|blocks|key|7pvqs|text|我觉得你已经很接近了|unstyled|depth|inlineStyleRanges|entityRanges|dofu4|SELECT+*+FROM+UNIQUES+
WHERE+NUMBER+IN+(SELECT+NUMBER+FROM+
UNIQUES+GROUP+BY+NUMBER+HAVING+COUNT(NUMBER)=1)|code-block|syntax|javascript|6frui|你想要：|biln2|ID++++++DESC++++++DATE++++++++++NUMBER+++++++++AMOUNT
100+++++TEST3+++++01-03-18++++++1009942++++++++156.000+
100+++++TEST6+++++05-20-18++++++1011055++++++++1564.0+|f65ro|我的回答是：|3keu8|​|4vaim|?|atomic|offset|length|6jo5u|2b4a0|有几种很好的方法可以做到这一点，我只是根据这个问题采用最简单的方法，只需将其从NOT+IN改为IN|style|CODE|2s20f^0|0|0|0|0|0|0|0|1|0|0|0|13|6|1B|2|0^^$0|$1|$2|3|4|5|6|$7|8|9|-4]]]|A|@$B|C|D|E|2|F|G|17|H|@]|I|@]|6|$]]|$B|J|D|K|2|L|G|18|H|@]|I|@]|6|$M|N]]|$B|O|D|P|2|F|G|19|H|@]|I|@]|6|$]]|$B|Q|D|R|2|L|G|1A|H|@]|I|@]|6|$M|N]]|$B|S|D|T|2|F|G|1B|H|@]|I|@]|6|$]]|$B|U|D|V|2|F|G|1C|H|@]|I|@]|6|$]]|$B|W|D|X|2|Y|G|1D|H|@]|I|@$Z|1E|10|1F|B|1G]]|6|$]]|$B|11|D|V|2|F|G|1H|H|@]|I|@]|6|$]]|$B|12|D|13|2|F|G|1I|H|@$Z|1J|10|1K|14|15]|$Z|1L|10|1M|14|15]]|I|@]|6|$]]|$B|16|D|-4|2|F|G|1N|H|@]|I|@]|6|$]]]]

I think you are pretty close

<pre><code>SELECT * FROM UNIQUES 
WHERE NUMBER IN (SELECT NUMBER FROM 
UNIQUES GROUP BY NUMBER HAVING COUNT(NUMBER)=1)
</code></pre>

And you want:

<pre><code>ID DESC DATE NUMBER AMOUNT
100 TEST3 01-03-18 1009942 156.000 
100 TEST6 05-20-18 1011055 1564.0 
</code></pre>

And my answer gives:

<a href="https://i.stack.imgur.com/dkn9I.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dkn9I.png" alt="enter image description here"></a>

There are several good ways to do this, I'm just going with the simplest based upon the question by just changing it from <code>NOT IN</code> to <code>IN</code>

entityMap|blocks|key|og4r|text|SELECT+SRC.ID,+SRC.DESC,+SRC.DATE,+SRC.NUMBER,+SRC.Amount
FROM+UNIQUES+AS+SRC

INNER+JOIN+(SELECT+NUMBER+FROM+UNIQUES+GROUP+BY+NUMBER+HAVING+COUNT(*)=1)+AS+NonDupe+
ON+SRC.Number+=+NonDupe.Number|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2sp12|我会避免使用列名称，例如DESC、DATE和NUMBER，因为它们出现在SQL和ODBC保留字列表中。|unstyled|abe30^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@]|A|@]|B|$C|D]]|$2|E|4|F|6|G|8|J|9|@]|A|@]|B|$]]|$2|H|4|-4|6|G|8|K|9|@]|A|@]|B|$]]]]

<pre><code>SELECT SRC.ID, SRC.DESC, SRC.DATE, SRC.NUMBER, SRC.Amount
FROM UNIQUES AS SRC

INNER JOIN (SELECT NUMBER FROM UNIQUES GROUP BY NUMBER HAVING COUNT(*)=1) AS NonDupe 
ON SRC.Number = NonDupe.Number
</code></pre>

I would avoid using column names such as DESC, DATE and NUMBER as they occur in the list of SQL and ODBC reserved words.

entityMap|blocks|key|1c7vf|text|除了not+exists方法之外，您还可以将表复制到自身，并检查“outer+join”表中的空值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|6cfd2|select+*
from
++++uniques+u
++++left+outer+join+uniques+dup
++++++++on+(u.NUMBER=dup.NUMBER)
where
++++dup.NUMBER+is+null|code-block|syntax|javascript|eg9t2|这是因为left+outer+join将返回"left“表(上面的别名为u+)中的所有内容，并且如果没有找到符合null条件的行，它将返回"right”表中的dup值(上面的别名为join+on+)。|376dt^0|2|A|X|A|0|0|10|1|1K|4|27|3|2I|7|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@$A|P|B|Q|C|D]|$A|R|B|S|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|T|9|@]|E|@]|F|$J|K]]|$2|L|4|M|6|7|8|U|9|@$A|V|B|W|C|D]|$A|X|B|Y|C|D]|$A|Z|B|10|C|D]|$A|11|B|12|C|D]]|E|@]|F|$]]|$2|N|4|-4|6|7|8|13|9|@]|E|@]|F|$]]]]

Aside from the <code>not exists</code> method, you can <code>outer join</code> the table to itself, and check for nulls in the "duplicate" table.

<pre><code>select *
from
 uniques u
 left outer join uniques dup
 on (u.NUMBER=dup.NUMBER)
where
 dup.NUMBER is null
</code></pre>

This works because the left outer join will return everything from the "left" table (aliased to <code>u</code> above), and it will return <code>null</code> values from the "right" table (aliased to <code>dup</code> above) if there is no row found with the <code>join on</code> criteria.

entityMap|blocks|key|15p62|text|我只会使用窗口函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|feuhh|select+u.*
from+(select+u.*,+count(*)+over+(partition+by+number)+as+cnt
++++++from+uniques+u
+++++)+u
where+cnt+=+1;|code-block|syntax|javascript|bjvk9^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|J|9|@]|A|@]|B|$F|G]]|$2|H|4|-4|6|7|8|K|9|@]|A|@]|B|$]]]]

I would just use window functions:

<pre><code>select u.*
from (select u.*, count(*) over (partition by number) as cnt
 from uniques u
 ) u
where cnt = 1;
</code></pre>

I have a table UNIQUES like :

<pre><code>ID DESC DATE NUMBER Amount
100 TEST1 01-01-18 1009674 10.20 
100 TEST2 01-02-18 1009674 245.10
100 TEST3 01-03-18 1009942 156.000 
100 TEST4 02-14-18 00042EX 154.6
100 TEST5 04-15-18 00042EX 25.10
100 TEST6 05-20-18 1011055 1564.0
</code></pre>

And I would like to get the unique records where the Number field has not duplicate or is not repeating.

Result expected:

<pre><code>ID DESC DATE NUMBER AMOUNT
100 TEST3 01-03-18 1009942 156.000 
100 TEST6 05-20-18 1011055 1564.0 
</code></pre>

Query I'm using:

<pre><code>SELECT * FROM UNIQUES 
WHERE NUMBER NOT IN (SELECT NUMBER FROM 
UNIQUES GROUP BY NUMBER HAVING COUNT(NUMBER)=1)
</code></pre>

Any assistance or help would be really appreciated.

Retrieve Unique records SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我有一个表的唯一性，如下所示： ID        DESC      DATE         NUMBER        Amount100       TEST1     01-01-18     1009674       10.20 100       TEST2     01-02-18     1009...

问检索唯一记录SQL
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问检索唯一记录SQLEN