MySQL选择组和一对多关系条件

Question

例如有这样的结构:

CREATE TABLE clicks
    (`date` varchar(50), `sum` int, `id` int)
;
CREATE TABLE marks
    (`click_id` int, `name` varchar(50), `value` varchar(50))
;

点击可以有很多标记

示例数据:

INSERT INTO clicks
    (`sum`, `id`, `date`)
VALUES
    (100, 1, '2017-01-01'),
    (200, 2, '2017-01-01')
    ;
INSERT INTO marks
    (`click_id`, `name`, `value`)
VALUES
    (1, 'utm_source', 'test_source1'),
    (1, 'utm_medium', 'test_medium1'),
    (1, 'utm_term', 'test_term1'),
    (2, 'utm_source', 'test_source1'),
    (2, 'utm_medium', 'test_medium1')
    ;

我需要获取按 date 分组的点击聚合值，其中包含所有选定值。我提出要求:

select 
  c.date,
  sum(c.sum)
from clicks as c
left join marks as m ON m.click_id = c.id
where 
(m.name = 'utm_source' AND m.value='test_source1') OR
(m.name = 'utm_medium' AND m.value='test_medium1') OR
(m.name = 'utm_term' AND m.value='test_term1')
group by date

并得到 2017-01-01 = 700，但我想得到 100，这意味着只有点击 1 才有所有标记。或者如果条件将是

(m.name = 'utm_source' AND m.value='test_source1') OR
(m.name = 'utm_medium' AND m.value='test_medium1')

我需要得到 300 而不是 600

我找到了答案，通过第一个查询获取不同的 click_id，然后使用条件 whereIn 按日期求和和分组，但在非常大且 id 为 uuid 的真实数据库中，此请求执行得非常慢。有什么建议可以让它正常工作吗？

Answer 1

您可以使用以下查询来实现它:当满足这三个条件时，您必须通过 HAVING count(*) >= 3

SELECT cc.DATE
    ,sum(cc.sum)
FROM clicks AS cc
INNER JOIN (
    SELECT id
    FROM clicks AS c
    LEFT JOIN marks AS m ON m.click_id = c.id
    WHERE (
            m.NAME = 'utm_source'
            AND m.value = 'test_source1'
            )
        OR (
            m.NAME = 'utm_medium'
            AND m.value = 'test_medium1'
            )
        OR (
            m.NAME = 'utm_term'
            AND m.value = 'test_term1'
            )
    GROUP BY id
    HAVING count(*) >= 3
    ) AS t ON cc.id = t.id
GROUP BY cc.DATE

当满足三个条件时，您必须通过 HAVING count(*) >= 2

SELECT cc.DATE
    ,sum(cc.sum)
FROM clicks AS cc
INNER JOIN (
    SELECT id
    FROM clicks AS c
    LEFT JOIN marks AS m ON m.click_id = c.id
    WHERE (
            m.NAME = 'utm_source'
            AND m.value = 'test_source1'
            )
        OR (
            m.NAME = 'utm_medium'
            AND m.value = 'test_medium1'
            )
    GROUP BY id
    HAVING count(*) >= 2
    ) AS t ON cc.id = t.id
GROUP BY cc.DATE

演示:http://sqlfiddle.com/#!9/fe571a/35

希望这对你有用...