请选择 进入手机版 | 继续访问电脑版
查看: 188|回复: 4

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

[复制链接]
发表于 2022-5-26 23:26:44 | 显示全部楼层 |阅读模式 来自 北京市 移动

查询站内所有成员私信
  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
复制代码
 楼主| 发表于 2022-5-26 23:27:51 | 显示全部楼层 来自 北京市 移动

查询会员最后登录时间
  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
复制代码
 楼主| 发表于 2022-5-26 23:30:00 | 显示全部楼层 来自 北京市 移动

所有用户组成员数量
  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
复制代码
 楼主| 发表于 2022-5-26 23:30:50 | 显示全部楼层 来自 北京市 移动

查询所有会员所在的管理组和会员组,以及总积分和各积分情况
  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 ;
复制代码
 楼主| 发表于 2022-5-26 23:31:33 | 显示全部楼层 来自 北京市 移动

相对复杂的查询同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`
复制代码
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|小黑屋|B.K 网络工作室 ( 京ICP备2021039272号-1 )京公网安备 11010802038335号

GMT+8, 2022-8-17 18:45 , Processed in 0.157302 second(s), 15 queries .

Powered by BKevin! X3.4

© 2020-2022 风萧网络工作室

快速回复 返回顶部 返回列表