把前几天和 j9988 讨教的问题贴出来

[playyuer]

把前几天和 j9988 讨教的问题贴出来:

表结构:
姓名(pk),日期(pk),金额

以下是数据:

select * into #T
from
(
select '张三' as 姓名,cast('2002-01-01' as datetime) as 日期,100 as 金额
union all
select '张三','2002-02-01',100
union all
select '张三','2002-03-01',110
union all
select '张三','2002-04-01',110
union all
select '张三','2002-05-01',110
union all
select '张三','2002-06-01',100
union all
select '张三','2002-07-01',120
union all
select '李四','2002-02-01',100
union all
select '李四','2002-03-01',110
union all
select '李四','2002-04-01',90
union all
select '李四','2002-05-01',100
union all
select '李四','2002-06-01',100
union all
select '李四','2002-07-01',150
union all
select '王五','2002-02-01',100
union all
select '王五','2002-03-01',90
union all
select '王五','2002-04-01',90
union all
select '王五','2002-05-01',90
union all
select '王五','2002-06-01',100
union all
select '王五','2002-07-01',80
) T


要求查询结果:

姓名,开始日期,结束日期,金额
李四,2002-02-01,2002-02-01,100
李四,2002-03-01,2002-03-01,110
李四,2002-04-01,2002-04-01,90
李四,2002-05-01,2002-06-01,100
李四,2002-07-01,2002-07-01,150
王五,2002-02-01,2002-02-01,100
王五,2002-03-01,2002-05-01,90
王五,2002-06-01,2002-06-01,100
王五,2002-07-01,2002-07-01,80
张三,2002-01-01,2002-02-01,100
张三,2002-03-01,2002-05-01,110
张三,2002-06-01,2002-06-01,100
张三,2002-07-01,2002-07-01,120


可以理解为: 每个人的发薪历史情况!
(个人认为 非常非常 有用,可用于各种含有历史记录(教育经历、工作经历等)的表查询)


稍后贴出参考答案!

[huxin1]

明天上班再看

• 打赏
• 举报

回复 赞

[j9988]

姓名+月份是唯一的.我说登山者是正确的.是因为
union all
select '张三','2002-08-01',110
union all
select '张三','2002-09-01',110
union all
select '张三','2002-11-01',110
union all
select '张三','2002-12-01',110
张三 2002-08-01 00:00:00.000 2002-09-01 00:00:00.000 110
张三 2002-08-11 00:00:00.000 2002-12-01 00:00:00.000 110

而不象REALGZ的结果:
张三 2002-08-01 00:00:00.000 2002-12-01 00:00:00.000 110

我的我测过,好象是正确的.

• 打赏
• 举报

回复 赞

[qwater]

学习中

• 打赏
• 举报

回复 赞

[zjcxc]

支持登山的,
因为姓名+日期为主键,唯一.
如果没有这个条件,登山的就有问题.

• 打赏
• 举报

回复 赞

[cheny1234]

学习

• 打赏
• 举报

回复 赞

[playyuer]

登山的条件更严了!

• 打赏
• 举报

回复 赞

[playyuer]

登山的条件改"善"了

• 打赏
• 举报

回复 赞

[benxie]

呵。

• 打赏
• 举报

回复 赞

[playyuer]

realgz(realgz) ***** :

select t.姓名
,min(日期) as 开始日期
,max(日期) as 结束日期
,min(金额) as 金额
from
(SELECT *,(select count(*)
from #t
where 姓名 = a.姓名
and 日期 > a.日期
and 金额 <> a.金额) as id
from #t a) T
group by 姓名,id,金额
order by 姓名,min(日期)


j9988 启发 playyuer:
select a.姓名,a.日期 as 开始日期,b.日期 as 结束日期,a.金额
from #T a,#T b
where a.姓名 = b.姓名
and a.金额 = b.金额
and a.日期 <= b.日期
and not exists (select 1
from #T C
where 姓名 = b.姓名
and 金额 = b.金额
and 日期 > b.日期
and not exists (select 1
from #T
where 姓名 = A.姓名
and 日期 > B.日期
and 日期 < C.日期
and 金额 <> A.金额))
and not exists (select 1
from #T C
where 姓名 = b.姓名
and 金额 = b.金额
and 日期 < A.日期
and not exists (select 1
from #T
where 姓名 = B.姓名
and 日期 < A.日期
and 日期 > C.日期
and 金额 <> B.金额))
and not exists(select 1
from #T
where 日期 < B.日期
and 日期 > A.日期
and 姓名 = B.姓名
and 金额 <> B.金额)

order by a.姓名, A.日期


playyuer:

select *
from
(select distinct 姓名
,(select min(日期)
from #T b
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 >= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 <= a.日期
and 金额 <> a.金额
),0)
and 日期 <= (select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')

)
) as 开始日期
,(select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')
) as 结束日期
,金额
from #t as A ) T
order by 姓名,开始日期

• 打赏
• 举报

回复 赞

[playyuer]

select *
,(select min(日期)
from #T b
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 >= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 <= a.日期
and 金额 <> a.金额
),0)
and 日期 <= (select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')

)
) as 开始日期
,(select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')
) as 结束日期
from #t as A
order by 姓名,开始日期

• 打赏
• 举报

回复 赞

[playyuer]

我先把此搞定,类似 tj_dns(愉快的登山者 MVP) :

select *
,(select min(日期)
from #T b
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 >= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 <= a.日期
and 金额 <> a.金额
),0)
and 日期 <= (select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')

)
) as 开始日期
,(select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')
) as 结束日期
from #t as A
order by 姓名,开始日期

再加条件!

select *
from
(select distinct 姓名
,(select min(日期)
from #T b
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 >= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 <= a.日期
and 金额 <> a.金额
),0)
and 日期 <= (select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')

)
) as 开始日期
,(select max(日期)
from #T
where 姓名 = A.姓名
and 金额 = a.金额
and 日期 <= isnull((select min(日期)
from #t
where 姓名 = A.姓名
and 日期 >= a.日期
and 金额 <> a.金额
),'9999-12-31')
) as 结束日期
,金额
from #t as A ) T
order by 姓名,开始日期

• 打赏
• 举报

回复 赞

[Jianli2004]

realgz(realgz)有点问题

如果
insert into #T select '张三','2002-02-01',110

结果为：

张三 Feb 1 200 Feb 1 200 100
张三 Feb 1 200 May 1 200 110
张三 Jan 1 200 Jan 1 200 100
张三 Jul 1 200 Jul 1 200 120
张三 Jun 1 200 Jun 11 200 100

• 打赏
• 举报

回复 赞

[Jianli2004]

j9988(j9988) 的结果也不对

楼主的方法要7到8个查询，效率一定很差

• 打赏
• 举报

回复 赞

[edchild]

学习

• 打赏
• 举报

回复 赞

[Jianli2004]

楼主的也有问题：
测试数据

insert into #T select '张三','2002-02-01',110

结果：
姓名 开始日期 结束日期 金额
---- ------------------------------------------------------ ------------------------------------------------------ -----------
李四 2002-02-01 00:00:00.000 2002-02-01 00:00:00.000 100
李四 2002-03-01 00:00:00.000 2002-03-01 00:00:00.000 110
李四 2002-04-01 00:00:00.000 2002-04-01 00:00:00.000 90
李四 2002-05-01 00:00:00.000 2002-06-01 00:00:00.000 100
李四 2002-07-01 00:00:00.000 2002-07-01 00:00:00.000 150
王五 2002-02-01 00:00:00.000 2002-02-01 00:00:00.000 100
王五 2002-03-01 00:00:00.000 2002-05-01 00:00:00.000 90
王五 2002-06-01 00:00:00.000 2002-06-01 00:00:00.000 100
王五 2002-07-01 00:00:00.000 2002-07-01 00:00:00.000 80
张三 2002-01-01 00:00:00.000 2002-02-01 00:00:00.000 100
张三 2002-02-01 00:00:00.000 2002-02-01 00:00:00.000 100
张三 2002-02-01 00:00:00.000 2002-02-01 00:00:00.000 110
张三 2002-02-01 00:00:00.000 2002-05-01 00:00:00.000 110
张三 2002-02-01 00:00:00.000 2002-06-11 00:00:00.000 100
张三 2002-07-01 00:00:00.000 2002-07-01 00:00:00.000

• 打赏
• 举报

回复 赞

[w_rose]

好累！

可以参考一下我在另一个帖子中写的：
http://expert.csdn.net/Expert/topic/2678/2678539.xml?temp=5.524844E-02

select 姓名,成绩,(select count(*)+1 from 成绩表 where 姓名=x.姓名
and 成绩<x.成绩) from 成绩表 as x

这一句当数据量很小时运行很快，当数据量很大时，对于每一条记录，都要再用子查询搜索出一堆记录然后统计，这时候简单笨拙的顺序扫描算法就更快的多。

而楼上的诸多方法都喜欢绕弯弯。

• 打赏
• 举报

回复 赞

[realgz]

--1、修正下语句贴出来：
SELECT t1.姓名 ,min(t1.日期) 开始 ,max(t1.日期) 结束,金额
from
(select t1.* ,(select count(*) from #t t2
where t2.姓名=t1.姓名 and t2.日期>t1.日期
and not exists (select 1 from #t t3 where t3.姓名=t2.姓名 and t3.日期=dateadd(month,-1,t2.日期) and t3.金额=t1.金额 ) ) cnt from #t t1
) t1
group by 姓名,金额,cnt
order by 姓名,开始
--2、大家看看J老师语录：
/*
j9988 says:
这些题所要完成的功能，无非就是分组查询，但少了可分组的键，必须人为加上。再一句GROUP BY
j9988 says:
但不能象原贴上，用一句来。这样会死机的。
j9988 says:
短短十来条记录就用几十上百MS。上万条记录在多用户下就得完蛋。
j9988 says:
所以，不论语句写得多好，对于表不健全的情况，都没用。且索引利用率相当低。十几条记录被HASH到几百上万条记录来筛选，真是笨。
j9988 says:
练语句可以，实战不行。
j9988 says:
我记得去年有一个问题，楼主是上亿条记录。要从中找出NAME同时含有A，B，C的ID，一句GROUP BY所用的时间是几百毫秒。
http://www.csdn.net/develop/Read_Article.asp?Id=23436
*/

• 打赏
• 举报

回复 赞

[prcgolf]

xuexi!1

• 打赏
• 举报

回复 赞

[Tomato77]

mark

• 打赏
• 举报

回复 赞

[TonyXiong]

好!

該問題的關鍵是在如何判斷下一個月的工資是否和這個月相同的問題上

其它的都好辦

• 打赏
• 举报

回复 赞