解决方案 »
- sql server 2005 创建新的数据库文件 提示空间不够
- 一道面试题:请问SQL语句能解决所有查询问题吗???如何证明??
- SQL 数据库字段怎么改为递增的属性?
- 高分求够sql 语句
- SQL2008的登录问题,sa用户可以登录,windows用户登录不了
- SQL最终查询
- 我的sqlserver 2000 里被人创建了N个表
- 求一个独立的sql分页储存过程
- 请问各位高手,如何在PB中调用SQL SERVER中自定义函数?
- 在SQL SERVER 2000下,怎么分辨数据?
- 小弟求教,怎么把VARCHAR转换成INT啊
- 如何把A服务器sql数据库中的数据,通过B服务器中的web网站查询出来
来得够早的。
而当Num<90000时不用特别处理,而当Num>90000时,需要特别处理。
主要看后四位数字,第2、3位为一组作为起始数字,第4、5位为一组作为结束数字。
select b.Number Num,a.count2,a.code
from tb a
left join master..spt_values b
on a.Num>90000 and b.type='p'
and b.number between substring(rtrim(a.Num),2,2) and right(a.Num,2)
union all
select Num,count2,code
from tb
where Num<=900000
希望将Num字段大于90000拆分成多条连续数值的记录,而不是单条字符串的记录。
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-05 14:18:24
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([Num] int,[Count2] int,[Code] Datetime)
Insert tb
Select 92126,200,'100202' union all
Select 94752,100,'100202' union all
Select 19,200,'100201' union all
Select 20,100,'100201'
Go
--Select * from tb-->SQL查询如下:
select b.number num,a.[Count2],a.code
from tb a
join master..spt_values b
on b.type='p' and a.num>90000
and b.number>=(a.num-90000)/100
and b.number<=(num-90000)%100
union all
select * from tb where num<90000
/*
num Count2 code
----------- ----------- -----------------------
21 200 2010-02-02 00:00:00.000
22 200 2010-02-02 00:00:00.000
23 200 2010-02-02 00:00:00.000
24 200 2010-02-02 00:00:00.000
25 200 2010-02-02 00:00:00.000
26 200 2010-02-02 00:00:00.000
47 100 2010-02-02 00:00:00.000
48 100 2010-02-02 00:00:00.000
49 100 2010-02-02 00:00:00.000
50 100 2010-02-02 00:00:00.000
51 100 2010-02-02 00:00:00.000
52 100 2010-02-02 00:00:00.000
19 200 2010-02-01 00:00:00.000
20 100 2010-02-01 00:00:00.000(14 行受影响)
*/
谢谢,我先试试。
to js_szy
第一组数字一定小于第二组数字的。
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-05 14:08:24
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([Num] int,[Count2] int,[Code] varchar(10))
Insert tb
Select 92126,200,'100202' union all
Select 94752,100,'100202' union all
Select 19,200,'100201' union all
Select 20,100,'100201'
Go
--Select * from tb-->SQL查询如下:
select b.number num,a.[Count2],a.code
from tb a
join master..spt_values b
on b.type='p' and a.num>90000
and b.number>=(a.num-90000)/100
and b.number<=(a.num-90000)%100
union all
select * from tb where num<90000
/*
num Count2 code
----------- ----------- ----------
21 200 100202
22 200 100202
23 200 100202
24 200 100202
25 200 100202
26 200 100202
47 100 100202
48 100 100202
49 100 100202
50 100 100202
51 100 100202
52 100 100202
19 200 100201
20 100 100201(14 行受影响)
*/字符变日期了,修改一下.
你们二位的办法都行,谢谢!
不过我还要好好理解一下,
结贴。
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int,[Count2] int,[Code] varchar(10))
insert [tb]
select 92126,200,100202 union all
select 94752,100,100202 union all
select 19,200,100201 union all
select 20,100,100201
---查询---
select
si+b.number as num,
count2,
code
from
(select
case when num>9000 then substring(ltrim(num),2,2) else num end as si,
case when num>9000 then right(num,2) else 0 end as ei,
*
from [tb]
) t,master..spt_values b
where
b.type='P' and b.number>=0
and
(si+b.number<ei and ei>0 or ei=0 and b.number=0)---结果---
num count2 code
----------- ----------- ----------
21 200 100202
22 200 100202
23 200 100202
24 200 100202
25 200 100202
47 100 100202
48 100 100202
49 100 100202
50 100 100202
51 100 100202
19 200 100201
20 100 100201(所影响的行数为 12 行)
实在不好意思,贴已经结了,没分了,下次有机会我一定给您加上,
不过我觉得您也不是为了得分来的,谢谢你。
嘿嘿...(si+b.number<=ei and ei>0 or ei=0 and b.number=0)这里少了个等号
--参考:
select b.Number Num,a.count2,a.code
from tb a
left join master..spt_values b
on a.Num>90000 and b.type='p'
and b.number between substring(rtrim(a.Num),2,2) and right(a.Num,2)
union all
select Num,count2,code
from tb
where Num<=900000
create table #t
(
Num int,
Count2 int,
Code varchar(20)
)
insert into #t
select 92126, 200, '100202' union
select 94752, 100, '100202' union
select 19 , 200, '100201' union
select 20 , 100, '100201' select * from #tdrop table #tt
select * into #tt from #t where 1=0declare @start int
declare @end int
declare @tnum int
declare @tcount2 int
declare @tcode varchar(20)
declare @tsql varchar(4000)declare num_cursur Cursor
for
select Num,Count2,Code from #topen num_cursur
fetch num_cursur into @tnum,@tcount2,@tcode
while @@FETCH_STATUS = 0
begin
if @tnum >= 90000
begin
set @start = cast(SUBSTRING(cast(@tnum as varchar(5)), 2,2) as int)
set @end = cast(SUBSTRING(cast(@tnum as varchar(5)), 4,2) as int)
select @start,@end
while @start <= @end
begin
set @tsql = 'insert into #tt select ' + cast(@start as varchar(20)) +','+ CAST(@tcount2 AS VARCHAR(20))+','+@tcode
exec(@tsql)
set @start = @start + 1
end
end
else
begin
set @tsql = 'insert into #tt select ' + cast(@tnum as varchar(20)) +','+ cast(@tcount2 as varchar(20))+','+@tcode
exec(@tsql)
end
fetch num_cursur into @tnum,@tcount2,@tcode
end
close num_cursur
deallocate num_cursurselect * from #tt
表T1有字段num count2 code,数据如上
查询语句如下
select t2.num,t1.count2 ,t1.code
from t2,t1
where t2.num between to_number(substr(t1.num,2,2)) and to_number(substr(t1.num,4,2))
and t1.num> 90000
union
select * from t1 where t1.num<= 90000
from tb a
left join master..spt_values b
on a.Num>90000 and b.type='p'
and b.number between substring(rtrim(a.Num),2,2) and right(a.Num,2)
union all
select Num,count2,code
from tb
where Num<=900000
如果不能重新设计,那上面添加1-100的辅助表,就是霸道,但貌似应该是union all,除非确保不重复
不能加辅助表,只能写什么存储过程,或者超复杂表达式的,属于鸡鸣狗盗