Sqlserver2000中。
表中有10个字段分别存放不同的配置,给定一个数值(取值1-10) 如何找到 Header=002 的记录中是否有 等于5的列
如果存在则 返回true,改怎么比较??是否是 把记录取出来,一个值一个值的比较?有没有其它办法?谢谢
test
Header(主键) Item1 Item2 Item3 Item4 Item5 Item6 Item7 Item8 Item9 Item10
001 1 3 2 5 7 9 4 6 8 10
002 3 4 7 1 5 2 8 0 0 0
---
003 ----------------------------------------------------------
表中有10个字段分别存放不同的配置,给定一个数值(取值1-10) 如何找到 Header=002 的记录中是否有 等于5的列
如果存在则 返回true,改怎么比较??是否是 把记录取出来,一个值一个值的比较?有没有其它办法?谢谢
test
Header(主键) Item1 Item2 Item3 Item4 Item5 Item6 Item7 Item8 Item9 Item10
001 1 3 2 5 7 9 4 6 8 10
002 3 4 7 1 5 2 8 0 0 0
---
003 ----------------------------------------------------------
Item1+','+Item2+','+Item3+','+Item4+','+Item5+','+Item6+','+Item7+','+Item8+','+Item9+','+Item10
如下面:Header(主键) 组合字段
001 1,3,2,5,7,9,4,6,8,10
002 3,4,7,1,5,2,8,0,0,0
declare @n int
set @n=5
然后利用select * from tb where charindex(','+ltrim(@n)+',',','+组合字段+',')>0
这样列出所以包含5这一列的记录
set @n=5
if exists (select * from tb
where charindex(','+ltrim(@n)+',',','+ltrim(Item1)+','+ltrim(Item2)+','+ltrim(Item3)+','+ltrim(Item4)+','
+ltrim(Item5)+','+ltrim(Item6)+','+ltrim(Item7)+','+ltrim(Item8)+','+ltrim(Item9)+','
+ltrim(Item10)+',')>0 and Header='002')
print('true')
else
print('fales')
where exists
(
select 1
from
(
select Item1 as Item
union all select Item2
union all select Item3
union all select Item4
union all select Item5
union all select Item6
union all select Item7
union all select Item8
union all select Item9
union all select Item10
)T
where Item=5
)
都检索,在程序中比较呢?
多谢 这个SQL语句不明白是什么意思
(
select Header,Item1 as Item from tb
union all
select Header,Item2 from tb
union all
select Header,Item3 from tb
union all
select Header,Item4 from tb
union all
select Header,Item5 from tb
union all
select Header,Item6 from tb
union all
select Header,Item7 from tb
union all
select Header,Item8 from tb
union all
select Header,Item9 from tb
union all
select Header,Item10 from tb
)select *
from test t
where not exists (select 1 from ach where Header = t.Header and Item = 5)
;with ach as
(
select Header,Item1 as Item from tb
union all
select Header,Item2 from tb
union all
select Header,Item3 from tb
union all
select Header,Item4 from tb
union all
select Header,Item5 from tb
union all
select Header,Item6 from tb
union all
select Header,Item7 from tb
union all
select Header,Item8 from tb
union all
select Header,Item9 from tb
union all
select Header,Item10 from tb
)select Header,
(case when exists (select 1 from ach where Header = t.Header and Item = 5) then 1 else 0 end) as flag
from test t
union
select item2 from test where Header='002'
union
...
然后针对这个再查询是否有值为5的记录