合并两个sql查询

Question

谁能帮我把这两个sql查询组合起来，把第一个查询的结果放到第二个查询的列中，谢谢

SELECT
    `c`.`backoffice_users_id`,
    sum(c.nbr_sous_risques) AS `nbr_sous_risques`
FROM `conseillers_sous_risques` AS `c`
WHERE
    (c.date_du_jour='2013-11-10')
    AND (c.idsous_risques BETWEEN 1 AND 12)
GROUP BY `c`.`backoffice_users_id`


SELECT
    `c`.`backoffice_users_id`,
    `c`.`idsous_risques`,
    `c`.`nbr_sous_risques`,
    `backoffice_users`.`lastname`
FROM `conseillers_sous_risques` AS `c`
INNER JOIN `backoffice_users` ON c.backoffice_users_id=backoffice_users.id
WHERE (c.date_du_jour='2013-11-10')
ORDER BY
    `c`.`backoffice_users_id` ASC,
    `c`.`idsous_risques` ASC

Answer 1

将第一个查询包装在子查询中，并使用LEFT JOIN连接它，以便仍然显示任何不匹配的记录，但在nbr_sous_risques中值为0。

SELECT  c.backoffice_users_id, 
        c.idsous_risques, 
        c.nbr_sous_risques, 
        backoffice_users.lastname,
        COALESCE(d.nbr_sous_risques, 0) nbr_sous_risques
FROM    conseillers_sous_risques AS c
        INNER JOIN backoffice_users 
            ON c.backoffice_users_id = backoffice_users.id 
        LEFT JOIN
        (
            SELECT  c.backoffice_users_id, 
                    SUM(c.nbr_sous_risques) AS nbr_sous_risques 
            FROM    conseillers_sous_risques AS c 
            WHERE   c.date_du_jour='2013-11-10' AND 
                    c.idsous_risques BETWEEN 1 AND 12
            GROUP   BY c.backoffice_users_id
        ) d ON  c.backoffice_users_id = d.backoffice_users_id
WHERE   c.date_du_jour='2013-11-10'
ORDER   BY c.backoffice_users_id ASC, c.idsous_risques ASC