mysql - 元素重复至少 3 次

Question

有没有办法在不使用 Count 和 Group By 的情况下判断一个元素是否在列中至少出现 3 次？

EDIT: The answer is expected in SQL (mysql or oracle11g) in which only SELECT, FROM, WHERE and JOINS are used. And of course, all the logical operators and quantifiers as RELATIONAL CALCULUS WITH TUPLES has NO tools at all.

Example of tuples:

{ t : {name} | ∃ s : {name, wage} ( Employee(s) ∧ s.wage = 50.000 ∧ t.name = s.name ) }

here, the limitations of Relational Calculus with tuples are clear.

No CTE's, no group by, no row tools, no distinct, no count, no views, no create, no insert, no alter. None of those awesome SQL tools.

我不想使用 Count 和 Group By 的原因是因为我会把它带到 Relational Calculus with Tuples，它不允许使用这些工具。

举个例子:

假设有一个表 ORDER (Id_article, Id_Provider)，其中两个 ID 都是外键。

查询:获取至少被订购3次的所有文章。

令表 ORDER 为:

   Id_Article      Id_Provider

       1               A
       1               B
       1               B
       2               C
       2               C
       3               A

查询结果应该只有元素1，在Id_Article中出现了3次。

Answer 1

向下知道这是否回答了您的问题。但是 oracle 有一个 row_number 函数

在这种情况下，partition_by 对每篇文章重新开始计数。

SQL Fiddle Demo

WITH CTE AS (
  SELECT    "Id_Article", 
            "Id_Provider", 
            ROW_NUMBER() OVER 
            (PARTITION BY "Id_Article" ORDER BY "Id_Provider")  AS rn
      FROM  Table1
)     
SELECT DISTINCT "Id_Article"
FROM CTE
WHERE rn >= 3;