MySQL - 循环遍历行

Question

我有以下代码

select count(*)
from (select Annotations.user_id
from Annotations, Users
where Users.gender =  'Female'
and Users.user_id = Annotations.user_id
and image_id = 1
group by Annotations.user_id
having sum(case when stem = 'taxi' then 1 else 0 end) > 0 and
       sum(case when stem = 'zebra crossing' then 1 else 0 end) > 0
) Annotations

它生成了一个计数，显示了在图像1中有多少女性使用了“出租车”和“斑马线”的词根。

样本数据

 user id, image id, stem
 1           1      image
 1           1      taxi
 1           1      zebra crossing
 2           1      person
 2           1      zebra crossing
 2           1      taxi
 3           1      person
 3           1      zebra crossing

期望结果（或类似结果）

stem1,  stem2,            count
taxi ,  zebra crossing      2
person, zebra crossing      2

然而，由于有超过2000个词干，我无法全部指定。

如果不是指定词干字符串，我该如何循环遍历带有image_id = 1和gender = female的词干行？

谢谢

- user2233286

---

请您能否提供一下样本数据和期望的结果？ - PM 77-1

stem 属于哪个表？ - PM 77-1

@PM77-1 77-1 已编辑为样本数据，而stem属于注释表。 - user2233286

你需要任意两个词根的计数吗？我猜对了吗？ - PM 77-1

@PM77-1 是的，任何两个词根的计数。所以如果我有另一个用户ID为4的条目，其中包含图像/出租车，那么计数将为2，因为用户ID 1也具有这些词根。 - user2233286

已发布我的代码。我还没有测试它 - 只是在您的样本数据上尝试了一下。因此可能是未经完全测试的。 - PM 77-1

Answer 1

更新： 据我理解，您想选择所有2个词干的组合，并计算拥有该词干组合的用户数量。以下是我的解决方案：

SELECT stem1, stem2, count(*) as count FROM
(
    SELECT a.user_id,a.image_id,a.stem as stem1,b.stem as stem2
    FROM Annotations a JOIN Annotations b
    ON a.user_id=b.user_id && b.image_id=a.image_id && a.stem!=b.stem
    JOIN Users ON Users.user_id = a.user_id
    WHERE Users.gender = "Female"
) as stems GROUP BY stem1, stem2 having count > 1 WHERE image_id=1;

这里的注意事项是，它会为每个词干组合返回2行（第二次出现时，词干的顺序将会颠倒）。

- Ali Gangji

---

我喜欢这个的输出结果。但是，我对计数有点困惑。它是否计算了命名相同group_concat的用户数量？ - user2233286

这个程序计算每个用户给出的词干数量，而不是它们的共现。 - user2233286

我已经更新了我的答案。现在它将产生一个列表，显示2个词干的组合，以及每个组合有多少用户。 - Ali Gangji

Answer 2

根据我的理解，您需要获取拥有2个或更多"stems"的女性用户。

更新：看起来您需要显示使用其他用户的stem的用户，我已为此更新了查询。

 SELECT
    distinct a.user_id,
    group_concat(DISTINCT a.stem ORDER BY a.stem)
 FROM
    Annotations a 
    JOIN Users u ON ( a.user_id = u.user_id AND u.gender =  'Female' )
    JOIN 
        (
            SELECT 
                b.user_id,
                b.stem
            FROM
                Annotations b
        ) AS b ON ( a.user_id <> b.user_id AND b.stem = a.stem )
 WHERE
    a.image_id = 1
GROUP BY
  a.user_id

- Akash

---

它给出了特定用户的总词干计数。 - Akash

好的，看起来你的需求有所不同，是要获取具有词干的用户，其中某个特定用户的词干也被其他用户使用，对吗？ - Akash

另外，恐怕您的理解是不正确的。我不是想获取拥有2个或更多词干的女性用户。我要做的正是我的示例所示的，找到两个词干在一起出现的情况。 - user2233286

再次说一遍，这并没有达到我需要的结果，因为没有计数。除了以下内容以外，我无法更好地解释我的原始问题。你和我都有3个属性，我们都是女性。2/3的属性相同。因此，计数为2。我想展示这些属性以及它们的计数。如果我们添加另一个用户，该用户也具有相同的属性，则计数为3。但是，如果您的第二个和第三个属性与他们的第二个和第三个属性相同，则计数为2。我希望这清楚地解释了我试图做什么。 - user2233286

我不需要显示用户。 - user2233286

Answer 3

这是我尝试解决你的问题的方法：

SELECT COUNT(*) AS Count, a1.stem AS Stem1, a2.Stem AS Stem2 
FROM Annotations AS a1
INNER JOIN Annotations AS a2 ON a1.user_id = a2.user_id AND a1.image_id = a2.image_id
                          AND a1.stem < a2.stem 
WHERE a1.image_id = 1
GROUP BY a1.stem, a2.Stem
HAVING COUNT(*) > 1; 

我没有包含image_id的逻辑。

请在这里查看我的SQL Fiddle: http://sqlfiddle.com/#!2/4ee69/33

根据以下数据（从您的数据中复制），我得到了下面发布的结果。

CREATE TABLE Annotations
    (`user_id` int, `image_id` int, `stem` varchar(14))
;

INSERT INTO Annotations
    (`user_id`, `image_id`, `stem`)
VALUES
    (1, 1, 'image'),
    (1, 1, 'taxi'),
    (1, 1, 'zebra crossing'),
    (2, 1, 'person'),
    (2, 1, 'zebra crossing'),
    (2, 1, 'taxi'),
    (3, 1, 'person'),
    (3, 1, 'zebra crossing')
;

  COUNT STEM1   STEM2
    2   person  zebra crossing
    2   taxi    zebra crossing

- PM 77-1

---

这为我提供了一个柄1的列，其中一些原因是字母a的输入:S 这可能是因为我没有指定image_id吗？在您的代码中，我该如何指定image_id？ - user2233286

我有5个图像ID，想要分别查询它们。 - user2233286

请查看我的演示（链接在帖子中）。如果表现良好，我将为 image_id 更新。我无法访问 MySql 服务器，因此 SQLFiddle.com 是我的唯一游乐场。 - PM 77-1

这个完美地运行了！谢谢。最后一个问题，如果我需要加入性别（通过用户ID与用户表相连），我该怎么做？ - user2233286

这个fiddle：http://sqlfiddle.com/#!2/64ca4/1 也有“女性”的逻辑。我还没有把它放到答案里。 - PM 77-1