tigase调试sql

-- 1、机构好友
select p.* from tig_pairs p, tig_nodes n, tig_users u 
    where pkey ='friends' 
    and p.nid=n.nid
    and n.uid=u.uid
    and n.node='0000001202';

-- 2、查询机构成员
select p.* from tig_pairs p, tig_nodes n, tig_users u 
    where pkey ='members' 
    and p.nid=n.nid
    and n.uid=u.uid
    and n.node='0000001202';

-- 3、   查询个人好友
select tp.* from tig_pairs tp,tig_users tu where tp.uid = tu.uid and tu.user_id='13294107839@test-d' and tp.pkey='roster'-- 4、查询vCard
select tp.* from tig_pairs tp,tig_users tu where tp.uid = tu.uid and tu.user_id='13294107839@test-d' and tp.pkey='vCard'5、离线消息：
select * from msg_history m, tig_ma_jids u where m.sender_uid=u.jid_id and u.jid  = '13522682337@test-d';6、查询在线用户：
select * from tig_users where last_login > last_logout  
7、查询机构信息（mml）
select * from user where CELL_PHONE = '13294107839';select * from org_user where uid = '00000009';select * from org where id = '00000030'   8、查询聊天好友列表
select btmj.jid_id buddy_id, otmj.jid_id owner_id ,btmj.jid bjd,tmm.body msg,tmm.ts ts ,'2' as imBuddyType,tp.pval vcard
                from tig_ma_msgs tmm INNER JOIN tig_ma_jids otmj on tmm.owner_id = otmj.jid_id INNER JOIN tig_ma_jids btmj 
                on tmm.buddy_id = btmj.jid_id LEFT JOIN tig_users tu on btmj.jid = tu.user_id LEFT JOIN tig_pairs tp 
                on tp.uid = tu.uid and tp.pkey='vCard'
                INNER JOIN (
                    select max(stmm.ts),stmm.stanza_hash ssh from tig_ma_msgs stmm
                    INNER JOIN tig_ma_jids on stmm.owner_id = jid_id or stmm.buddy_id = jid_id where jid = '13000000020@test-d'
                    and stmm.ts >= '2011-12-27 18:25:44'
                    and stmm.ts BETWEEN '2011-12-27 18:25:44' and FROM_UNIXTIME(ceil(1577442344000/1000))
                    and stmm.owner_id!=stmm.buddy_id 
                    -- 20170626发现现有sql的问题：好友跟我说了一句话，他能够在我的消息好友列表中；但是我却不能在他的聊天好友列表中，下句sql解决该问题
                    and !(stmm.buddy_id=jid_id and EXISTS (select * from tig_ma_msgs where owner_id=stmm.buddy_id and buddy_id=stmm.owner_id))
                    GROUP BY buddy_id
                ) bsss on bsss.ssh=tmm.stanza_hash
            where otmj.jid = '13000000020@test-d' or btmj.jid = '13000000020@test-d'
            and tmm.owner_id!=tmm.buddy_id
            and tmm.type='chat'
            order by tmm.ts desc

8.1、left join bsss（消息处理思路：满足起始、终止时间查询、关键字模糊匹配下的消息情况下，取其最新时间的那条记录）部分
select max(ts),tig_ma_msgs.stanza_hash ssh from tig_ma_msgs 
                    INNER JOIN tig_ma_jids on owner_id = jid_id where jid = '18571528346@test-d'
                    -- （1）员工加入机构时间
                    and ts >= '2011-12-27 18:25:44'
                    -- （2）查询的时间跨度
                    and ts BETWEEN '2011-12-27 18:25:44' and FROM_UNIXTIME(ceil(1577442344000/1000))
                    -- （3）文本关键字匹配
                    and F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"text":"','"')!=''
                    AND INSTR(F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"text":"','"'),'e')>0
                    AND INSTR(F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"type":"','"'),'text')>0
                    and owner_id!=buddy_id GROUP BY buddy_id9、查询聊天室好友列表
select tmm.room_jid as room_jid,ts.pval as bname,maxmsg.ts as ts , maxmsg.msg as msg ,'1' as imBuddyType  from tig_muc_members tmm 
                INNER JOIN 
                (select mh.room_name,MAX(mh.timestamp) as ts , mh.body as msg from muc_history mh 
                    where 1=1
                    and mh.timestamp >= 1191909500579
                    and mh.timestamp BETWEEN 1191909500579 and 1495592629158
                    AND F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(mh.body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"text":"','"')!=''
                                                    AND INSTR(F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(mh.body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"text":"','"'),'你')>0
                                                    AND INSTR(F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(mh.body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"type":"','"'),'text')>0
                    GROUP BY mh.room_name) 
                maxmsg on maxmsg.room_name= tmm.room_jid
                LEFT JOIN tig_nodes tpn on tpn.node = tmm.room_jid 
                LEFT JOIN tig_nodes tn on tpn.nid = tn.parent_nid and tn.node = 'config' 
                LEFT JOIN tig_pairs ts on ts.nid = tn.nid and ts.pkey = 'muc#roomconfig_roomname'
                where jid = '18702777848@test-d'
                ORDER by maxmsg.ts desc9.1、inner join maxmsg(left join maxmsg会查出没有聊天内容的空房间)（消息处理思路：满足起始、终止时间查询、关键字模糊匹配下的房间情况下，取其最新时间的那条记录）部分
select mh.room_name,MAX(mh.timestamp) as ts , mh.body as msg from muc_history mh 
where 1=1
-- （1）员工加入机构时间
and mh.timestamp >= 1191909500579
-- （2）查询时间跨度
and mh.timestamp BETWEEN 1191909500579 and 1495592629158
-- （3）文字关键字匹配
AND F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(mh.body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"text":"','"')!=''
                        AND INSTR(F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(mh.body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"text":"','"'),'你')>0
                        AND INSTR(F_GET_JSON_VALUE(REPLACE(replace(REPLACE(REPLACE(mh.body, CHAR(10), ''), CHAR(13), ''),' ',''),'"','"'),'"type":"','"'),'text')>0
GROUP BY mh.room_name
ORDER BY mh.timestamp desc9.2、根据房间号查询房间名称
select ts.pval from tig_pairs ts 
INNER JOIN tig_nodes tn on ts.nid = tn.nid 
INNER JOIN tig_nodes tpn on tpn.nid = tn.parent_nid and tn.node = 'config'
where tpn.node = 'krywhshie63opjrde13hpnmo9hatpa@muc.test-d' 
and ts.pkey = 'muc#roomconfig_roomname'10、普通消息详情
select rowTb.rownum2 as rownum, owner_id,buddy_id, REPLACE(REPLACE(REPLACE(body, CHAR(10), ''), CHAR(13), ''),'"','"') msg,direction ,ts
            from tig_ma_msgs tmm
            INNER JOIN (select @rownum2:=@rownum2+1 as rownum2, t.msg_id as msgId from (select @rownum2:=0) a, tig_ma_msgs t 
                where t.owner_id=40 and t.buddy_id = 39
                    ) rowTb on rowTb.msgId = tmm.msg_id
            INNER JOIN tig_ma_jids btmj on tmm.buddy_id = btmj.jid_id 
            where owner_id=40 and buddy_id = 39 

11、聊天室消息详情
select rowTb.rownum2 as rownum,room_name as roomName, FROM_UNIXTIME(ceil(timestamp/1000) ,'%Y-%m-%d %H:%i:%s') as ts,timestamp,
                SUBSTRING_INDEX(sender_jid, '/', 1) as sengderJid,sender_nickname as senderNickName,REPLACE(REPLACE(REPLACE(body, CHAR(10), ''), CHAR(13), ''),'"','"') body from muc_history
                INNER JOIN 
                (select @rownum2:=@rownum2+1 as rownum2, t.room_name as roomName,t.timestamp as ttimesTamp from (select @rownum2:=0) a, muc_history t 
                    where room_name= '8tu7w8zp3cbhqxjwb4f9are20rm9f0@muc.test-d'
                    and t.timestamp >= 1181542060555
                ) rowTb 
                on (rowTb.roomName = room_name and rowTb.ttimesTamp = timestamp)
                where room_name='8tu7w8zp3cbhqxjwb4f9are20rm9f0@muc.test-d'
                and timestamp >= 1181542060555

12、查询房间属性
select nid, node from tig_nodes where node = '7e2d0e643a22424995b60a3af26efec1579@muc.mml';
select *  from tig_pairs where uid = (select uid from tig_users where user_id='multi-user-chat') and nid in (select nid from tig_nodes where parent_nid=763 or nid=763);13、删除房间信息
13.1、删除房间所有成员信息
-- select ts.* ,tn.,tpn. from tig_pairs ts 
-- INNER JOIN tig_nodes tn on ts.nid = tn.nid 
-- INNER JOIN tig_nodes tpn on tpn.nid = tn.parent_nid and tn.node = 'config'
-- where tpn.node in ('f9e193db32cf4fc78815adb83de690e5239@muc.mml','3dd57580da73427cbb74fd80aec464a1414@muc.mml','3dd57580da73427cbb74fd80aec464a1414@muc.mml','d8b8e0bc47ea48abb33db1ec819ade33617@muc.mml','87e0f4f1a47248f28e1a884707871539947@muc.mml','e1f52c546db54f6ca6d0315e4846cc18699@muc.mml','e1f52c546db54f6ca6d0315e4846cc18699@muc.mml','3cf0690bdac349c59acb0dc521f2f0ac119@muc.mml','3cf0690bdac349c59acb0dc521f2f0ac119@muc.mml','51773e4c71e14e4880bdd7aa3bbae627434@muc.mml','51773e4c71e14e4880bdd7aa3bbae627434@muc.mml','3c1609b0be954213b4b0fd5199748e7a129@muc.mml','3c1609b0be954213b4b0fd5199748e7a129@muc.mml','32-86-84-1000002559-1000002552@muc.mml','32-86-84-1000002559-1000002552@muc.mml','32-86-84-1000002560-1000002552@muc.mml','32-86-84-1000002560-1000002552@muc.mml','32-86-83-1000002559-1000002551@muc.mml','32-86-83-1000002559-1000002551@muc.mml','32-86-84-1000002561-1000002552@muc.mml','32-86-84-1000002561-1000002552@muc.mml','32-86-84-1000002554-1000002552@muc.mml','32-86-84-1000002554-1000002552@muc.mml','32-86-84-1000002559-1000002552@muc.mml','32-86-84-1000002560-1000002552@muc.mml','32-86-84-1000002561-1000002552@muc.mml','32-86-30-1000002554-1000002492@muc.mml','32-86-30-1000002554-1000002492@muc.mml','32-86-85-1000002554-1000002553@muc.mml','32-86-85-1000002554-1000002553@muc.mml','32-86-85-1000002561-1000002553@muc.mml','32-86-85-1000002561-1000002553@muc.mml','32-86-85-1000002559-1000002553@muc.mml','32-86-85-1000002559-1000002553@muc.mml','32-86-85-1000002560-1000002553@muc.mml','32-86-85-1000002560-1000002553@muc.mml','123456789@muc.mml','zidingyi2@muc.mml')
-- and ts.pkey = 'muc#roomconfig_roomname';-- delete from tig_pairs where nid = 200;
-- delete from tig_nodes where nid = 200 and parent_nid = 199;
-- delete from tig_pairs where nid = 201;
-- delete from tig_nodes where nid = 201;
-- delete from tig_pairs where nid = 199;
-- delete from tig_nodes where nid = 199;select nid, node from tig_nodes where node = '154-165834-165820-470-457@muc.mml';delete from tig_muc_members where room_jid = '154-165834-165820-470-457@muc.mml';delete from muc_history where room_name = '154-165834-165820-470-457@muc.mml';delete from tig_pairs where uid = (select uid from tig_users where user_id='multi-user-chat') and nid in (select nid from tig_nodes where parent_nid=? or nid=?);delete from tig_nodes where nid=? or parent_nid=?;13.2、删除房间指定成员信息
-- im 房间用户成员
delete from tig_pairs where nid = (select nid from tig_nodes where  parent_nid=(select nid from tig_nodes where node = ? -- roomid
) and node = 'affiliations')  and pkey in (?) -- senderjid，不要后缀resource
SELECT * from tig_pairs where nid = (select nid from tig_nodes where  parent_nid=(select nid from tig_nodes where node = '104-165723-165722-246-245@muc.mml' -- roomid
) and node = 'affiliations')  and pkey in ('1000002738@mml','1000002739@mml','1000002740@mml') -- senderjid-- im 扩展成员
delete from tig_muc_members where room_jid = ? and jid in (?)
SELECT * from tig_muc_members where jid in ('1000002738@mml','1000002739@mml','1000002740@mml') and room_jid = '104-165723-165722-246-245@muc.mml'14、获取房间的最后一条消息的时间，及对应的成员
select sender_jid,timestamp from muc_history where room_name ='29-81-48-102-55@muc.mml' ORDER BY timestampdesc LIMIT 1;15、查询房间与成员SELECT tp.pkey, tn2.node FROM tig_pairs tp,tig_nodes tn ,tig_nodes tn2 
WHERE tp.nid = tn.nid  and tn2.nid = tn.parent_nid
AND tp.uid =  (SELECT uid FROM tig_users WHERE user_id = 'multi-user-chat' )
AND tp.nid IN (SELECT nid FROM tig_nodes WHERE parent_nid in (SELECT nid FROM tig_nodes WHERE parent_nid = 25 AND nid > 10092) 
        OR nid in (SELECT nid FROM tig_nodes WHERE parent_nid = 25 AND nid > 10092)) 
AND (tp.pval = 'member' or tp.pval = 'admin');16、日消息数统计
16.1、每天普通在线消息数量统计
SELECT DATE(ts) AS msgdate, COUNT(0) AS online_message_count FROM tig_ma_msgs GROUP BY msgdate ORDER BY online_message_count DESC LIMIT 1,1000;16.2、每天房间在线消息数量统计
SELECT DATE(FROM_UNIXTIME(timestamp / 1000,'%Y-%m-%d %H:%i:%S')) AS msgdate, COUNT(0) AS online_muc_message_count FROM  muc_history 
GROUP BY msgdate ORDER BY online_muc_message_count DESC LIMIT 1,1000;

文章永久链接：https://tech.souyunku.com/29772