SQL范围查询用于非顺序字母数据值

本文介绍了SQL范围查询用于非顺序字母数据值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在开发一个网站，有两种形式的搜索屏幕和第一种形式的serachresultscreen我从两个下拉列表中选择一个范围（即从值1到值2）

范围是分别按顺序钻石清晰度

FL，IF，VVS，VVS1，VVS2，SI，SI1，I1，I2

当我在下一个表单上查询它时，例如

净度> = FL和清晰度< = VVS1

然后它显示结果，其中包含所有值字母F到V（即FL，IF，VVS，VVS1，VV2，SI，I1，I2

请建议我回答一下

我需要的输出是（当从= FL到= VVS1之间选择时）然后结果输出值FL，IF，VVS，VVS1只有

[edit]删除了虚假代码块 - OriginalGriff [/ edit]

i am developing a website there are two form searchscreen and serachresultscreen in first form i select a range from two dropdown list (i.e From value1 to value 2)

range is for diamond clarity like in order respectively

FL,IF,VVS,VVS1,VVS2,SI,SI1,I1,I2
when i range query it on next form like

clarity>=FL and clarity<=VVS1
then it show result with all values between alphabet F to V (i.e FL,IF,VVS,VVS1,VV2,SI,I1,I2

please suggest me some answere

My required output is(when selected between from=FL to=VVS1) then result output value FL,IF,VVS,VVS1 only

[edit]Spurious code block removed - OriginalGriff[/edit]

推荐答案

DECLARE @tmp TABLE(Clarity VARCHAR(30), RowNo INT)

--get all Clarities 
;WITH Clarities AS
(
    SELECT 1 AS RowNo, Clarity
    FROM YourTable
    UNION ALL
    SELECT RowNo + 1 AS RowNo, Clarity
    FROM Clarities
)
INSERT INTO @tmp (Clarity, RowNo)
SELECT Clarity, RowNo
FROM Clarities
--uncomment below line if no. of Clarities in your table is more than 100
--OPTION (MAXRECURSION 0)

--declare variables to get first and last clarity no.
DECLARE @firstClarity INT
DECLARE @lastClarity INT

--get id for the first clarity
SELECT @firstClarity = MIN(RowNo)
FROM @tmp
WHERE Clarity = 'FL' 
--get id for the last clarity
SELECT @lastClarity = MAX(RowNo)
FROM @tmp
WHERE Clarity = 'VVS1' 

--result query:
SELECT t1.Clarity
FROM YourTable AS t1 LEFT JOIN @tmp AS t2 ON t1.Clarity = t2.Clarity
WHERE t2.RowNo BETWEEN @firstClarity AND @lastClarity

在上面的示例中，我使用 []获取所有明确内容并为他们添加唯一编号。请阅读以上代码中的评论。

In above example, i use CTE[^] to get all clarities and to add unique number for them. Please, read comment in above code.

这篇关于SQL范围查询用于非顺序字母数据值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

fl