面试题

1、利用case when进行子查询

 SELECT u.id user_id,u.username user_username,g.id group_id,
  CASE  g.flag  
  WHEN   '0'   THEN   g.groupcode  
  ELSE  (SELECT t.fullname FROM tab_0009 t WHERE t.id = g.id)
  END AS   group_groupname, 
 CASE  g.flag  
  WHEN   '0'   THEN   g.groupname  
  ELSE  (SELECT t.fullcode FROM tab_0009 t WHERE t.id = g.id)
  END AS   group_groupcode, 
u_g.id usergroup_id,u_g.user_id usergroup_userId,u_g.group_id usergroup_groupId
FROM user_group u_g RIGHT JOIN  t_group g ON u_g.group_id = g.id LEFT JOIN t_user u ON u_g.user_id = u.id  

2、将t_group和tab_0009作外连接，最后字段拼接

SELECT u.id user_id,u.username user_username,g.id group_id,
CONCAT(IFNULL(g.groupname,''),IFNULL(t.fullname,''))  group_groupname,
CONCAT(IFNULL(g.groupcode,''),IFNULL(t.fullcode,''))  group_groupcode,
u_g.id usergroup_id,u_g.user_id usergroup_userId,u_g.group_id usergroup_groupId
FROM user_group u_g ,t_user u,t_group g LEFT JOIN tab_0009 t ON t.id = g.id   WHERE u_g.group_id = g.id AND  u_g.user_id = u.id;

SELECT u.id user_id,u.username user_username,g.id group_id,
CONCAT(IFNULL(g1.groupname,''),IFNULL(t.fullname,''))  group_groupname,
CONCAT(IFNULL(g1.groupcode,''),IFNULL(t.fullcode,''))  group_groupcode,
u_g.id usergroup_id,u_g.user_id usergroup_userId,u_g.group_id usergroup_groupId

FROM user_group u_g RIGHT JOIN  t_group g ON u_g.group_id = g.id LEFT JOIN t_user u ON u_g.user_id = u.id,
t_group g1 LEFT JOIN tab_0009 t ON t.id = g1.id WHERE g1.id = g.id; 

SELECT u.id user_id,u.username user_username,g.id group_id,
g.groupname group_groupname,
g.groupcode group_groupcode,
u_g.id usergroup_id,u_g.user_id usergroup_userId,u_g.group_id usergroup_groupId
FROM user_group u_g RIGHT JOIN  t_group g ON u_g.group_id = g.id LEFT JOIN t_user u ON u_g.user_id = u.id 
 WHERE g.flag != 0 
 
UNION 
 SELECT u.id user_id,u.username user_username,g.id group_id,
(SELECT t.fullname FROM tab_0009 t WHERE t.id = g.id)  group_groupname,
(SELECT t.fullcode FROM tab_0009 t WHERE t.id = g.id) group_groupcode,
u_g.id usergroup_id,u_g.user_id usergroup_userId,u_g.group_id usergroup_groupId
FROM user_group u_g RIGHT JOIN  t_group g ON u_g.group_id = g.id LEFT JOIN t_user u ON u_g.user_id = u.id 
 WHERE g.flag = 0