社区数据库常用SQL语句
1997 点击·4 回帖
bkevin0829
楼主
查询站内所有成员私信
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
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
bkevin0829
沙发
查询会员最后登录时间
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
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
bkevin0829
板凳
所有用户组成员数量
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
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`
bkevin0829
地板
查询所有会员所在的管理组和会员组，以及总积分和各积分情况
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 ;
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 ;
bkevin0829
4F
相对复杂的查询同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`