我在蒙戈布有以下收集资料(如果是的话,有4.2份)。
[
{"a":1,"b":1,"c":1,"d":1},
{"a":2,"b":1,"c":1,"d":8},
{"a":3,"b":1,"c":0,"d":1},
{"a":4,"b":2,"c":1,"d":1},
{"a":5,"b":2,"c":0,"d":1},
{"a":6,"b":3,"c":0,"d":2},
{"a":7,"b":4,"c":1,"d":1},
{"a":8,"b":5,"c":0,"d":4},
{"a":9,"b":6,"c":1,"d":1}
]
我试图检索符合同样符合以下条件的C=1和d=1的所有记录。 没有任何其他价值b=1的记录可具有1的 c价值。
设定的结果应当
[
{"a":4,"b":2,"c":1,"d":1},
{"a":7,"b":4,"c":1,"d":1},
{"a":9,"b":6,"c":1,"d":1}
]
我尤其感到迷惑不解,因为我想将问题处理如下。
(1) 。 在原始收集中发现每份记录为0.1和c=1。
(2) 。 发现原收藏的B项价值,有1个以上有C=1的记录。
(3) 。 将记录从1份移至2份中的b值
在小标题中,我将做以下工作:
SELECT t1.*
FROM
(SELECT* FROM tbl WHERE c=1 AND d=1) t1
LEFT JOIN
(SELECT b,SUM(CASE WHEN c=1 THEN 1 ELSE 0 END) as c_count
FROM tbl
GROUP BY b) t2
ON t1.b = t2.b
WHERE c_count = 1
或
SELECT *
FROM test
WHERE c = 1
AND d = 1
AND b IN (SELECT b
FROM (SELECT b,
Sum(CASE
WHEN c = 1 THEN 1
ELSE 0
END) AS c_count
FROM test
GROUP BY b)
WHERE c_count = 1)
在省会中,这样做是可能的吗? 我的主要困难似乎是能够相互交流看法。
I ve looked at $lookup but it doesn t seem like I can perf或m either of the following SQL-like operations using it, especially since the "right table" of the "join" is a view and not a collection.
I guess my questions are n-fold. How can I get the results I want? Is it possible to do by emulating sql? And m或e imp或tantly, is it possible to join on 或 filter a value by the results of an aggregation pipeline on the same collection