最好把查询条件的值'00001','00007','00012','00004','00009','00010','00016','00018','00020'等放到临时表t_temp的ID字段中,然后连接查询: select MY_TB.* from MY_TB,t_temp where MY_TB.ID=*t_temp.ID
赞同: pbsql(风云)的方法,还在就是不知道你是怎么得到值'00001','00007','00012','00004','00009','00010','00016','00018','00020'这个的,这就决定了你的临时表的处理难度的问题,做好临时表, select b.my_id,isnull(a.name,'暂无') as name,isnull(a.count,'空') as count from my_TB A left outer join #temp b on a.my_id=b.My_id
exec(' select b.my_id,isnull(a.name,''暂无'') as name,isnull(a.count,''空'') as count from my_TB A left outer join '+ 'select my_id='+replace(@YourStr,',' union select ') +' b on a.my_id=b.My_id')/* 'select my_id='+replace(@YourStr,',' union select ') 这个语句是把你的字符串变成一个派生表,如下: (select my_id='00001' union select '00002' union select '00003'.....) */
更正:少了一对括号exec(' select b.my_id,isnull(a.name,''暂无'') as name,isnull(a.count,''空'') as count from my_TB A left outer join ( '+ 'select my_id='+replace(@YourStr,',' union select ') +') b on a.my_id=b.My_id')
可以的,在where条件中就可以,就象你上面所说的IN(....)可是你这儿的问题这样没办法处理。
老大那不能在FROM里用IN或是其它的让这一串成为集合的?
也就是说能不能让这一串字符怎么变一下放在FROM中用,如下 select b.*,a.name,a.count from MY_TB as a right outer join ('00001','00007','00012','00004','00009','00010','00016','00018','00020') as b on a.id = b.id
赞同: pbsql(风云)的方法, 这种问题必须要使用外连接查询,如果你不想用存储过程的话。还在就是不知道你是怎么得到值'00001','00007','00012','00004','00009','00010','00016','00018','00020'这个的,这就决定了你的临时表的处理难度的问题,做好临时表, select b.my_id,isnull(a.name,'暂无') as name,isnull(a.count,'空') as count from my_TB A left outer join #temp b on a.my_id=b.My_id
可以这样写: 先建一临时表tem_id id char(10) 存放你要取的idselect id, count=(select NAME from MY_TB where MY_ID=id), COUNT=(select MY_ID from MY_TB where MY_ID=id) from tem_id
(对前面的贴修改一下,有点笔误) 可以这样写:
先建一临时表tem_id id char(10) 存放你要取的idselect id, NAME=(select NAME from MY_TB where MY_ID=id), COUNT=(select COUNT from MY_TB where MY_ID=id) from tem_id
CrazyFor(烟草),老大问题又变得严重了! 当union很多时会缓存缢出啊!!这时候又该怎么办?? 如下: select id = '00000' union select '00001' union select '00002' union select '00003' union select '00004' ^^^ ^^^ ^^^ ^^^(当数量超过500时,就会缓存缢出!!!) ^^^ ^^^ ^^^
select MY_TB.* from MY_TB,t_temp where MY_TB.ID=*t_temp.ID
select b.my_id,isnull(a.name,'暂无') as name,isnull(a.count,'空') as count from my_TB A left outer join #temp b on a.my_id=b.My_id
select b.my_id,isnull(a.name,''暂无'') as name,isnull(a.count,''空'') as count from my_TB A left outer join
'+
'select my_id='+replace(@YourStr,',' union select ')
+'
b on a.my_id=b.My_id')/*
'select my_id='+replace(@YourStr,',' union select ')
这个语句是把你的字符串变成一个派生表,如下:
(select my_id='00001' union select '00002' union select '00003'.....)
*/
select b.my_id,isnull(a.name,''暂无'') as name,isnull(a.count,''空'') as count from my_TB A left outer join (
'+
'select my_id='+replace(@YourStr,',' union select ')
+')
b on a.my_id=b.My_id')
select b.*,a.name,a.count from MY_TB as a right outer join ('00001','00007','00012','00004','00009','00010','00016','00018','00020') as b on a.id = b.id
将要查找的id放在另一张表中(这样也可以由用户录入),设此表为:Temp_tb,字段ID存放要查找的人员id,两张表用外连接查询,具体的数据库不同,外联接的语句有所不同,以SQL_SERVER为例:
select Temp_tb.id Name,Count,from MY_TB right join Temp_tb on MY_TB.id=Temp_tb.id
这样,在Temp_tb表中所有要查询的id都会显示,MY_TB 中没有的id以空值显示,你可以将Name替换成以“暂无”显示。
这种问题必须要使用外连接查询,如果你不想用存储过程的话。还在就是不知道你是怎么得到值'00001','00007','00012','00004','00009','00010','00016','00018','00020'这个的,这就决定了你的临时表的处理难度的问题,做好临时表,
select b.my_id,isnull(a.name,'暂无') as name,isnull(a.count,'空') as count from my_TB A left outer join #temp b on a.my_id=b.My_id
先建一临时表tem_id
id char(10) 存放你要取的idselect
id,
count=(select NAME from MY_TB where MY_ID=id),
COUNT=(select MY_ID from MY_TB where MY_ID=id)
from tem_id
可以这样写:
先建一临时表tem_id
id char(10) 存放你要取的idselect
id,
NAME=(select NAME from MY_TB where MY_ID=id),
COUNT=(select COUNT from MY_TB where MY_ID=id)
from tem_id
当union很多时会缓存缢出啊!!这时候又该怎么办??
如下:
select id = '00000'
union
select '00001'
union
select '00002'
union
select '00003'
union
select '00004'
^^^
^^^
^^^
^^^(当数量超过500时,就会缓存缢出!!!)
^^^
^^^
^^^