SQL 语句

SELECT a.*, (SELECT count(*) FROM user_group AS b WHERE a.sid = b.gid) AS count, (SELECT c.name FROM riki.market_apps AS c WHERE a.owner
= c.app) AS mafeng, a.owner in ("i1") AS isione FROM `groups` AS a WHERE name LIKE '%%' ORDER BY a.id LIMIT 200 OFFSET 0;

SELECT a.*,b.fullname,b.email,b.login_at FROM app_member a left join users b on a.uid=b.username WHERE a.app = '3gpjfqge85u6626f';
// 删除组
DELETE FROM app_member WHERE type='group' AND uid='10pjvf3bjeeu';

// 删除角色
DELETE FROM app_member WHERE type='role' AND uid='';

//查询
SELECT a.*,b.fullname,b.email,b.login_at FROM app_member a left join users b on a.uid=b.username WHERE a.app = '3gpjfqge85u6626f';

SELECT * FROM user_group WHERE gid='10pjvf3bjeeu';

SELECT * FROM `groups` WHERE sid='10pjvf3bjeeu' AND `owner` = '3gpjfqge85u6626f';
# left join
SELECT * FROM riki.subscribe_apps a LEFT JOIN `groups` b ON a.id=b.`owner`;

SELECT * FROM `groups` b LEFT JOIN riki.subscribe_apps a ON a.id=b.`owner`;

SELECT a.*, c.name AS app_name, (SELECT count(*) FROM user_group AS b WHERE a.sid = b.gid) AS count
FROM `groups` AS a
LEFT JOIN riki.subscribe_apps c
ON c.id=a.`owner`
WHERE a.name LIKE '%%' ORDER BY a.id LIMIT 20 OFFSET 100;

SELECT * FROM riki.subscribe_apps where id = 'f08am4p62q8gludg';

SELECT a.sid, a.owner in ('i1') AS isione, a.name, a.description FROM `groups` AS a, user_group AS b,
riki.subscribe_apps AS c
WHERE a.sid = b.gid AND c.id=a.`owner` AND b.uid ='mafeng';

SELECT c.sid, c.description, c.owner in ('i1') AS isione, c.`name`, c.`owner`, d.`name` AS c_instance_name FROM
(SELECT a.sid, a.name, a.description, a.`owner` FROM `groups` AS a, user_group AS b
WHERE a.sid = b.gid AND b.uid ='mafeng') AS c LEFT JOIN
riki.subscribe_apps d
ON d.id=c.`owner`;

SELECT c.sid, c.description, c.owner in ('i1') AS isione, c.`name`, c.`owner`, d.`name` AS c_instance_name FROM
(SELECT a.sid, a.name, a.description, a.`owner`, %s FROM %s AS a, %s AS b WHERE a.sid = b.rid AND b.uid = ? %s) AS c
LEFT JOIN
riki.subscribe_apps d
ON d.id=c.`owner`;

SELECT a.*, (SELECT count(*) FROM user_group WHERE gid ='10pjvgnloaua') AS count, c.`name` AS c_instance_name FROM `groups` AS a
LEFT JOIN riki.subscribe_apps c
ON c.id=a.`owner`
WHERE a.sid ='10pjvgnloaua';

 

// on case 语句

SELECT a.*,b.fullname,b.email,b.login_at, d.`name` AS c_instance_name, c.`owner` in ('i1') AS isione FROM app_member a
left join users b on a.uid=b.username
LEFT JOIN (SELECT `owner`, sid, `name` FROM `groups`
UNION
SELECT `owner`, sid, `name` FROM roles) c
ON
CASE
WHEN a.type= 'group_role' then left(a.uid, 12)=c.sid
ELSE a.uid=c.sid END
LEFT JOIN riki.subscribe_apps d
ON d.id=c.`owner`
WHERE a.app ='oax5g5luoxitn5gx';

 

// 递归查找

 

select @pv := t.parent as parent from (select * from `groups` order by id desc) t join (select @pv :='xxxx') tmp where t.sid = @pv;

 

select @pv := t.parent as parent, t.name, t.sid, t.description from
(select * from `groups` order by id desc) t join
(select @pv := (select b.sid from user_group a left join `groups` b on a.gid = b.sid
where a.uid = 'mafeng' and b.internal = true and b.owner ='i1' ORDER BY b.sid DESC LIMIT 1)) tmp where t.sid = @pv;