我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

给老婆的一篇文章

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

给老婆的一篇文章

老婆公司有个这样的需求：

查询出某游戏的用户回流信息，当用户连续两天登陆，则判定为2日回流，如果间隔一天登陆，则判定为3日回流，如果间隔5天登陆，则判定为7日回流。用户数据间隔时间短为14天（固定）。

准备数据

database语法和mysql一致。

创建表：

create table user_login(
u_id int,
login_date timestamp default current_timestamp
);

插入数据：

DELIMITER //
create procedure loop_insert()
begin

declare days int;
declare usrs int;
declare mx int;
declare i int;

set days = 14;
set usrs = 30;
set mx = 500;
set i = 1;

repeat 
    insert into user_login(u_id,login_date) values (floor((RAND() * usrs)),subdate(sysdate(),(RAND() * (days+1))));
    set i = i + 1;
until i >= mx

end repeat;

end//
call loop_insert();

最开始我想到的是用group_concat,sql是这样：

SELECT 
    u_id, group_concat(distinct DATE_FORMAT(login_date, '%Y%m%d') order by DATE_FORMAT(login_date, '%Y%m%d') desc separator '-') AS yyyymmdd
FROM
    user_login
GROUP BY u_id;

给老婆的一篇文章

后来想想日期转换成INTEGER相减是不准确的（比如跨月），而且这样显示并不能解决需求。

好吧，考虑行转列。

行转列

行转列，需要case when枚举，好在日期只有14天，可以做到：

SELECT 
        u_id,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180413' THEN login_date
                ELSE ''
            END AS d20180413,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180414' THEN login_date
                ELSE ''
            END AS d20180414,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180415' THEN login_date
                ELSE ''
            END AS d20180415,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180416' THEN login_date
                ELSE ''
            END AS d20180416,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180417' THEN login_date
                ELSE ''
            END AS d20180417,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180418' THEN login_date
                ELSE ''
            END AS d20180418,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180419' THEN login_date
                ELSE ''
            END AS d20180419,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180420' THEN login_date
                ELSE ''
            END AS d20180420,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180421' THEN login_date
                ELSE ''
            END AS d20180421,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180422' THEN login_date
                ELSE ''
            END AS d20180422,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180423' THEN login_date
                ELSE ''
            END AS d20180423,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180424' THEN login_date
                ELSE ''
            END AS d20180424,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180425' THEN login_date
                ELSE ''
            END AS d20180425,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180426' THEN login_date
                ELSE ''
            END AS d20180426
    FROM
        user_login

查询结果：

给老婆的一篇文章

按用户合并

按用户合并日期，去重，用max可以保证单条数据，有数据置为1无数据置为0：

SELECT 
        u_id,
            CASE
                WHEN MAX(d20180413) = '' THEN '0'
                ELSE '1'
            END AS isZ20180413,
            CASE
                WHEN MAX(d20180414) = '' THEN '0'
                ELSE '1'
            END AS isZ20180414,
            CASE
                WHEN MAX(d20180415) = '' THEN '0'
                ELSE '1'
            END AS isZ20180415,
            CASE
                WHEN MAX(d20180416) = '' THEN '0'
                ELSE '1'
            END AS isZ20180416,
            CASE
                WHEN MAX(d20180417) = '' THEN '0'
                ELSE '1'
            END AS isZ20180417,
            CASE
                WHEN MAX(d20180418) = '' THEN '0'
                ELSE '1'
            END AS isZ20180418,
            CASE
                WHEN MAX(d20180419) = '' THEN '0'
                ELSE '1'
            END AS isZ20180419,
            CASE
                WHEN MAX(d20180420) = '' THEN '0'
                ELSE '1'
            END AS isZ20180420,
            CASE
                WHEN MAX(d20180421) = '' THEN '0'
                ELSE '1'
            END AS isZ20180421,
            CASE
                WHEN MAX(d20180422) = '' THEN '0'
                ELSE '1'
            END AS isZ20180422,
            CASE
                WHEN MAX(d20180423) = '' THEN '0'
                ELSE '1'
            END AS isZ20180423,
            CASE
                WHEN MAX(d20180424) = '' THEN '0'
                ELSE '1'
            END AS isZ20180424,
            CASE
                WHEN MAX(d20180425) = '' THEN '0'
                ELSE '1'
            END AS isZ20180425,
            CASE
                WHEN MAX(d20180426) = '' THEN '0'
                ELSE '1'
            END AS isZ20180426
    FROM
        (SELECT 
        u_id,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180413' THEN login_date
                ELSE ''
            END AS d20180413,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180414' THEN login_date
                ELSE ''
            END AS d20180414,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180415' THEN login_date
                ELSE ''
            END AS d20180415,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180416' THEN login_date
                ELSE ''
            END AS d20180416,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180417' THEN login_date
                ELSE ''
            END AS d20180417,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180418' THEN login_date
                ELSE ''
            END AS d20180418,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180419' THEN login_date
                ELSE ''
            END AS d20180419,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180420' THEN login_date
                ELSE ''
            END AS d20180420,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180421' THEN login_date
                ELSE ''
            END AS d20180421,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180422' THEN login_date
                ELSE ''
            END AS d20180422,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180423' THEN login_date
                ELSE ''
            END AS d20180423,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180424' THEN login_date
                ELSE ''
            END AS d20180424,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180425' THEN login_date
                ELSE ''
            END AS d20180425,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180426' THEN login_date
                ELSE ''
            END AS d20180426
    FROM
        user_login) t0
    GROUP BY u_id

查询结果：

给老婆的一篇文章

列转行

使用group_concat:

SELECT 
        u_id,
            CONCAT(isZ20180413, isZ20180414, isZ20180415, isZ20180416, isZ20180417, isZ20180418, isZ20180419, isZ20180420, isZ20180421, isZ20180422, isZ20180423, isZ20180424, isZ20180425, isZ20180426) AS sumIsZ
    FROM
        (SELECT 
        u_id,
            CASE
                WHEN MAX(d20180413) = '' THEN '0'
                ELSE '1'
            END AS isZ20180413,
            CASE
                WHEN MAX(d20180414) = '' THEN '0'
                ELSE '1'
            END AS isZ20180414,
            CASE
                WHEN MAX(d20180415) = '' THEN '0'
                ELSE '1'
            END AS isZ20180415,
            CASE
                WHEN MAX(d20180416) = '' THEN '0'
                ELSE '1'
            END AS isZ20180416,
            CASE
                WHEN MAX(d20180417) = '' THEN '0'
                ELSE '1'
            END AS isZ20180417,
            CASE
                WHEN MAX(d20180418) = '' THEN '0'
                ELSE '1'
            END AS isZ20180418,
            CASE
                WHEN MAX(d20180419) = '' THEN '0'
                ELSE '1'
            END AS isZ20180419,
            CASE
                WHEN MAX(d20180420) = '' THEN '0'
                ELSE '1'
            END AS isZ20180420,
            CASE
                WHEN MAX(d20180421) = '' THEN '0'
                ELSE '1'
            END AS isZ20180421,
            CASE
                WHEN MAX(d20180422) = '' THEN '0'
                ELSE '1'
            END AS isZ20180422,
            CASE
                WHEN MAX(d20180423) = '' THEN '0'
                ELSE '1'
            END AS isZ20180423,
            CASE
                WHEN MAX(d20180424) = '' THEN '0'
                ELSE '1'
            END AS isZ20180424,
            CASE
                WHEN MAX(d20180425) = '' THEN '0'
                ELSE '1'
            END AS isZ20180425,
            CASE
                WHEN MAX(d20180426) = '' THEN '0'
                ELSE '1'
            END AS isZ20180426
    FROM
        (SELECT 
        u_id,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180413' THEN login_date
                ELSE ''
            END AS d20180413,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180414' THEN login_date
                ELSE ''
            END AS d20180414,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180415' THEN login_date
                ELSE ''
            END AS d20180415,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180416' THEN login_date
                ELSE ''
            END AS d20180416,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180417' THEN login_date
                ELSE ''
            END AS d20180417,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180418' THEN login_date
                ELSE ''
            END AS d20180418,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180419' THEN login_date
                ELSE ''
            END AS d20180419,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180420' THEN login_date
                ELSE ''
            END AS d20180420,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180421' THEN login_date
                ELSE ''
            END AS d20180421,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180422' THEN login_date
                ELSE ''
            END AS d20180422,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180423' THEN login_date
                ELSE ''
            END AS d20180423,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180424' THEN login_date
                ELSE ''
            END AS d20180424,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180425' THEN login_date
                ELSE ''
            END AS d20180425,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180426' THEN login_date
                ELSE ''
            END AS d20180426
    FROM
        user_login) t0
    GROUP BY u_id) t1

查询结果：

给老婆的一篇文章

统计

SELECT 
    u_id,
    sumIsZ,
    CASE
        WHEN LOCATE('11', sumIsZ) > 0 THEN 2
        ELSE 0
    END AS is2Back,
    CASE
        WHEN LOCATE('101', sumIsZ) > 0 THEN 3
        ELSE 0
    END AS is3Back,
    CASE
        WHEN LOCATE('1001', sumIsZ) > 0 THEN 4
        ELSE 0
    END AS is4Back,
    CASE
        WHEN LOCATE('10001', sumIsZ) > 0 THEN 5
        ELSE 0
    END AS is5Back,
    CASE
        WHEN LOCATE('100001', sumIsZ) > 0 THEN 6
        ELSE 0
    END AS is6Back,
    CASE
        WHEN LOCATE('1000001', sumIsZ) > 0 THEN 7
        ELSE 0
    END AS is7Back
FROM
    (SELECT 
        u_id,
            CONCAT(isZ20180413, isZ20180414, isZ20180415, isZ20180416, isZ20180417, isZ20180418, isZ20180419, isZ20180420, isZ20180421, isZ20180422, isZ20180423, isZ20180424, isZ20180425, isZ20180426) AS sumIsZ
    FROM
        (SELECT 
        u_id,
            CASE
                WHEN MAX(d20180413) = '' THEN '0'
                ELSE '1'
            END AS isZ20180413,
            CASE
                WHEN MAX(d20180414) = '' THEN '0'
                ELSE '1'
            END AS isZ20180414,
            CASE
                WHEN MAX(d20180415) = '' THEN '0'
                ELSE '1'
            END AS isZ20180415,
            CASE
                WHEN MAX(d20180416) = '' THEN '0'
                ELSE '1'
            END AS isZ20180416,
            CASE
                WHEN MAX(d20180417) = '' THEN '0'
                ELSE '1'
            END AS isZ20180417,
            CASE
                WHEN MAX(d20180418) = '' THEN '0'
                ELSE '1'
            END AS isZ20180418,
            CASE
                WHEN MAX(d20180419) = '' THEN '0'
                ELSE '1'
            END AS isZ20180419,
            CASE
                WHEN MAX(d20180420) = '' THEN '0'
                ELSE '1'
            END AS isZ20180420,
            CASE
                WHEN MAX(d20180421) = '' THEN '0'
                ELSE '1'
            END AS isZ20180421,
            CASE
                WHEN MAX(d20180422) = '' THEN '0'
                ELSE '1'
            END AS isZ20180422,
            CASE
                WHEN MAX(d20180423) = '' THEN '0'
                ELSE '1'
            END AS isZ20180423,
            CASE
                WHEN MAX(d20180424) = '' THEN '0'
                ELSE '1'
            END AS isZ20180424,
            CASE
                WHEN MAX(d20180425) = '' THEN '0'
                ELSE '1'
            END AS isZ20180425,
            CASE
                WHEN MAX(d20180426) = '' THEN '0'
                ELSE '1'
            END AS isZ20180426
    FROM
        (SELECT 
        u_id,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180413' THEN login_date
                ELSE ''
            END AS d20180413,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180414' THEN login_date
                ELSE ''
            END AS d20180414,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180415' THEN login_date
                ELSE ''
            END AS d20180415,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180416' THEN login_date
                ELSE ''
            END AS d20180416,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180417' THEN login_date
                ELSE ''
            END AS d20180417,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180418' THEN login_date
                ELSE ''
            END AS d20180418,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180419' THEN login_date
                ELSE ''
            END AS d20180419,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180420' THEN login_date
                ELSE ''
            END AS d20180420,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180421' THEN login_date
                ELSE ''
            END AS d20180421,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180422' THEN login_date
                ELSE ''
            END AS d20180422,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180423' THEN login_date
                ELSE ''
            END AS d20180423,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180424' THEN login_date
                ELSE ''
            END AS d20180424,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180425' THEN login_date
                ELSE ''
            END AS d20180425,
            CASE DATE_FORMAT(login_date, '%Y%m%d')
                WHEN '20180426' THEN login_date
                ELSE ''
            END AS d20180426
    FROM
        user_login) t0
    GROUP BY u_id) t1) t2
;

查询结果：

给老婆的一篇文章

结语

老婆，明天照着这个逻辑写就可以了，么么哒。（2018年4月26日23点50分）

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉

给老婆的一篇文章

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

给老婆的一篇文章

2024-04-02

Python常用框架有哪些?(一篇文章给你讲透)

2023-09-25

一篇文章了解MySQL的group by

准备工作！ 1.本文章MySQL使用的是5.7，引擎使用的是innodb 2. 使用的表结构（t1），字段a上有一个索引， 1. group by常用方法： group by的常规用法是配合聚合函数，利用分组信息进行统计，常见的是配合ma
2023-08-18

在DedeCMS中的文章页面的上一篇下一篇链接处增加文章摘要的方法

Dedecms系统默认的是在文章的上一篇和下一篇的链接只显示标题，但是有时我们希望显示其他信息，比如文章的摘要。找到arc.archives.class.php文件，在include目录下面，然后查找“GetPreNext&rd
2022-06-12

一篇文章读懂nginx的gzip_static模块

2024-04-02

这是我的第一篇测试文章

思路：坐标型动态规划，找到规律一切就都迎刃而解了。话不多说，一图胜千言。![fig1]根据上面可以直接得到状态转移方程，fi代表的是以该位置为正方形的右下角则最大正方形的边长。时间复杂度O（N M），空间复杂度O（N M）这题唯一的缺点
2023-01-31

一篇文章说通C#的属性Attribute

2024-04-02

一篇文章带你详解Spring的AOP

2024-04-02

一篇文章读懂nginx的gzip功能

2024-04-02

一篇文章教会你PYcharm的用法

2024-04-02

关于nagios的一篇很不错的文章

http://blog.csdn.net/lin_fs/article/details/8555960http://os.51cto.com/art/201301/376725.htmhttp://freeloda.blog.51cto.c
2023-06-04

一篇文章带你搞定JAVA Maven

2024-04-02

一篇文章带你自学python Django

2024-04-02

Laravel如何获取上一篇和下一篇文章数据

今天小编给大家分享一下Laravel如何获取上一篇和下一篇文章数据的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。首先文章的起
2023-07-04

一篇文章带你入门Java Script

2024-04-02

织梦dedecms获取上一篇下一篇文章链接的方法

我们知道，在文章页调用前后文章的链接和标题，dedecms默认上一篇和下一篇的标签分别为{dede:prenext get='pre'/} {dede:prenext get='next'/}。有时出于需要，我们只需要链接而不用标题，要怎h
2022-06-12

一篇文章弄懂js中的typeof用法

2024-04-02

一篇文章带你了解Python中的类

2024-04-02

一篇文章弄懂C#中的async和await

2024-04-02

一篇文章弄懂Mybatis中#和$的区别

2024-04-02

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈