--1.创建一个合并的函数 Create function test(@kh char(6),@rq char(8)) returns varchar(1000) as begin declare @str varchar(1000) set @str='' select @str=@str+'/'+ wml_min from wml_mstr where wml_kh = @kh and left(wml_rq,6) = left(@rq ,6) set @str=right(@str,len(@str)-1) return(@str) End/* Usage: select distinct wml_kh,wml_rq,dbo.test(wml_kh,wml_rq) as wml_min into #tmp from wml_mstr where left(wml_rq,6) = '200306' Select wml_kh,wml_min,count(*) from #tmp group by wml_kh,wml_min having count(*) > 1 drop table #tmp */
select * from 表 tem where exists(select 1 from (select convert(char(8),日期,120) 日期,上午上班 from 表 where datediff(month,日期,getdate())=0 group by convert(char(8),日期,120),上午上班 having count(distinct 工号)>1) tem where 日期=convert(char(8),tem.日期,120) and 上午上班=tem.上午上班)
select * from 表 tem where exists(select 1 from (select convert(char(8),日期,120) 日期,上午上班 from 表 where datediff(month,日期,getdate())=0 group by convert(char(8),日期,120),上午上班 having count(distinct 工号)>1) tem where 日期=convert(char(8),tem.日期,120) and 上午上班=tem.上午上班)
1:(马可的) select distinct wml_kh,wml_rq,dbo.test(wml_kh,wml_rq) as wml_min into #tmp from wml_mstr where left(wml_rq,6) = '200306'服务器: 消息 536,级别 16,状态 1,过程 test,行 10 Invalid length parameter passed to the substring function. The statement has been terminated.
Create function test(@kh char(6),@rq char(8))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+'/'+ wml_min from wml_mstr
where wml_kh = @kh and left(wml_rq,6) = left(@rq ,6)
set @str=right(@str,len(@str)-1)
return(@str)
End/* Usage:
select distinct wml_kh,wml_rq,dbo.test(wml_kh,wml_rq) as wml_min into #tmp
from wml_mstr where left(wml_rq,6) = '200306'
Select wml_kh,wml_min,count(*) from #tmp group by wml_kh,wml_min having count(*) > 1
drop table #tmp
*/
select distinct wml_kh,wml_rq,dbo.test(wml_kh,wml_rq) as wml_min into #tmp
from wml_mstr where left(wml_rq,6) = '200306'服务器: 消息 536,级别 16,状态 1,过程 test,行 10
Invalid length parameter passed to the substring function.
The statement has been terminated.