mysql查询，嵌套where子句 [英] mysql query, nested where clauses

问题描述

我有一个有效的查询，该查询对表LDS中的所有不同值进行计数，其中STATUS ='OK'AND DATE> ='2012'并且如果有多个相同的ID，则如果此ID具有状态，则以最新的日期计算一个的确定":

I have a working query that counts all distinct values from table LDS where STATUS = 'OK' AND DATE >= '2012' AND if there are multiple identical IDs then count the one with the newest date if this id has a status of "ok":

COUNT(DISTINCT lds1.ID)
FROM 
    LDS lds1
    LEFT JOIN LDS lds2
        ON lds1.ID = lds2.ID
        AND lds1.Date < lds2.Date
        AND lds1.Status = 'ok'
WHERE 
    lds1.Date >= '2012'
    AND lds1.Status = 'ok'
    AND lds2.ID IS NUL 

我现在需要在运行上述查询之前添加另一个条件为TRUE:仅考虑STATUS = NULL AND DATE> = 2011的ID". STATUS = NULL的初始ID实例不计入结果；它只是确定是否应该考虑一个ID.

I now need to add another condition to be TRUE before the above query is run: "only consider IDs where STATUS=NULL AND DATE>= 2011". The initial ID instance with STATUS = NULL is not to be counted in the results; it just determines if an ID should even be considered.

Table LDS:

ID | STATUS  | DATE
1  | NULL    | 2011
1  | ok      | 2012
2  | bad     | 2012
1  | bad     | 2013
3  | NULL    | 1999
3  | ok      | 2012
4  | ok      | 2012
5  | NULL    | 2011
5  | ok      | 2012
6  | NULL    | 2012

完整查询的预期结果是ID"5".

The expected result of the full query is ID "5".

更新:
也许这可以用数组解决?
1.从表LDS中获取所有ID，其中STATUS = NULL和DATE> = 2011，并将其放入数组中(结果:ID1，ID5，ID6)
2.对于数组中的每个ID，请检查表中的所有实例，然后选择日期最大的实例，并且STATUS IS NOT NULL(结果:ID1，ID5)
3.计算其中有STATUS = OK(结果:ID5)

UPDATE:
Maybe this can be solved with arrays?
1. take all IDs from table LDS where STATUS=NULL AND DATE>=2011 and put into array (result: ID1, ID5, ID6)
2. For each ID in the array, check all instances in the table and select the one with the largest date AND STATUS IS NOT NULL (result: ID1, ID5)
3. Count how many of these have a STATUS=OK (result: ID5)

推荐答案

我相信我已经找到了答案.也许有人可以验证这一点.

I believe I have found an answer. Maybe someone could verify this.

COUNT DISTINCT ID 
FROM lds
    WHERE ID in 
( 
COUNT(DISTINCT lds1.ID)
FROM 
    LDS lds1
    LEFT JOIN LDS lds2
        ON lds1.ID = lds2.ID
        AND lds1.Date<lds2.Date
        AND lds1.Status = 'ok'
    WHERE 
        lds1.Date>='2012'
        AND lds1.Status = 'ok'
        AND lds2.ID IS NUL
)
AND DATE>='2011' AND STATUS=NUL 

这篇关于mysql查询，嵌套where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！