元素重复至少3次 [英] Element repeated at least 3 times

问题描述

是否有一种方法可以在不使用Count和Group By的情况下确定元素在列中是否至少存在3次?

Is there a way to tell if an element is at least 3 times in a column without using Count and Group By?

预期的答案是在SQL(mysql或oracle11g)中，其中 仅使用SELECT，FROM，WHERE和JOINS .当然，所有逻辑运算符和量词作为 RELCALAL CALCULUS WITH TUPLES 根本没有工具.

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.

元组示例:

{t:{name} | ∃s:{姓名，工资}(雇员w s.wage = 50.000∧t.name = s.name)}

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

在这里，使用元组的关系演算的局限性很明显.

没有CTE，没有分组依据，没有行工具，没有区别，没有计数，没有视图，没有创建，没有插入，没有更改.这些出色的SQL工具都不是.

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的原因是因为我将使用Tuples将其带到Relational Calculus，这不允许使用那些工具.

The reason why I don't want to use Count and Group By is because I'll take this to Relational Calculus with Tuples, which doesn't allow those tools.

例如:

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

Suppose there is a table ORDER (Id_article, Id_Provider) where both ID's are foreign keys.

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

Query: Get all the articles that were ordered at least 3 times.

让表ORDER为:

   Id_Article      Id_Provider

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

查询结果应仅为元素1，因为它是Id_Article中的3倍.

The result of the query should be only the element 1, as it is 3 times in Id_Article.

推荐答案

可能的解决方案:

让我们致电以下Ids

SELECT O1.id_article AS id FROM ORDER O1 JOIN ORDER O2 ON O1.id_article = O2.id_article MINUS SELECT O3.id_article AS id FROM ORDER O3 MINUS SELECT O3.id_article AS id FROM ORDER O3 MINUS SELECT O3.id_article AS id FROM ORDER O3 MINUS SELECT O3.id_article AS id FROM ORDER O3

SELECT O1.id_article AS id FROM ORDER O1 JOIN ORDER O2 ON O1.id_article = O2.id_article MINUS SELECT O3.id_article AS id FROM ORDER O3 MINUS SELECT O3.id_article AS id FROM ORDER O3 MINUS SELECT O3.id_article AS id FROM ORDER O3 MINUS SELECT O3.id_article AS id FROM ORDER O3

那么我们的关系方程是{ t : {id} | ∃ s : {id} ( Ids(s) ∧ t.id = s.id ) }

Then our relational equation is { t : {id} | ∃ s : {id} ( Ids(s) ∧ t.id = s.id ) }

如果您不能使用MINUS，那么这可能可以帮助

If you can't use MINUS, then maybe this can help Converting aggregate operators from SQL to relational algebra

这篇关于元素重复至少3次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！