客户生日的查找语句

[earthpea]

近来在编写一个系统的时候，遇到了一个生日的查找问题，需要大家的帮助。
数据表有二个，一个是客户信息表，一个是业务流水表

客户信息表：
客户号 varchar
客户姓名 varchar
客户生日 datetime
.......

业务流水表
会员卡号 varchar
销售人员代码 varchar
购买人客户号 varchar
使用人客户号 varchar
......

系统用销售人员代码登录，如123456，进入系统后可以查找当天过生日的客户和此后一个星期及此后一个月内过生日的客户资料（客户姓名、出生日期等），包括购买人和使用人都要找到，其中，业务流水表中有130万条记录，只有对销售人员代码进行索引，客户信息表有300万条记录，对客户号进行索引。
运行以下语句耗时０秒，结果有922条记录
select 购买客户号
from 业务表
where 销售人员 = '123456'
union
select 使用客户号
from 业务表
where 销售人员 = '123456'

运行以下语句耗时９秒，也是922条记录，正确
select * from 客户信息表
where
客户号 in(
select 购买客户号
from 业务表
where 销售人员 = '123456'
union
select 使用客户号
from 业务表
where 销售人员 = '123456')

最后，运行以下语句求后一个月内将要过生日的客户时，十分钟都没有出来
select * from 客户信息表
where
客户号 in(
select 购买客户号
from 业务表
where 销售人员 = '123456'
union
select 使用客户号
from 业务表
where 销售人员 = '123456')
and (DateAdd(year,Year(Getdate())-Year(客户生日),客户生日)
Between Convert(Varchar(10),Getdate(),120) And Convert(Varchar(10),Getdate()+30,120))

请教一下大家，怎么做才能很快的得出这个销售人员名下的客户的生日情况呢？十分感谢了。

业务流水表中，约有1.5万左右个销售人员，最少的销售人员拥有1份记录，最多的约5千份记录。如果对业务流水表中的二个客户号加索引有帮助吗？

[dawugui]

select * from 客户信息表 where 
datediff(day,getdate(),detename(year,getdate()) + datename(month,客户生日) + datename(day,客户生日)) <= 30
and 客户号 in
(
  select 购买人客户号 from 业务表 where 销售人员 = '123456'
  union all
  select 使用人客户号 from 业务表 where 销售人员 = '123456'
) 

[dawugui]

上面还有点不对,遇到跨年的没考虑,我再想想.

[dawugui]

仔细看了看,你的写法也没什么问题,关键在于上百万的记录用in的话时间是很长的.

[dobear_0922]

--try
select Ta.* 
from 客户信息表 Ta 
   join (select 客户号=购买人客户号 from 业务表 where 销售人员 = '123456'
         union all
         select 使用人客户号 from 业务表 where 销售人员 = '123456') Tb on Ta.客户号=Tb.客户号   
where datediff(day,getdate(),detename(year,getdate()) + datename(month,客户生日) + datename(day,客户生日)) <= 30

[earthpea]

如果建索引呢？在业务流水表中对购买人和使用人的客户号建索引会不会有所改善呢？

[dobear_0922]

--try 跨年的不太好解决
select Ta.* 
from 客户信息表 Ta 
   join (select 客户号=购买人客户号 from 业务表 where 销售人员 = '123456'
         union all
         select 使用人客户号 from 业务表 where 销售人员 = '123456') Tb on Ta.客户号=Tb.客户号   
where datediff(day,getdate(),DateAdd(year,Year(Getdate())-Year(客户生日),客户生日)) <= 30 
  or datediff(day,getdate(),DateAdd(year,Year(Getdate())-Year(客户生日)+1,客户生日)) <= 30 

[Limpire]

先给客户“客户生日”添加索引，应该会快很多。

[earthpea]

dobear_0922 的试过了，34秒后报了个错
服务器: 消息 241，级别 16，状态 1，行 1
从字符串转换为 datetime 时发生语法错误。

回Limpire：
我只对业务流水表有权限，客户表不能增加索引的。

[dobear_0922]

--try 解决跨年
select Ta.* 
from 客户信息表 Ta 
   join (select 客户号=购买人客户号 from 业务表 where 销售人员 = '123456'
         union all
         select 使用人客户号 from 业务表 where 销售人员 = '123456') Tb on Ta.客户号=Tb.客户号   
where datediff(day,getdate(),
case when dateadd(yy,datediff(yy,客户生日,getdate()),客户生日)>getdate() then dateadd(yy,datediff(yy,客户生日,getdate()),客户生日) else dateadd(yy,datediff(yy,客户生日,getdate())+1,客户生日) end)<=30    

[Limpire]

如果建索引呢？在业务流水表中对购买人和使用人的客户号建索引会不会有所改善呢？
-----------------
业务流水表部分是：
where 销售人员=???
购买人和使用人的客户号建索引不会有太大帮助。

考虑过之后，客户信息表.客户生日加索引用处也不大

临时表处理，才能提高速度：

select a.* into #TEMP
	from 客户信息表 a join
		(
			select 购买客户号 as 客户号
			from 业务表
			where 销售人员='123456'
			union
			select 使用客户号
			from 业务表
			where 销售人员='123456'
		) b
	on a.客户号=客户号

--这里照抄了：
select * from #TEMP where (DateAdd(year,Year(Getdate())-Year(客户生日),客户生日) 
	Between Convert(Varchar(10),Getdate(),120) And Convert(Varchar(10),Getdate()+30,120)) 

[Limpire]

对300多万的“客户生日”进行函数转换匹配，肯定快步到哪去。

放到临时表，一下子瘦了很多，你上面说了，是922条记录，在这些记录里再找匹配记录，应该快很多了，我预期不超过30秒。

[cxmcxm]

既然业务员最多有5000条记录,将此业务员的5000条记录先出后,再对此5000条记录与客户表连接

select a.购买人客户号,a.使用人客户号,b.客户姓名 购买人姓名,b.客户生日 购买人生日,
     c.客户姓名 使用人姓名,c.客户生日 使用人生日  into #tmp 
from 业务流水表 a,客户信息表 b,客户信息表 c where a.销售人员代码='录入的销售人员代码'
	and a.购买人客户号=b.客户号 and a.使用人客户号=c.客户号

再对最多为5000条记录的临时表#tmp进行查询

[earthpea]

嗯，临时表是个好办法，以前没有试过，但有两个问题：
1、临时表会在什么时候被自动删除？还是需要我在程序中删除？因为使用的人都不是太懂计算机的销售人员，他们可能会点“退出系统”，也可能直接关闭浏览器，也许点开看了一下客户生日后，再去看一下业绩情况，又回来点开看客户生日等。
2、在一定的范围内，也许会有数十名仍至上百名人员并发使用本系统，假设有十个人会同时点击查看客户生日的话，对临时表名和内容有没有冲突呢？还是SQL SERVER会自动分配？

谢谢了，呵呵。

[Limpire]

临时表
以一个井号（#）开头的那些表名，仅对当前会话可见，因此不同会话之间的同名临时表，并不互相冲突。
当前会话一旦结束，临时表自动销毁。
SQL中没有局部临时表的说法，临时表其实就是指局部临时表。

全局临时表
以两个井号（##）开头的那些表名，对所有会话可见。
如果在创建全局临时表的会话结束前没有显式地删除这些表，那么只要所有其它任务停止引用它们，这些表即被销毁。
当创建全局临时表的连接断开后，新的任务不能再引用它们。
当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被销毁。

[earthpea]

十分感谢大家，我就准备用临时表来进行操作了！
结帐，呵呵，希望能得到大家更多的帮助。