sql语句精选

组委会表

• 需求：(大表 -> 中表 -> 小表)

select pres.president_name,pres.id ,mix.u_realname,mix.u_nickname,mix.tid from grace_event_president pres right join ( select m.u_realname,m.u_nickname ,p.id as tid, p.president_id,p.member_id from grace_members m right join grace_event_president_members p on (m.u_id = p.member_id)) as mix on (pres.id = mix.president_id) WHERE pres.status = 1 ORDER BY pres.president_name DESC

image.png

需求 通过members表当中，找出对应的组委会名下，不在 president_member 表中的组员 姓名和呢称

SELECT u_realname,u_nickname,u_id FROM grace_members as a WHERE (NOT EXISTS (select * FROM grace_event_president_members as b WHERE a.u_id = b.member_id and b.president_id = 1 ))

image.png

需求 event_title 表 联合 event_group 表和event_president表

• 三表以上联合查询从第一个from开始加（）,一直嵌套下去

SELECT t.topic_id,t.title,t.title_content,t.location,t.location_desc,t.start_day,t.end_day,t.status,t.group_id,t.president_id,g.group_name,p.president_name
FROM (grace_event_title t left join grace_event_group g on (t.group_id = g.id) left join grace_event_president p on (t.president_id = p.id)) ORDER by t.topic_id DESC

image.png