一个表中有三个标识符的SQL算法 [英] SQL algorithm with three identifiers from one table

问题描述

可能这将是很容易，但我不知道，如何从一个查询从我的DB获取必要的值。只是现在不能弄清楚。我将在CodeIginiter系统中进行此查询。

Probably this will be really easy, but I can't figure out, how to get necessary values from my DB with one query. Just can't figure it out now. I'm going to make this query inside CodeIginiter system.

表'信息'结构：

CREATE TABLE information (
    planid int(11) NOT NULL,
    production_nr int(11) NOT NULL,
    status int(11) NOT NULL
);

表'信息'内容：




必要的输出：
我想获得最好 - 只有一个查询，但如果不可能，那么多个）所有planid的其中：所有这个计划id的pruduction_nrs有状态> = 3。

Table 'information' content:


Necessary output: I would like to get (at the best - with only one query, but if its not possible, then with multiple) all planid's where: ALL of this plan id's pruduction_nrs has status >= 3.

case，我需要得到这些plandid的：2和5，因为每个planid的所有production_nrs有大于或等于3的状态。

In this case, I would need to get these plandid's: 2 and 5 because each of these planid's ALL production_nrs has status greater or equal than 3.

推荐答案

select planid, production_nr
from information inf1
where not exists (select 1 from information inf2
                  where inf1.planid = inf2.planid
                  and   status < 3)

您可以考虑修改select子句您的需求：

You might consider amending the select clause (first row) according to your needs:

• 添加不同（如果表格包含状态栏）

• Add distinct (if the table PK includes status column)

更改列列表

这篇关于一个表中有三个标识符的SQL算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！