社区数据库常用SQL语句

查询站内所有成员私信

SELECT
  a.`message_id` AS 'ID',
  b1.`username` AS '发件人',
  b2.`username` AS '收件人',
    a.`content` AS '内容',
  FROM_UNIXTIME(a.`created_time`,'%y%m%d') AS '时间'
FROM
  pw_windid_message a
  LEFT JOIN pw_user b1
    ON a.`from_uid` = b1.`uid`
  LEFT JOIN pw_user b2
    ON a.`to_uid` = b2.`uid`
    WHERE FROM_UNIXTIME(a.`created_time`,'%y%m%d') BETWEEN '200101' AND '200601'
--    and a.`from_uid` in ('1')
    AND a.`to_uid` IN ('1')
--    or  a.`from_uid`  in ('1')
--    OR  a.`to_uid` IN ('1')
ORDER BY FROM_UNIXTIME(a.`created_time`,'%y%m%d')  DESC

查询会员最后登录时间
SELECT
  aa.`uid` AS '用户ID',
  aa.`username` AS '用户名',
--  aa.`groupid` as '用户组ID',
  bb.`name` AS '管理组名称',
  FROM_UNIXTIME(aa.`regdate`,'%Y-%m-%d') AS '注册时间',
  FROM_UNIXTIME(cc.`lastvisit`,'%Y-%m-%d') AS '最后登录时间',
  cc.`lastloginip` AS '最后访问IP'
FROM
  pw_user aa
  LEFT JOIN pw_user_groups bb
    ON bb.`gid` = aa.`groupid`
  LEFT JOIN pw_user_data cc
    ON cc.`uid` = aa.`uid`
WHERE aa.`groupid` IN ('3', '4', '5')
ORDER BY aa.`groupid`

-------------------------------------------------------------------------------------------------------

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

所有用户组成员数量

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

查询所有会员所在的管理组和会员组，以及总积分和各积分情况
SELECT
  a.`uid`,
  a.`username`,
  (
    CASE
      WHEN a.`groupid` = '0'
      THEN "无"
      ELSE b.`name`
    END
  ) AS '管理组',
  c.`name` AS '会员组',
  d.`postnum` ,
  d.`digest`,
  (d.`postnum` + d.`digest`*2 + d.`credit2`*5) AS '总积分' ,
  d.`credit1` AS '金庸币',
  d.`credit2` AS '江湖威望',
  d.`credit3` AS '武学宝盒'
FROM
  pw_user a
  LEFT JOIN pw_user_groups b ON a.`groupid` = b.`gid`
  LEFT JOIN pw_user_groups c ON a.`memberid` = c.`gid`
  LEFT JOIN pw_user_data d ON a.`uid` = d.`uid`
  ORDER BY (d.`postnum` + d.`digest`*2 + d.`credit2`*5) DESC
 LIMIT 10000 ;

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

SELECT ww.*,vs.username FROM pw_windid_user_data ww
-- where ww.`uid` in
LEFT JOIN
(SELECT * FROM (
SELECT
  a.`uid`
  ,a.`username`
  ,b.`lastloginip`
  ,a.`email`
  ,b.`credit1`
  ,b.`credit2`
  ,b.`credit3`
FROM
  pw_user a
  LEFT JOIN pw_user_data b
    ON a.`uid` =  b.`uid`
WHERE  b.`lastloginip` LIKE ('120.244.166%')
--  AND a.`uid`not IN ('8998','9005')
  OR a.`uid` IN ('1469','2248')
--  or b.`lastloginip` like ('111.132%')
)
t) vs ON ww.`uid` = vs.`uid`
WHERE ww.`uid` = vs.`uid`