declare @Str varchar(8000)
select @Str=replace(工号集,';',',') from 主表1
exec('select 日期,工号,产量,部门 from 主表2 where 工号 in ('''+@Str+''') from 主表1)')
select @Str=replace(工号集,';',',') from 主表1
exec('select 日期,工号,产量,部门 from 主表2 where 工号 in ('''+@Str+''') from 主表1)')
returns(varchar(8000))
as
begin
declare @Str varchar(8000)
select @Str=replace(工号集,';',''+char(39)+','+char(39)+'') from 主表1
return @Str
end
godeclare @Str varchar(8000)
select @Str=dbo.test(工号集) from 主表1
exec('select 日期,工号,产量,部门 from 主表2 where convert(varchar,工号) in ('''+@Str+''') from 主表1)')
charindex(','+b.工号+',' , ','+a.工号集+',')>0or:
select * from 主表1 a left join 从表2 on ','+a.工号集+',' like '%,'+b.工号+',%'
select * from 主表1 a left join 从表2 on ','+a.工号集+',' like '%,'+b.工号+',%'
结果被面试那哥们否定了,然后我又说出了nboys() ( ) 的方法,又被否定,他说的结果是:一个SQL语句是搞不定的,必须写程序先把主表的工号集分开成N笔单独的工号资料,然后再连接到从表。争了一番,我说搞台机子试试,被拒绝。回家之后没有测试
(
日期 datetime,
工号 char(4),
产量 int)declare @ccy1 datetime,@ccy2 char(255),@ccy3 intdeclare ccy_cursor cursor for
select 日期,工号集,产量 from 主表1open ccy_cursorfetch next from ccy_cursor into @ccy1,@ccy2,@ccy3while @@fetch_status = 0
begin
while ltrim(@ccy2)<>''
begin
insert into #temp_table(日期,工号,产量) values (@ccy1,left(@ccy2,4),@ccy3)
set @ccy2 = substring(@ccy2,6,255)
endfetch next from ccy_cursor into @ccy1,@ccy2,@ccy3
endclose ccy_cursor
deallocate ccy_cursorselect 日期,从表2.工号,产量,部门 from 从表2,#temp_table where 从表2.工号=#temp_table.工号select * from #temp_table
select * from 主表1
select * from 从表2drop table #temp_table
Date varchar(10) NOT NULL default '',
Uids varchar(30) default NULL,
Output int default NULL
)
INSERT INTO ta (Date, Uids, Output) VALUES('2003.03.01', '0231;0236;0237;0451', '120');
INSERT INTO ta (Date, Uids, Output) VALUES('2003.03.02', '0110;0125;0223;0231;0258', '200');
INSERT INTO ta (Date, Uids, Output) VALUES('2003.03.03', '0056;0256;0223', '52');CREATE TABLE tb (
Uid varchar(4) default NULL,
Depa varchar(10) default NULL,
Sex char(1) default NULL
)
INSERT INTO tb (Uid, Depa, Sex) VALUES('0110', 'A01', '1');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0056', 'A01', '0');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0125', 'A02', '1');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0223', 'A01', '1');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0231', 'A02', '1');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0236', 'A03', '0');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0237', 'A02', '1');
INSERT INTO tb (Uid, Depa, Sex) VALUES('0451', 'A02', '1');SELECT ta.Date,ta.Uids, tb.Uid, tb.Depa, ta.Output
FROM ta
LEFT JOIN tb ON ta.Uids LIKE '%'+ tb.Uid +'%';Date Uids Uid Depa Output
---------- ------------------------------ ---- ---------- -----------
2003.03.01 0231;0236;0237;0451 0231 A02 120
2003.03.01 0231;0236;0237;0451 0236 A03 120
2003.03.01 0231;0236;0237;0451 0237 A02 120
2003.03.01 0231;0236;0237;0451 0451 A02 120
2003.03.02 0110;0125;0223;0231;0258 0110 A01 200
2003.03.02 0110;0125;0223;0231;0258 0125 A02 200
2003.03.02 0110;0125;0223;0231;0258 0223 A01 200
2003.03.02 0110;0125;0223;0231;0258 0231 A02 200
2003.03.03 0056;0256;0223 0056 A01 52
2003.03.03 0056;0256;0223 0223 A01 52(所影响的行数为 10 行)
呵呵,
遇到这个问题
解决办法???打击他一下,
然后走人...........