MySQL实用语句

-- 查询第一个字符为逗号的记录, 并去掉逗号

SELECT a.fff FROM ttt WHERE SUBSTRING(fff, 1, 1) = ',';

UPDATE ttta SET fff = SUBSTRING(fff, 2) WHERE SUBSTRING(fff, 1, 1) = ',';

-- 查询一个用户属于两个组的信息

SELECT c.id uid, c.user_name, c.last_name, GROUP_CONCAT(a.name) 组, GROUP_CONCAT(a.id) 组id
FROM ea_org a
INNER JOIN org_users b ON a.id = b.org_id AND b.deleted = 0
INNER JOIN users c ON b.users_id = c.id
GROUP BY c.user_name
HAVING COUNT(*) > 1;

 

-- 拼接

SELECT GROUP_CONCAT( distinct a.id separator  ";") FROM a WHERE a.id IN ('111','222','333','444') ; //先去重id, 然后用;隔开