当前位置: 欧洲杯竞猜 > 计算机知识 > 正文

日期段拆分难点,日期段合併难点

时间:2020-01-17 20:10来源:计算机知识
* 说明:对tt1表按名字、按规定的时间段归纳,时间段是这样的,按每年的7月1日至次年的6月30日为一段,如tt1表中,该名字(hans)的第一个时间不是7月1日的,该时间为开始时间(19

* 说明:对tt1表 按名字、按规定的时间段归纳,时间段是这样的,按每年的7月1日至次年的6月30日为一段,如tt1表中,该名字(hans)的第一个时间不是7月1日的,该时间为开始时间(1998-2),则第一段是1998-2-1至1998-6-30,第二段是1998-7-1至1999-6-30,第三段是1999-7-1至2000-6-30,而,tt1表中,hans的最后时间是2001-1,那么,第四段是2000-7-1至2001-1-31;
* dick的分段如此类推。

类似问题:

脚本,使用@特殊变量:   

INSERT INTO tt2 VALUES ("dick", {^1999-5-1},{^1999-6-30},200)
INSERT INTO tt2 VALUES ("dick", {^1999-7-1},{^2000-6-30},800)
INSERT INTO tt2 VALUES ("dick", {^2000-7-1},{^2001-1-31},1500)

Create Cursor Table2 (cname C(10),date3 D,date4 D,Nmonth N(2))
Create Cursor Table1 (cname C(10),date1 D,date2 D)
Insert Into Table1 Values ("Jack",{^1991-03-31},{^1994-12-15})
Insert Into Table1 Values ("Lily",{^2000-9-10},{^2003-2-15})
Insert Into Table1 Values ("Marry",{^1995-5-26},{^1996-2-20})
Insert Into Table1 Values ("Mimi",{^2007-4-1},{^2008-2-12})
Insert Into Table1 Values ("Cici",{^2006-7-18},{^2008-11-12})
Scan
    ldDate1=Gomonth(Date(Year(Date1),Month(Date1),1),1)
    ldDate2=Gomonth(Date(Year(Date2),Month(Date2),1),1)-1
    ldDate3=ldDate1
    lnI=0
    Do While ldDate1<ldDate2
        lnI=lnI 1
        ldDate1=Gomonth(ldDate1,1)
        If ldDate1<ldDate2
            If Month(ldDate1)=7
                Insert Into Table2 Values (Table1.cname,ldDate3,ldDate1-1,lnI)
                ldDate3=ldDate1
                lnI=0
            EndIf
        Else
            Insert Into Table2 Values (Table1.cname,ldDate3,ldDate2,lnI)
        EndIf
    EndDo
EndScan
Select Table2
Browse

-- ---------------------------- 
-- Records of pd 
-- www.jbxue.com
-- ---------------------------- 
INSERT INTO `pd` VALUES ('1', '2013-07-25 00:00:01'); 
INSERT INTO `pd` VALUES ('1', '2013-07-26 00:00:02'); 
INSERT INTO `pd` VALUES ('2', '2013-07-23 00:00:04'); 
INSERT INTO `pd` VALUES ('2', '2013-07-26 00:00:03'); 
INSERT INTO `pd` VALUES ('3', '2013-07-26 00:00:01');

*----------------------------------------------------------------
* 方法一:VFP SQL方法


if type("tt1.nCnt")="U"
    alter table tt1 add ncnt N(3)
endif
if type("tt1.date1")="U"
    alter table tt1 add date1 D
endif
replace all date1 with gomonth(date(val(cyear),val(cmonth),1),-6)
go top


* 1-Scatter Name 属性方法


*!* scatter Name obj
*!* lncnt=obj.ncnt
*!* scan
*!*    if (nnewsal!=obj.nnewsal) or (year(date1)!=year(obj.date1)) or (cname!=obj.cname)
*!*        scatter Name obj
*!*        lncnt=lncnt 1
*!*    endif
*!*    replace nCnt with lncnt
*!* endscan


* 2-Scatter Memvar 同名变量方法
scatter memvar
lncnt=m.ncnt
scan
    if (nnewsal!=m.nnewsal) or (year(date1)!=year(m.date1)) or (cname!=m.cname)
        scatter memvar
        lncnt=lncnt 1
    endif
    replace ncnt with lncnt
endscan
select cname,gomonth(min(date1),6) date1, gomonth(max(date1),7)-1 date2,nnewsal,ncnt from tt1 group by ncnt,cname

编程对Table1表的日期数据处理,把日期段分拆,形成Table2的形式。

CREATE TABLE `pd` ( 
  `imei` varchar(32) NOT NULL DEFAULT '', 
  `dat` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

解决方法

将上面的代码做了修改,修改后的代码如下

要求:

* 最后结果
CREATE table tt2 (cname c(6), date1 d ,date2 d, nnewsal n (9,2))
INSERT INTO tt2 VALUES ("hans", {^1998-2-1},{^1998-6-30},300) 
INSERT INTO tt2 VALUES ("hans", {^1998-7-1},{^1999-6-30},1400)
INSERT INTO tt2 VALUES ("hans", {^1999-7-1},{^2000-6-30},1500)
INSERT INTO tt2 VALUES ("hans", {^2000-7-1},{^2001-1-31},5000)

Create Table Table1 (cname C(10), date1 D, date2 D)
Insert Into Table1 Values ("Jack", {^1991-3-31}, {^1994-12-15})
Insert Into Table1 Values ("Lily", {^2000-9-10},{^2003-2-15})
Insert Into Table1 Values ("Marry", {^1995-5-26}, {^1996-2-20})
Insert Into Table1 Values ("Mimi", {^2007-4-1}, {^2008-2-12})
Insert Into Table1 Values ("Cici", {^2006-7-18}, {^2008-11-12})

本文介绍下,在mysql语句中使用@变量的一个例子,学习下这个特殊变量的用法,有需要的朋友参考下吧。

Set Date To Ansi
Set Century On
Set Safety Off
CLOSE DATABASES
Create Table tt1 (cname c(6),cyear c(6), Cmonth c(6),nnewsal N (9,2))
Insert Into tt1 Values ("hans", "1998","2",300)
Insert Into tt1 Values ("hans", "1998","3",300)
Insert Into tt1 Values ("hans", "1998","4",300)
Insert Into tt1 Values ("hans", "1998","5",300)
Insert Into tt1 Values ("hans", "1998","6",300)
Insert Into tt1 Values ("hans", "1998","7",1400)
Insert Into tt1 Values ("hans", "1998","8",1400)
Insert Into tt1 Values ("hans", "1998","9",1400)
Insert Into tt1 Values ("hans", "1998","10",1400)
Insert Into tt1 Values ("hans", "1998","11",1400)
Insert Into tt1 Values ("hans", "1998","12",1400)
Insert Into tt1 Values ("hans", "1999","1",1400)
Insert Into tt1 Values ("hans", "1999","2",1400)
Insert Into tt1 Values ("hans", "1999","3",1400)
Insert Into tt1 Values ("hans", "1999","4",1400)
Insert Into tt1 Values ("hans", "1999","5",1400)
Insert Into tt1 Values ("hans", "1999","6",1400)
Insert Into tt1 Values ("hans", "1999","7",500)
Insert Into tt1 Values ("hans", "1999","8",500)
Insert Into tt1 Values ("hans", "1999","9",1200)
Insert Into tt1 Values ("hans", "1999","10",1200)
Insert Into tt1 Values ("hans", "1999","11",1200)
Insert Into tt1 Values ("hans", "1999","12",1200)
Insert Into tt1 Values ("hans", "2000","1",1200)
Insert Into tt1 Values ("hans", "2000","2",1200)
Insert Into tt1 Values ("hans", "2000","3",500)
Insert Into tt1 Values ("hans", "2000","4",500)
Insert Into tt1 Values ("hans", "2000","5",500)
Insert Into tt1 Values ("hans", "2000","6",500)
Insert Into tt1 Values ("hans", "2000","7",500)
Insert Into tt1 Values ("hans", "2000","8",500)
Insert Into tt1 Values ("hans", "2000","9",500)
Insert Into tt1 Values ("hans", "2000","10",500)
Insert Into tt1 Values ("hans", "2000","11",500)
Insert Into tt1 Values ("hans", "2000","12",500)
Insert Into tt1 Values ("hans", "2001","1",500)
 
 
Insert Into tt1 Values ("dick", "1999","5",200)
Insert Into tt1 Values ("dick", "1999","6",200)
Insert Into tt1 Values ("dick", "1999","7",800)
Insert Into tt1 Values ("dick", "1999","8",800)
Insert Into tt1 Values ("dick", "1999","9",800)
Insert Into tt1 Values ("dick", "1999","10",800)
Insert Into tt1 Values ("dick", "1999","11",800)
Insert Into tt1 Values ("dick", "1999","12",800)
Insert Into tt1 Values ("dick", "2000","1",800)
Insert Into tt1 Values ("dick", "2000","2",800)
Insert Into tt1 Values ("dick", "2000","3",800)
Insert Into tt1 Values ("dick", "2000","4",800)
Insert Into tt1 Values ("dick", "2000","5",800)
Insert Into tt1 Values ("dick", "2000","6",800)
Insert Into tt1 Values ("dick", "2000","7",1500)
Insert Into tt1 Values ("dick", "2000","8",1500)
Insert Into tt1 Values ("dick", "2000","9",1500)
Insert Into tt1 Values ("dick", "2000","10",1500)
Insert Into tt1 Values ("dick", "2000","11",1500)
Insert Into tt1 Values ("dick", "2000","12",1500)
Insert Into tt1 Values ("dick", "2001","1",1500)

  1. date2 变成date4--当月最后一天。 

    Create Cursor Table2 (cname C(10),date3 D,date4 D,Nmonth N(2)) Create Cursor Table1 (cname C(10),date1 D,date2 D) Insert Into Table1 Values ("Jack",{^1991-03-31},{^1994-12-15}) Insert Into Table1 Values ("Lily",{^2000-9-10},{^2003-2-15}) Insert Into Table1 Values ("Marry",{^1995-5-26},{^1996-2-20}) Insert Into Table1 Values ("Mimi",{^2007-4-1},{^2008-2-12}) Insert Into Table1 Values ("Cici",{^2006-7-18},{^2008-11-12}) Scan

     ldDate1=Gomonth(Date(Year(Date1),Month(Date1),1),1)
     ldDate2=Gomonth(Date(Year(Date2),Month(Date2),1),1)-1
     lnI=0
     ldDate3=ldDate1
     ldDate4={}
     Do While ldDate4<=ldDate2
         lnI=lnI 1
         ldDate4=Gomonth(ldDate3,lnI)
         If Month(ldDate4)=7
             Insert Into Table2 Values (Table1.cname,ldDate3,ldDate4-1,lnI)
             ldDate3=ldDate4
             lnI=0
         EndIf
     EndDo
     If ldDate4>ldDate3
         Insert Into Table2 Values (Table1.cname,ldDate3,ldDate2,lnI)
     EndIf
    

    EndScan Select Table2 Browse

复制代码代码示例:

结果表Table2
Cname  date3     date4        Nmonth
Jack   1991-4-1  1991-6-30     3
Jack   1991-7-1  1992-6-30    12
Jack   1992-7-1  1993-6-30    12
Jack   1993-7-1  1994-6-30    12 
Jack   1994-7-1  1994-12-31    6
Lily   2000-10-1 2001-6-30     9
Lily   2001-7-1  2002-6-30    12
Lily   2002-7-1  2003-2-28     8
Marry  1995-6-1  1995-6-30     1
Marry  1995-7-1  1996-2-29     8
Mimi   2007-5-1  2007-6-30     2
Mimi   2007-7-1  2008-2-29     8
Cici   2006-8-1  2007-6-30    11
Cici   2007-7-1  2008-6-30    12
Cici   2008-7-1  2008-11-30    5
要求:
1、date1 :以当年6月30日为界,当年6月30日后的,以次年6月30日为界。如,Jack 的date1 1991-3-31 那么 分解后第一段,为 Jack 1991-4-1 1991-6-30,第二段类推。
2、date1 变成date3--次月1日

计算用户距上次访问的天数,根据imei号区分不同的用户,如果时间段内只有一次访问则为0。

select * from ( 
    select imei user_id, max(max_dd) , max(max_dd_2), to_days( max(max_dd)) - to_days(max(max_dd_2)) days  from (
        select imei, max_dd, max_dd_2 from ( 
              select tmp.imei, tmp.dates, 
                           if(@imei=tmp.imei, @rank:=@rank 1,@rank:=1) as rank, 
                      if(@rank = 1, @max_d := tmp.dates, @max_d := null) as max_dd, 
                      if(@rank = 2, @max_d_2 := tmp.dates, @max_d_2 := null) as max_dd_2, 
                            @imei:=tmp.imei 
              from (select imei, dates from pb order by imei asc ,dates desc ) tmp , 
                (select @rownum :=0 , @imei := null ,@rank:=0, @max_d :=null, @max_d_2 := null) a 
      ) result 
    ) t 
    group by imei 
    having count(*) > 1 
) x where x.days >= 1 and EXISTS (select 'x' from pb where dates > '2013-07-26 00:00:00')

复制代码代码示例:

注意:
表数据量较大时,使用union all等操作将会有悲剧性的结果。
本文原始链接:

初始化数据:   

编辑:计算机知识 本文来源:日期段拆分难点,日期段合併难点

关键词: 欧洲杯竞猜