php - 如何从父表作为一行的 chldren 中进行选择

Question

我正在努力将 member_details 表中的所有结果作为一行获取并按 memberId 分组。这是我的表结构：

//成员

    +---------------------------+
    | memberId  | stateId       |
    +-----------+---------------+
    |  1        |  101          |
    |  2        |  103          |
    |  3        |  109          |
    |  4        |  109          |
    +---------------------------+

//member_details

    +--------------------------------------------------+
    |id |memberId |gender       |age_type       |number|
    +---+---------+-------------+---------------+------+
    |1  |4        |male         |childs         |2000  |
    |2  |4        |male         |adults         |590   |
    |3  |4        |male         |elders         |900   |
    |4  |4        |female       |childs         |2000  |
    |4  |4        |female       |adults         |2000  |
    |4  |4        |female       |elders         |2000  |
    +--------------------------------------------------+

//结果表视图

    +--------------------------------------------------------------------------+
    | childs | childs  | adults | adults | elders | elders | total  | total    |
    | (male) |(female) | (male) |(female)|(male)  |(female)| (male) | (female) |
    +--------+---------+--------+--------+--------+--------+-------------------+
    | 2000   | 2000    | 590    | 2000   | 900    |2000    | 5590   | 6000     |
    +--------------------------------------------------------------------------+

//sql

    SELECT *.m, *.md 
    FROM members m
    JOIN member_details md 
    ON m.memberId = md.memberId
    where m.stateId = 109
    GROUP BY md.memberId

标签： phpsql

Answer 1

您可以使用case如下表达式

select
  sum(case when gender = 'male' and age_type = 'childs' then number else 0 end) as total_male_childs,
  sum(case when gender = 'male' and age_type = 'adults' then number else 0 end) as total_male_adults,
  sum(case when gender = 'male' and age_type = 'elders' then number else 0 end) as total_male_elders,
  sum(case when gender = 'female' and age_type = 'childs' then number else 0 end) as total_female_childs,
  sum(case when gender = 'female' and age_type = 'adults' then number else 0 end) as total_female_adults,
  sum(case when gender = 'female' and age_type = 'elders' then number else 0 end) as total_female_elders    
from member_dtails