本文探讨如何通过SQL查询合并两个子查询，这两个子查询分别统计了`t_card_info`中type为0和1的卡片数量，关联条件基于`comp.comp_info_id`。第一条SQL计算了type=0的卡片在每个机构中的学生数量，而第二条SQL计算了type=1的卡片对应的教师数量。最终目标是整合这两个结果，展示机构ID、机构名称、学生卡片数量和教师卡片数量。
想要达到这样的效果
两条sql 只有一个 card.type 条件不一样 0,于1 的区别 , 最后希望结果等于
comp.comp_info_id,
comp.comp_name,
card.school_info_id,
count(card_info_id) stu ，
count(card_info_id) tea
第一条s’q’l
SELECT
comp.comp_info_id,
comp.comp_name,
card.school_info_id,
count(card_info_id) stu
FROM
t_comp_info comp
LEFT JOIN t_card_info card ON comp.comp_info_id = card.school_info_id
AND card.state = 2 AND card.type = 0
WHERE
comp.state = 1
GROUP BY
comp.comp_info_id
ORDER BY count(1 )
第二条sql
SELECT
comp.comp_info_id,
comp.comp_name,
card.school_info_id,
count(card_info_id) tea
FROM
t_comp_info comp
LEFT JOIN t_card_info card ON comp.comp_info_id = card.school_info_id
AND card.state = 2 AND card.type = 1
WHERE
comp.state = 1
GROUP BY
comp.comp_info_id
ORDER BY count(card.card_info_id ) desc
回答
把两个查询作为子查询关联起来，用他们相同的字段 oncode 作为连接条件来关联
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
