如何使用select查询获取输出 [英] how to get my output using select query
本文介绍了如何使用select查询获取输出的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表格如下(Tablename Batch)
Bthid Facid
1 1,4,6
2 5,9,11
3 4
4 11
数据类型如下
Bthid bigint
Facid varchar(40)
查询如下
选择 Bthid 来自 bthfac 其中 Facid = ' 4'
当我按以下方式运行上述查询输出时
Bthid
3
但我希望输出如下
Bthid
1
3
因为Facid 4也出现在上表中的Bthid 1中Batch
解决方案
使用时如果你将搜索下面的记录,
其中facid喜欢%1%'它将显示错误的o / p(将显示11的记录也因为它包含1)
要么你应该saparate表或用逗号解析facid列saparator然后搜索facid。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo]。[ParseValues]
( @String varchar ( 8000 ), @分隔符 varchar ( 10 ))
RETURNS @ RESUL TS TABLE (ID int identity ( 1 , 1 ),Val varchar ( 50 ))
AS
BEGIN
DECLARE @ Value varchar ( 100 )
WHILE @ String 不 null
BEGIN
SELECT @ Value = CASE WHEN PATINDEX(' %' + @Delimiter + ' %', @ String ) > 0 那么 LEFT ( @ String ,PATINDEX(' %' + @Delimiter + ' %', @ String ) - 1) ELSE @ String END ,@ String = CASE WHEN PATINDEX(' %' + @Delimiter + ' %', @ String )> 0 那么 SUBSTRING( @ String ,PATINDEX(' %' + @Delimiter + ' %', @ String )+ LEN( @Delimiter ),LEN( @ String )) ELSE NULL END
INSERT INTO @ RESULTS (Val)
SELECT @ Value
END
返回
END< br $>
查询
选择 b.bthid,pv.val as facId
来自 bthfac b
cross apply dbo.ParseValues(facid,' ,' ) as pv
其中 pv.val = 4
快乐编码!
:)
选择 Bthid 来自 bthfac WHERE
Facid LIKE ' %,' + @ search + ' ,%' - middle
或
Facid LIKE @ search + ' , %' - 开始
或
Facid LIKE ' %,' + @ search - end
< span class =code-keyword> OR
Facid = @ search - single
欲了解更多信息,请参阅
http:// stackoverflow。 com / questions / 5611715 / where-value-in-column-containing-逗号分隔值 [ ^ ]
你需要使用
其中 facid 喜欢 ' %4%'
Table as follows(Tablename Batch)
Bthid Facid
1 1,4,6
2 5,9,11
3 4
4 11
Datatype as follows
Bthid bigint
Facid varchar(40)
Query as follows
select Bthid from bthfac where Facid = '4'
When i run the above query output as follows
Bthid
3
But i want the output as follows
Bthid
1
3
Because Facid 4 is also there in the Bthid 1 in the above table Batch
解决方案
when using like if you will search record like below,
where facid like '%1%' it will show wrong o/p (will show record for 11 also because it contains 1)
either you should saparate tables or parse facid column by comma saparator and then search for facid.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ParseValues] (@String varchar(8000), @Delimiter varchar(10) ) RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50)) AS BEGIN DECLARE @Value varchar(100) WHILE @String is not null BEGIN SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END INSERT INTO @RESULTS (Val) SELECT @Value END RETURN END
query
select b.bthid, pv.val as facId from bthfac b cross apply dbo.ParseValues(facid,',') as pv where pv.val=4
Happy Coding!
:)
select Bthid from bthfac WHERE Facid LIKE '%,' + @search + ',%' --middle OR Facid LIKE @search + ',%' --start OR Facid LIKE '%,' + @search --end OR Facid = @search --single
For more info refer
http://stackoverflow.com/questions/5611715/where-value-in-column-containing-comma-delimited-values[^]
You need to use
where facid like '%4%'
这篇关于如何使用select查询获取输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文