问题描述
我有一个有效的查询,该查询对表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子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!