从Oracle表获取价值计数 [英] Geting value count from an Oracle Table

问题描述

我有一个表，其中包含员工.由于我所工作的公司很大(员工超过3000名)，这很自然，其中一些人名字相同.现在，可以通过用户名来区分他们，但是由于网页需要与所有这些用户一起下拉，因此我需要在其名称中添加一些额外的数据.

I have a table, that contains employees. Since the company I'm working for is quite big (>3k employees) It is only natural, that some of them have the same names. Now they can be differentiated by their usernames, but since a webpage needs a drop-down with all of these users, I need to add some extra data to their names.

我知道我可以先抓住所有用户，然后通过foreach运行它们，然后为每个用户对象添加一个计数.但是那将是非常无效的.因此，我需要一个良好的SQL查询，该查询将执行以下操作.子查询可以成为我需要的东西吗?

I know I could first grab all of the users and then run them through a foreach and add a count to each of the user objects. That would be quite ineffective though. Therefore I'm in need of a good SQL query, that would do something like this. Could a sub-query be the thing I need?

我的桌子看起来像这样:

My Table looks something like this:

name ----- surname ----- username

John       Mayer         jmaye

Suzan      Harvey        sharv

John       Mayer         jmay3

现在，如果查询返回相同的3个字段，并且如果有多个具有相同姓名和姓氏的人返回布尔值，那么我认为这很好.

Now what I think would be great, if the query returned the same 3 fields and also a boolean if there is more than one person with the same name and surname combination.

推荐答案

在Daniel的答案中添加标记...

Adding the flag to Daniel's answer...

SELECT NAME, SURNAME, USERNAME, DECODE(COUNT(*) OVER (PARTITION BY NAME, SURNAME), 1, 'N', 'Y')
FROM
YOUR_TABLE;

请注意，Oracle SQL不支持布尔值(叹息...)

Please note that Oracle SQL has no support for booleans (sigh...)

这篇关于从Oracle表获取价值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！