sql with ties_SQL WITH TIES子句

sql with ties

SQL | 带领带条款 (SQL | WITH TIES Clause)

WITH TIES clause is newly added to the oracle 12c software. So, to understand the concept we will take an example.

WITH TIES子句是新添加到oracle 12c软件中的。 因此，为了理解这一概念，我们将举一个例子。

Before that just have a look at the syntax:

在此之前，请看一下语法：

    SELECT TOP (<number_of_rows>) WITH TIES <column_name>/*(for_all_columns)
    FROM <table_name>
    ORDER BY <column_name> <order(ASC/DESC)>;

Here, ASC stands for Ascending order and DESC stands for Descending order.

在此， ASC代表升序， DESC代表降序。

Example:

例：

Suppose we have 10 runners in the race and we have only 3 prizes to give away 1st, 2nd & 3rd respectively. But the situation arises that two runners finish the race exactly at the same time and are eligible for the 3rd position prize.

假设我们有10名运动员参加比赛，而我们只有3个奖项分别颁发第一，第二和第三名。 但是出现了这样的情况，两个跑步者恰好同时完成了比赛，并且有资格获得第三名。

To overcome this problem SQL provides us with the clause known as WITH TIES clause.

为了克服这个问题，SQL为我们提供了称为WITH TIES子句的子句 。

To have a better understanding let's have the following example.

为了更好地理解，让我们看下面的例子。

SNO	NAME	RANK
1	Nitin	First
2	Prakash	Second
3	Parmeet	Third
4	Sunny	Third
5	Hassan	Fourth
6	Rithwik	Fifth

SNO	名称	秩
1个	尼丁	第一
2	普拉卡什	第二
3	帕尔梅特	第三
4	阳光明媚	第三
5	哈桑	第四
6	里思威克	第五

Now, if we want the names of the player having the first three position,

现在，如果我们希望玩家的名字具有前三个位置，

Query:

查询：

SELECT TOP(4) WITH TIES sno, name, rank
FROM mytable
ORDER BY rank ASC;

Output

输出量

SNO	NAME	RANK
1	Nitin	First
2	Prakash	Second
3	Parmeet	Third
4	Sunny	Third

SNO	名称	秩
1个	尼丁	第一
2	普拉卡什	第二
3	帕尔梅特	第三
4	阳光明媚	第三

Note:

注意：

• We get the tied line in our yield, just when we utilize the request by the provision in our Select explanation. Assume, if we won't use request by the proviso, and still, we are utilizing with ties statement, at that point we won't get the tied line in our yield and the inquiry carries on the same as if we are utilizing the ONLY condition rather than With Ties proviso.

  当我们通过选择说明中的规定利用请求时，我们就获得了收益上的纽带。 假设，如果我们不使用附加条件的请求，并且仍然使用ties语句，那我们将不会在收益率中获得束缚线，并且查询的进行与使用只有条件，而不是有条件。

• If it's not too much trouble ensure that, you run these inquiries in Oracle Database 12c, because Fetch provision is the recently included component in Oracle 12c, additionally With Ties, runs just in Oracle Database 12c, these questions won't run in beneath renditions of 12c like 10g or 11g.

  如果不是很麻烦，请确保在Oracle Database 12c中运行这些查询，因为Fetch提供是Oracle 12c中最近包含的组件，此外，有了Ties，它仅在Oracle Database 12c中运行，因此这些问题不会在解释下方显示。 12c之类的东西10g或11g。

翻译自: https://www.includehelp.com/sql/with-ties-clause.aspx

sql with ties