CASE WHEN 很强大不仅可以合并不同列的数据,也可以根据查询结果的范围给对应字段设置不同的值,刚做了adminuser的处理,主要是当用户仓库用户在systemname显示对应仓库下的名字,当用户是客服中心就在systemname显示对应客服中心下的名称,当都不是显示空
代码如下
SELECT
su.SystemUserID,su.SystemID,s.`Name`,su.LoginID,wu.WarehouseID , w.WarehouseName,csc.CustomerServiceCenterID,
csc.ServiceCenterName,su.FirstName,su.MiddleName,su.LastName,su.Email,su.CreatedDate,
CASE s.SystemID WHEN 1 THEN w.WarehouseName WHEN 3 THEN csc.ServiceCenterName ELSE '' END AS SystemName
FROM systemuser su
LEFT JOIN system s on s.SystemID=su.SystemID
LEFT JOIN warehouseuser wu on wu.SystemUserID=su.SystemUserID
LEFT JOIN warehouse w on w.WarehouseID=wu.WarehouseID
LEFT JOIN customerservicecenteruser cscu on cscu.SystemUserID=su.SystemUserID
LEFT JOIN customerservicecenter csc on csc.CustomerServiceCenterID=cscu.CustomerServiceCenterID
where
su.Status!='Deleted'
order by LoginID desc
效果图