面試題，java面試題

面試題，java面試題

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_groupIdFROM 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_groupIdFROM 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_groupIdFROM 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_groupIdFROM 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_groupIdFROM 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