穆琪的博客

一个程序员的自传

用到的SQL语句集合

作品收藏量前十:
SELECT ys_hallindexinfo.zpname,COUNT(*) as num FROM ys_hallindexinfo,ys_user_collect WHERE ys_user_collect.targetId=ys_hallindexinfo.id GROUP BY ys_user_collect.targetId ORDER BY num DESC LIMIT 10;

艺术家搜索量前十:
SELECT COUNT(*) as num,ys_user_info.userName FROM ys_artist,ys_search_record,ys_user_info WHERE ys_artist.userId=ys_search_record.targetId AND ys_artist.userId=ys_user_info.userId GROUP BY ys_search_record.targetId ORDER BY num DESC LIMIT 10;

查询作品类型比例:
SELECT pub_dict.NAME,ys_hallindexinfo.category,COUNT(*) as num FROM ys_hallindexinfo,pub_dict WHERE ys_hallindexinfo.category=pub_dict.CODE AND pub_dict.KIND='workcategory' GROUP BY ys_hallindexinfo.category;

查询艺术家年龄段:
SELECT SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE())>70 THEN 1 ELSE 0 END) as 70up FROM ys_user_info WHERE birthday<>'' AND userType=2;

建立年龄段视图:
CREATE VIEW artageduan AS
(SELECT '18岁以下' as ageduan,SUM(CASE WHEN (TIMESTAMPDIFF(YEAR, birthday,CURDATE())<18) THEN 1 ELSE 0 END) as num FROM ys_user_info WHERE birthday<>'' AND userType=2)
UNION ALL
(SELECT '18岁-30岁' as ageduan,SUM(CASE WHEN (TIMESTAMPDIFF(YEAR, birthday, CURDATE())>=18 AND TIMESTAMPDIFF(YEAR, birthday, CURDATE())<30) THEN 1 ELSE 0 END) as num FROM ys_user_info WHERE birthday<>'' AND userType=2)
UNION ALL
(SELECT '30岁-60岁' as ageduan,SUM(CASE WHEN (TIMESTAMPDIFF(YEAR, birthday, CURDATE())>=30 AND TIMESTAMPDIFF(YEAR, birthday, CURDATE())<60) THEN 1 ELSE 0 END) as num FROM ys_user_info WHERE birthday<>'' AND userType=2)
UNION ALL
(SELECT '60岁以上' as ageduan,SUM(CASE WHEN (TIMESTAMPDIFF(YEAR, birthday, CURDATE())>=60) THEN 1 ELSE 0 END) as num FROM ys_user_info WHERE birthday<>'' AND userType=2);

查询艺术家年龄:
SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) as age,userName FROM ys_user_info WHERE birthday<>'' AND userType=2;

创客作品数量前十:
select COUNT(*) as num,ys_hallindexinfo.userId,ys_user_info.userName from ys_hallindexinfo,ys_user_info where ys_hallindexinfo.userId=ys_user_info.userId AND ys_hallindexinfo.type=1 GROUP BY ys_hallindexinfo.userId ORDER BY num DESC LIMIT 10;

学校创客对应表:
SELECT COUNT(*),userName,IFNULL(school,'学校暂未填写') as school FROM ys_user_info WHERE userType=1 GROUP BY userName;

作品搜索量前十:
SELECT COUNT(*) as num,ys_hallindexinfo.zpname FROM ys_search_record,ys_hallindexinfo WHERE ys_hallindexinfo.id=ys_search_record.targetId GROUP BY ys_search_record.targetId ORDER BY num DESC LIMIT 10;

作品评论数前十:
SELECT COUNT(*) AS num,ys_hallindexinfo.zpname FROM ys_user_comment,ys_hallindexinfo WHERE ys_user_comment.targetId=ys_hallindexinfo.id AND ys_user_comment.targetType='4' GROUP BY ys_user_comment.targetId ORDER BY num DESC LIMIT 10;

作品点赞数前十:
SELECT COUNT(*) AS num,ys_hallindexinfo.zpname FROM ys_user_like,ys_hallindexinfo WHERE ys_user_like.targetId=ys_hallindexinfo.id AND ys_user_like.targetType='4' GROUP BY ys_user_like.targetId ORDER BY num DESC LIMIT 10;
城市分布
SELECT count(*) as num,province FROM ys_school WHERE province<>'' AND province<>'-' GROUP BY province;

学校创客数排名前十:
SELECT COUNT(*) as num,IFNULL(school,'学校暂未填写') as school FROM ys_user_info WHERE userType=1 GROUP BY school ORDER BY num DESC LIMIT 10;
作品浏览量前十:
SELECT ex.visitsNum,zp.zpname FROM ys_hallindexinfo zp LEFT JOIN ys_hallindex_extend ex ON zp.id = ex.targetId ORDER BY ex.visitsNum DESC LIMIT 10;

30天内签到排行前十:
select ys_user_info.userName as name,COUNT(*) as dznum from ys_user_sign,ys_user_info where ys_user_sign.userid=ys_user_info.userId AND date_sub(curdate(), INTERVAL 30 DAY) <= date(ys_user_sign.createTime) GROUP BY name ORDER BY name DESC LIMIT 10;
查询男女人数
SELECT COUNT(1) as zong,SUM(CASE WHEN SEX = '1' THEN 1 ELSE 0 END) as nan,SUM(CASE WHEN SEX = '0' THEN 1 ELSE 0 END) as nv FROM ys_artist;

新:
SELECT 'artnan' as name,SUM(CASE WHEN SEX = '1' THEN 1 ELSE 0 END) as sex FROM ys_artist
UNION ALL
SELECT 'artnv' as name,SUM(CASE WHEN SEX = '0' THEN 1 ELSE 0 END) as sex FROM ys_artist;
联合查询查询男女比例

更新:
因数据表更新,查询男女比例如下:
SELECT 'artnan' as name,SUM(CASE WHEN gender=1 THEN 1 ELSE 0 END) as sex FROM ys_user_info WHERE userType=1
UNION ALL
SELECT 'artnv' as name,SUM(CASE WHEN gender=0 THEN 1 ELSE 0 END) as sex FROM ys_user_info WHERE userType=1;
查询其他类别只需要更改usertype
构造视图的语句为:
create view markersex as
SELECT '男' as name,SUM(CASE WHEN gender=1 THEN 1 ELSE 0 END) as sex FROM ys_user_info WHERE userType=1
UNION ALL
SELECT '女' as name,SUM(CASE WHEN gender=0 THEN 1 ELSE 0 END) as sex FROM ys_user_info WHERE userType=1;

查询今日新增数量(画廊)可改:
select * from ys_gallery where date_format(CREATETIME,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d');
只需要改动表名与字段名即可

查询画廊关注量
SELECT gallery.NAME,COUNT(*) as attnum FROM ys_gallery gallery LEFT JOIN ys_user_attention attention ON gallery.ID = attention.targetId GROUP BY gallery.NAME ORDER BY attnum DESC LIMIT 10;

创客关注量前十
SELECT mark.name,COUNT(*) as attnum FROM ys_maker mark LEFT JOIN ys_user_attention attention ON mark.userId = attention.targetId GROUP BY mark.name ORDER BY attnum DESC LIMIT 10;

艺术家关注量前十
SELECT art.NAME,COUNT(*) as attnum FROM ys_artist art LEFT JOIN ys_user_attention attention ON art.ID = attention.targetId GROUP BY art.NAME ORDER BY attnum DESC LIMIT 10;

创客浏览量前十
SELECT ifnull(ex.visitsNum,'0') NUM,maker.name FROM ys_maker maker LEFT JOIN ys_hallindex_extend ex ON maker.ID = ex.targetId ORDER BY ex.visitsNum DESC LIMIT 10;
画廊浏览量前十
SELECT ex.visitsNum,gallery.NAME FROM ys_gallery gallery LEFT JOIN ys_hallindex_extend ex ON gallery.ID = ex.targetId ORDER BY ex.visitsNum DESC LIMIT 10;
艺术家浏览量前十
SELECT ex.visitsNum,art.NAME FROM ys_artist art LEFT JOIN ys_hallindex_extend ex ON art.ID = ex.targetId ORDER BY ex.visitsNum DESC LIMIT 10;
积分排行
SELECT Score,NickName FROM `ys_user_info` WHERE NickName <> '' ORDER BY Score DESC LIMIT 5;

艺术家分布
SELECT count(NAME) as num,province FROM ys_artist WHERE province<>'' AND province<>'-' GROUP BY province;
 

© 鲁ICP备18041558号
Powered by Z-BlogPHP & Yiwuku.com