php - MySQL - 如果为一组记录设置了另一个行值，则获取行

Question

我有以下 MySQL 数据库表，该表由表单插件填充。我想提取每组记录的 field_name“您的电子邮件”的值(按“submit_time”列值分组)，并且 field_name“checkbox-signup”的值为“注册我们的电子邮件通讯” .

示例数据表(表名称 = dbplugin_submits):

    --------------------------------------------------------------------
    |submit_time|field_name     |value                            |order|
    |---------- |-------------------------------------------------------|
    |1400149594 |checkbox-signup|Signup for our email newsletter  |4    |
    |1400149594 |your-message   |Testing newsletter signup        |3    |
    |1400149594 |your-subject   |Testing newsletter signup        |2    |
    |1400149594 |your-name      |Test User 1                      |0    |
    |1400149594 |your-email     |Test User@company1.co.uk         |1    |
    |1400149627 |your-email     |Test User2@company2.co.uk        |1    |
    |1400149627 |your-name      |Test User 2                      |0    |
    |1400149594 |Submitted From |188.66.94.194                    |10000|
    |1400149594 |Submitted Login|admin                            |9999 |
    |1400149627 |your-subject   |Testing again - please ignore    |2    |
    |1400149627 |your-message   |                                 |3    |
    |1400149627 |checkbox-signup|                                 |4    |
    |1400149627 |Submitted Login|admin                            |9999 |
    |1400149627 |Submitted From |188.66.94.194                    |10000|
    |1400149887 |your-name      |Test User 3                      |0    |
    |1400149887 |your-email     |user.three@company2.co.uk        |1    |
    |1400149887 |your-subject   |TESTING FORM FOR ANALYTICS       |2    |
    |1400149887 |your-message   |PLEASE IGNORE                    |3    |
    |1400149887 |checkbox-signup|                                 |4    |
    |1400149887 |Submitted From |188.66.94.194                    |10000|
    |1400768741 |your-name      |Test User                        |0    |
    |1400768741 |your-email     |testuser@company.co.uk           |1    |
    |1400768741 |your-subject   |Testing                          |2    |
    |1400768741 |your-message   |Please ignore - just testing     |3    |
    |1400768741 |checkbox-signup|Signup for our email newsletter  |4    |
    |1400768741 |Submitted Login|admin                            |9999 |
    |1400768741 |Submitted From |188.66.94.19                     |10000|
     -------------------------------------------------------------------

我已经尝试了以下方法，但我不确定内部查询是否是正确的方法:

SELECT  a.*
FROM    `dbplugin_submits` a
        INNER JOIN
        (
            SELECT  `submit_time`
            FROM    `dbplugin_submits`
            GROUP BY `submit_time`
        ) b ON a.`submit_time` = b.`submit_time`
          AND a.`field_name` = 'checkbox-signup'
          AND a.`field_value` = 'Sign up for our email newsletters'
          AND b.`field_name` = 'your-email'

任何帮助将不胜感激!

Answer 1

它尚未经过测试，但类似的东西应该可以工作:

SELECT a.submit_time, b.value FROM dbplugin_submits a 
JOIN dbplugin_submits b ON (a.submit_time = b.submit_time AND b.field_name='your-email')
WHERE  a.field_name = 'checkbox-signup' AND a.value = 'Signup for our email newsletter'