php - 日期为 'categories' 的 SQL JOIN

Question

我正在尝试创建一个查询，将成员分为 child 和成人:

mysql 票证:

|ticket_id|ticket_no|
|    1    | 123456  |

mysql成员:

|member_id|ticket_no|  name  |    dob     |
|    1    | 123456  | edward | 2010-03-05 | //child
|    2    | 123456  | karen  | 1965-03-05 | //adult

html 输出:

|ticket_no|Adult Names|Children Names|
| 123456  |   karen   |    edward    |

我真的想用一个连接查询来完成它，并将它作为一行输出在一个循环中。这只是一个例子:

SELECT t.*, m.*
FROM (`tickets` t)
JOIN(`members` m)
    ON(m.`ticket_no` = t.`ticket_no`)
    (SELECT m.`name` FROM m WHERE m.`dob` BETWEEN {$cStart} AND {$cEnd}) as children
    (SELECT m.`name` FROM m WHERE m.`dob` BETWEEN {$aStart} AND {$aEnd}) as adults
ORDER BY t.`ticket_no`

非常感谢任何帮助。

工作 SQL:

        SELECT t.*,
        CASE
            WHEN m.`dob`
                BETWEEN '{$datetime['start']}'
                AND '{$datetime['end']}'
            THEN m.`name`
        END AS ChildName
        FROM `tickets` t
        JOIN (`members` m)
            ON (m.`ticket_no` = t.`ticket_no`)
        ORDER BY t.`ticket_no`

Answer 1

你可以试试这个。只需在您使用的变量中传递适当的日期范围

    SELECT t.ticket_no,
    case when m.`dob` BETWEEN {$aStart} AND {$aEnd} then m.name  end as AdultName,
 case when m.`dob` BETWEEN {$cStart} AND {$cEnd} then m.name end as ChildName
     FROM (`tickets` t)
    JOIN(`members` m)
        ON(m.`ticket_no` = t.`ticket_no`)
    ORDER BY t.`ticket_no`