PHPwind 9.x 数据库常用查询语句

郝苗

查询站内所有成员私信

1. SELECT
   
2.   a.`message_id` AS 'ID',
   
3.   b1.`username` AS '发件人',
   
4.   b2.`username` AS '收件人',
   
5.     a.`content` AS '内容',
   
6.   FROM_UNIXTIME(a.`created_time`,'%y%m%d') AS '时间'
   
7. FROM
   
8.   pw_windid_message a
   
9.   LEFT JOIN pw_user b1
   
10.     ON a.`from_uid` = b1.`uid`
    
11.   LEFT JOIN pw_user b2
    
12.     ON a.`to_uid` = b2.`uid`
    
13.     WHERE FROM_UNIXTIME(a.`created_time`,'%y%m%d') BETWEEN '200101' AND '200601'
    
14. --    and a.`from_uid` in ('1')
    
15.     AND a.`to_uid` IN ('1')
    
16. --    or  a.`from_uid`  in ('1')
    
17. --    OR  a.`to_uid` IN ('1')
    
18. ORDER BY FROM_UNIXTIME(a.`created_time`,'%y%m%d')  DESC

复制代码

郝苗

查询会员最后登录时间

1. SELECT
   
2.   aa.`uid` AS '用户ID',
   
3.   aa.`username` AS '用户名',
   
4. --  aa.`groupid` as '用户组ID',
   
5.   bb.`name` AS '管理组名称',
   
6.   FROM_UNIXTIME(aa.`regdate`,'%Y-%m-%d') AS '注册时间',
   
7.   FROM_UNIXTIME(cc.`lastvisit`,'%Y-%m-%d') AS '最后登录时间',
   
8.   cc.`lastloginip` AS '最后访问IP'
   
9. FROM
   
10.   pw_user aa
    
11.   LEFT JOIN pw_user_groups bb
    
12.     ON bb.`gid` = aa.`groupid`
    
13.   LEFT JOIN pw_user_data cc
    
14.     ON cc.`uid` = aa.`uid`
    
15. WHERE aa.`groupid` IN ('3', '4', '5')
    
16. ORDER BY aa.`groupid`

复制代码

1. SELECT
   
2.   aa.`uid` AS '用户ID',
   
3.   aa.`username` AS '用户名',
   
4. -- aa.`groupid` as '用户组ID',
   
5. bb.`name` AS '管理组名称',
   
6. abc.`name` AS '用户组名称',
   
7.   FROM_UNIXTIME(aa.`regdate`,'%Y-%m-%d') AS '注册时间',
   
8.   FROM_UNIXTIME(cc.`lastvisit`,'%Y-%m-%d') AS '最后登录时间',
   
9.   cc.`lastloginip` AS '最后访问IP'
   
10. FROM
    
11.   pw_user aa
    
12.   LEFT JOIN pw_user_groups bb
    
13.     ON bb.`gid` = aa.`groupid`
    
14.   LEFT JOIN pw_user_data cc
    
15.     ON cc.`uid` = aa.`uid`
    
16.   LEFT JOIN pw_user_groups abc
    
17.     ON abc.`gid` = aa.`memberid`
    
18. WHERE FROM_UNIXTIME(cc.`lastvisit`,'%Y%m%d') BETWEEN '20211201' AND '20211231'
    
19. -- and aa.`groupid` IN ('3', '4', '5')
    
20. ORDER BY cc.`lastvisit` DESC LIMIT 5000

复制代码

郝苗

所有用户组成员数量

1. SELECT
   
2.   b.`name` AS '用户组',
   
3.   COUNT(a.`memberid`) AS '人数'
   
4. FROM pw_user a
   
5. LEFT JOIN pw_user_groups b ON b.`gid` = a.`memberid`
   
6. GROUP BY a.`memberid

复制代码

郝苗

查询所有会员所在的管理组和会员组，以及总积分和各积分情况

1. SELECT
   
2.   a.`uid`,
   
3.   a.`username`,
   
4.   (
   
5.     CASE
   
6.       WHEN a.`groupid` = '0'
   
7.       THEN "无"
   
8.       ELSE b.`name`
   
9.     END
   
10.   ) AS '管理组',
    
11.   c.`name` AS '会员组',
    
12.   d.`postnum` ,
    
13.   d.`digest`,
    
14.   (d.`postnum` + d.`digest`*2 + d.`credit2`*5) AS '总积分' ,
    
15.   d.`credit1` AS '金庸币',
    
16.   d.`credit2` AS '江湖威望',
    
17.   d.`credit3` AS '武学宝盒'
    
18. FROM
    
19.   pw_user a
    
20.   LEFT JOIN pw_user_groups b ON a.`groupid` = b.`gid`
    
21.   LEFT JOIN pw_user_groups c ON a.`memberid` = c.`gid`
    
22.   LEFT JOIN pw_user_data d ON a.`uid` = d.`uid`
    
23.   ORDER BY (d.`postnum` + d.`digest`*2 + d.`credit2`*5) DESC
    
24. LIMIT 10000 ;

复制代码

郝苗

相对复杂的查询同IP下用户的Sql语句

1. SELECT ww.*,vs.username FROM pw_windid_user_data ww
   
2. -- where ww.`uid` in
   
3. LEFT JOIN
   
4. (SELECT * FROM (
   
5. SELECT
   
6.   a.`uid`
   
7.   ,a.`username`
   
8.   ,b.`lastloginip`
   
9.   ,a.`email`
   
10.   ,b.`credit1`
    
11.   ,b.`credit2`
    
12.   ,b.`credit3`
    
13. FROM
    
14.   pw_user a
    
15.   LEFT JOIN pw_user_data b
    
16.     ON a.`uid` =  b.`uid`
    
17. WHERE  b.`lastloginip` LIKE ('120.244.166%')
    
18. --  AND a.`uid`not IN ('8998','9005')
    
19.   OR a.`uid` IN ('1469','2248')
    
20. --  or b.`lastloginip` like ('111.132%')
    
21. )
    
22. t) vs ON ww.`uid` = vs.`uid`
    
23. WHERE ww.`uid` = vs.`uid`

复制代码