22,209
社区成员
发帖
与我相关
我的任务
分享
--> Title : Generating test data [tb]
--> Author : wufeng4552
--> Date : 2009-11-19 11:17:23
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] ([NO] varchar(20),ID int identity)
insert into [tb]
select '70,120,70,150,95 ' union all
select '70,120,95 ' union all
select '120,95,150' union all
select '70,95' union all
select '95,150' union all
select '95'
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
begin
return(
select col=min(cast(substring(@s,number,charindex(',',@s+',',number)-number)as int))
from master..spt_values
where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
end
go
select
dbo.UF_minget([No]),
case when dbo.UF_minget([No])=70 then 200
when dbo.UF_minget([No])=120 then 290
when dbo.UF_minget([No])=95 then 200
when dbo.UF_minget([No])=150 then 290
end
from tb order by id
/*
----------- -----------
70 200
70 200
95 200
70 200
95 200
95 200
(6 個資料列受到影響)
*/
刚才那个有点问题
declare @tb table([id] int,[values] varchar(18))
insert @tb
select 1,'70,120,70,150,95' union all
select 2,'70,120,95' union all
select 3,'120,95,150' union all
select 4,'70,95' union all
select 5,'95,150' union all
select 5,'95,150' union all
select 5,'120,150' union all
select 5,'95'
declare @tb1 table([f1] int,[f2] int)
insert @tb1
select 70,200 union all
select 120,290 union all
select 95,200 union all
select 150,290
SELECT TOP 100 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT T1.ID, T1.[values], T2.[f2]
FROM (
SELECT ID, [values], MIN(NUM) AS NUM
FROM (
SELECT A.id, A.[values], CAST(SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) AS INT) AS NUM
FROM @tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
) P
GROUP BY ID, [values]
) AS T1 INNER JOIN @tb1 T2 ON T1.NUM = T2.[f1]
DROP TABLE #
/*
ID values f2
----------- ------------------ -----------
5 120,150 290
3 120,95,150 200
1 70,120,70,150,95 200
2 70,120,95 200
4 70,95 200
5 95 200
5 95,150 200
(7 row(s) affected)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-19 14:30:42
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([id] int,[value] varchar(16))
insert [tb1]
select 1,'70,120,70,150,95' union all
select 2,'70,120,95' union all
select 3,'120,95,150' union all
select 4,'70,95' union all
select 5,'95,150' union all
select 6,'95'
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([id] int,[value] int)
insert [tb2]
select 70,200 union all
select 120,290 union all
select 95,200 union all
select 150,290
--------------开始查询--------------------------
;with f as
(
Select
a.id,[value]=substring(a.[value],b.number,charindex(',',a.[value]+',',b.number)-b.number)
from
Tb1 a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[value])
where
substring(','+a.[value],b.number,1)=','
)
select
*
from
(select id,min(value) as value from f group by id) a
join
(select id0=row_number()over(order by id),* from tb2)b
on
a.id=b.id0
----------------结果----------------------------
/*id value id0 id value
----------- ---------------- -------------------- ----------- -----------
1 120 1 70 200
2 120 2 95 200
3 120 3 120 290
4 70 4 150 290
(4 行受影响)
*/
declare @tb table([id] int,[values] varchar(18))
insert @tb
select 1,'70,120,70,150,95' union all
select 2,'70,120,95' union all
select 3,'120,95,150' union all
select 4,'70,95' union all
select 5,'95,150' union all
select 5,'95'
declare @tb1 table([f1] int,[f2] int)
insert @tb1
select 70,200 union all
select 120,290 union all
select 95,200 union all
select 150,290
SELECT TOP 100 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT T1.ID, T1.[values], T2.[f2]
FROM (
SELECT ID, [values], MIN(NUM) AS NUM
FROM (
SELECT A.id, A.[values], SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) AS NUM
FROM @tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
) P
GROUP BY ID, [values]
) AS T1 INNER JOIN @tb1 T2 ON T1.NUM = T2.[f1]
DROP TABLE #
/*
ID values f2
----------- ------------------ -----------
3 120,95,150 290
1 70,120,70,150,95 290
2 70,120,95 290
4 70,95 200
5 95 200
5 95,150 290
(6 row(s) affected)
create table tb(id int, NO2 varchar(50))
insert into tb values(1,'70,120,70,150,95')
insert into tb values(2,'70,120,95')
insert into tb values(3,'120,95,150')
insert into tb values(4,'70,95')
insert into tb values(5,'95,150')
insert into tb values(6,'95')
create table glb(no2 int, show int)
insert into glb values(70 ,200)
insert into glb values(120,290)
insert into glb values(95 ,200)
insert into glb values(150,290)
go
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
select m.* , n.show from
(
select id , min(no2) no2 from
(
SELECT A.id, NO2 = SUBSTRING(A.[NO2], B.id, CHARINDEX(',', A.[NO2] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[NO2], B.id, 1) = ','
) t group by id
) m , glb n
where m.no2 = n.no2
order by id
DROP TABLE #
drop table tb , glb
/*
id no2 show
----------- -------------------------------------------------- -----------
1 120 290
2 120 290
3 120 290
4 70 200
5 150 290
6 95 200
(所影响的行数为 6 行)
*/
--> Title : Generating test data [tb]
--> Author :
--> Date : 2009-11-19
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] ([NO] varchar(20))
insert into [tb]
select '70,120,70,150,95 ' union all
select '70,120,95 ' union all
select '120,95,150' union all
select '70,95' union all
select '95,150' union all
select '95'
if object_id('UF_minget')is not null drop function UF_minget
go
create function UF_minget
(@s varchar(200))
returns int
as
begin
return(
select col=min(substring(@s,number,charindex(',',@s+',',number)-number))
from master..spt_values
where type='p' and number<=len(@s+'a') and charindex(',',','+@s,number)=number)
end
go
select case when dbo.UF_minget([No])=70 then 200
when dbo.UF_minget([No])=120 then 290
when dbo.UF_minget([No])=95 then 200
when dbo.UF_minget([No])=150 then 290
end
from tb
/*
(6 個資料列受到影響)
-----------
290
290
290
200
290
200
(6 個資料列受到影響)
*/
declare @tb table([id] int,[values] varchar(8))
insert @tb
select 1,'10,20,30' union all
select 2,'20,30,50' union all
select 3,'10,30' union all
select 4,'30,50' union all
select 5,'30'
declare @tb1 table([f1] int,[f2] int)
insert @tb1
select 10,200 union all
select 20,290 union all
select 30,200 union all
select 50,290
SELECT TOP 100 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT T1.ID, T1.[values], T2.[f2]
FROM (
SELECT ID, [values], MIN(NUM) AS NUM
FROM (
SELECT A.id, A.[values], SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) AS NUM
FROM @tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
) P
GROUP BY ID, [values]
) AS T1 INNER JOIN @tb1 T2 ON T1.NUM = T2.[f1]
DROP TABLE #
/*
ID values f2
----------- -------- -----------
1 10,20,30 200
3 10,30 200
2 20,30,50 290
5 30 200
4 30,50 200
(5 row(s) affected)