如何判断年龄范围？

[DreamingEric]

假设有数据：
Id AgeRange
1 1,10
2 8,15

AgeRange的规则是“下限,上限”,那如何按提供过来的数字进行比较呢？

譬如：
1、查找值是'1,'，这样是大于1岁没有上限，那出来的结果应该是Id:[1,2]
2、查找值是'7,9,'，这样是查找7到9岁之间，那出来的结果应该是Id:[1,2]
3、查找值是'5,6,'，这样是查找5到6岁之间，那出来的结果应该是Id:[1]

[吉普赛的歌]

USE tempdb
GO
--创建测试表和测试数据
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(Id INT PRIMARY KEY,AgeRange VARCHAR(50))
GO
INSERT INTO t VALUES (1,'1,10');
INSERT INTO t VALUES (2,'8,15');
GO
--创建分割表值函数
IF OBJECT_ID('[dbo].[Fun_String2ToIntArray]') IS NOT NULL 
    DROP FUNCTION [dbo].[Fun_String2ToIntArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToIntArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE (rowNum INT IDENTITY(1,1), [item] INT)
AS
BEGIN
    IF LEN(@split) = 0
      BEGIN
        SET @split = N','
      END
 
    DECLARE @xml XML;
    SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
 
    INSERT INTO @table(item)
      SELECT item
      FROM   (SELECT c.value('text()[1]', 'int') [item]
              FROM   @xml.nodes('/x') t(c)) t
      WHERE  item IS NOT NULL
 
    RETURN
END
GO
--创建查找函数
IF OBJECT_ID('[dbo].[Fun_Search]') IS NOT NULL 
    DROP FUNCTION [dbo].Fun_Search
GO
CREATE FUNCTION [dbo].Fun_Search(@p1 VARCHAR(20))
RETURNS TABLE 
AS
RETURN 
(
	WITH ctePara AS(
		SELECT TOP 1 item AS paraBeginRange  
			,(SELECT TOP 1 item 
			  FROM dbo.Fun_String2ToIntArray(@p1,',') 
			  ORDER BY rowNum DESC) AS paraEndRange
		FROM dbo.Fun_String2ToIntArray(@p1,',') ORDER BY rowNum
	),cteT AS (
		SELECT *
			,SUBSTRING(AgeRange,1,CHARINDEX(',',AgeRange)-1) AS beginRange
			,SUBSTRING(AgeRange,CHARINDEX(',',AgeRange)+1,LEN(AgeRange)) AS endRange 
		FROM t	
	)
	SELECT * FROM cteT CROSS APPLY ctePara WHERE EXISTS(
		SELECT * FROM ctePara WHERE 
			(ctePara.paraBeginRange BETWEEN cteT.beginRange AND cteT.endRange)
			OR 
			(ctepara.paraEndRange BETWEEN cteT.beginRange AND cteT.endRange)
	)
	OR EXISTS(
		SELECT * FROM ctePara WHERE parabeginrange=paraendrange AND ctePara.paraBeginRange<=cteT.beginRange
	)
)
GO
--1、查找值是'1,'，这样是大于1岁没有上限，那出来的结果应该是Id:[1,2]
SELECT * FROM dbo.Fun_Search('1,')
--2、查找值是'7,9,'，这样是查找7到9岁之间，那出来的结果应该是Id:[1,2]
SELECT * FROM dbo.Fun_Search('7,9,')
--3、查找值是'5,6,'，这样是查找5到6岁之间，那出来的结果应该是Id:[1]
SELECT * FROM dbo.Fun_Search('5,6,')

不过， 建议你把表就设计成 beginRange , endRange
参数也改成 2 个， @paraBeginRange, @paraEndRange
你觉得可以精简的东西， 最终还是要去分解， 而且效率降低了……

[学海无涯-回头是岸]

你用拆分的方式把右边的agerange 拆开，再比较

[zbdzjx]

with t1 as
(
select 1 Id, '1,10' AgeRange union all
select 2 Id, '8,15' AgeRange
)
, t2 as
(
select '1,' age union all
select '7,9,' age union all
select '5,6,' age
)
, t1_1 as
(
select Id, AgeRange
, CONVERT(int, SUBSTRING(agerange,1, patindex('%,%', AgeRange)-1)) BeginAge
, CONVERT(int, SUBSTRING(agerange, patindex('%,%', AgeRange)+1, 10)) EndAge 
from t1
)
, t2_1 as
(
select age
, CONVERT(int, SUBSTRING(age,1, patindex('%,%', age)-1)) BeginAge
, CONVERT(int, case when age like '%,%,' then SUBSTRING(age, patindex('%,%', age)+1, LEN(age)-patindex('%,%', age)-1) else 99999 end) EndAge 
from t2
)
select * from t2_1 a left join t1_1 b on a.BeginAge<=b.EndAge and a.EndAge>=b.BeginAge

[shoppo0505]

建议把开始和结束范围分成两列

[等待戈多12]

-- 创建测试数据
if OBJECT_ID('tb','U') is not null
drop table tb
create table tb(id int, agerange varchar(10))
go
insert into tb(id, agerange)
select 1, '1,10' union all
select 2, '8,15'

-- 声明变量及输入值
declare @input varchar(10)
set @input = '7,9'

-- 查询
select *
from tb
where 
	cast(left(@input, charindex(',',@input) - 1 ) as int) 
		between cast(left(agerange, charindex(',',agerange) - 1 ) as int) 
		and cast(substring(agerange, charindex(',',agerange) + 1, len(agerange)) as int)
	or 
	(
	cast(substring(@input, charindex(',',@input) + 1, len(@input)) as int) 
		between cast(left(agerange, charindex(',',agerange) - 1 ) as int) 
		and cast(substring(agerange, charindex(',',agerange) + 1, len(agerange)) as int)
	)	
union 
select *
from tb
where substring(@input, charindex(',',@input) + 1, len(@input)) =''
and (cast(left(@input, charindex(',',@input) - 1 ) as int) 
		< cast(substring(agerange, charindex(',',agerange) + 1, len(agerange)) as int)
	)