我有一个表，数据如上图。
在SQL中要怎么把它组合在分别的变量中，画红线的就是一段。
规律就是Y1是一样的，三个*** 开始的上一行数据就是一个新变量的开始
263.547-254.509=9.038
410.572-401.534=9.038
566.635-575.674=9.039
------解决方案--------------------
前后两条 X1 相减吗？
;WITH T AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY X1,Y1) n
FROM #T
)
SELECT t1.FID,
t1.x1-t2.x1 v
FROM T t1
JOIN T t2
ON t2.n = t1.n-1
WHERE t1.FID LIKE '***%'
FID v
-------------------------------------- ---------------------------------------
*** A123970 9.038
*** A028948 9.038
*** A321870 9.039
------解决方案--------------------
你想要的效果是，星号开头的字段的x1值减去这一行的上一行的X1值吧？
这里涉及顺序，所以排序不能错。
试试下面的：
SELECT * INTO ttttt FROM (
SELECT 1 AS ID, 'LITTLE KING' FID,254.509 X1, 57.966 Y1
UNION SELECT 2,'*** A123970',263.547,57.966
UNION SELECT 3,'A012-1H__ PM#123970__ DRI-FIT CP__ ',281.624,57.966
UNION SELECT 4,'SINGLE KNIT__ TRICOT__ WARP KNIT ',281.624,57.966
UNION SELECT 5,'SINGLE KNIT__ TRICOT__ WARP KNIT ',290.663,57.966
UNION SELECT 6,'MESH__ 100.0% POLYESTER__ FACE + ',299.701,57.966
UNION SELECT 7,'BACK: FILAMENT 100.0% POLYESTER ',308.739,57.966
UNION SELECT 8,'1/75/72 X1 DENIER, MICROFIBER, SEMI ',317.778,57.966
UNION SELECT 9,'DULL, FALSE-TWIST, ROUND__ MM ',326.816,57.966
UNION SELECT 10,'INHERENT ON FACE AND BACK__ ',335.855,57.966
UNION SELECT 11,'DISPERSE DYED PIECE DYED__ ',344.893,57.966
UNION SELECT 12,'TECHNICAL FACE=FACE__ 28GG__ ',353.931,57.966
UNION SELECT 13,'W:152.0000 CENTIMETER__ MINIMUM ',362.97,57.966
UNION SELECT 14,'CUTTABLE__ WT:78G/M2__ ZONED ',372.008,57.966
UNION SELECT 15,'COOLING QUALIFIED__ ',381.047,57.966
UNION SELECT 16,'FORMOSA TAFFETA DIV 1 ',401.534,57.966
UNION SELECT 17,'*** A028948 ',410.572,57.966
UNION SELECT 18,'E29 -0114U000__ PM#028948__ ',428.649,57.966
UNION SELECT 19,'"FROG"__ 1/1 TAFFETA__ 100.0% ',437.687,57.966
UNION SELECT 20,'POLYESTER__ FILL: FILAMENT 100.0% ',446.726,57.966
UNION SELECT 21,'POLYESTER 1/75/36 DENIER, SEMI ',455.764,57.966
UNION SELECT 22,'MEN CHUEN ',566.635,57.966
UNION SELECT 23,'*** A321870 ',575.674,57.966
UNION SELECT 24,'PCR-1408__ PM#058661__ 1X1 DOUBLE ',593.751,57.966
) Z
WITH cte AS (
SELECT t1.id,t1.fid,t1.x1,t1.y1,cast(0 AS NUMERIC(6,3)) as p_x1 FROM ttttt AS t1
UNION ALL
SELECT t2.id,t2.fid,t2.x1,t2.y1,cte.x1 AS p_x1 FROM ttttt AS t2 INNER JOIN cte ON cte.id+1=t2.id