SQL 组合数据。分不多，求大神~~~

--测试数据
SELECT * INTO #T FROM (
SELECT 'LITTLE KING' FID,254.509 X1, 57.966 Y1
UNION SELECT '*** A123970',263.547,57.966
UNION SELECT 'A012-1H__ PM#123970__ DRI-FIT CP__    ',281.624,57.966
UNION SELECT 'SINGLE KNIT__ TRICOT__ WARP KNIT   ',281.624,57.966
UNION SELECT 'SINGLE KNIT__ TRICOT__ WARP KNIT   ',290.663,57.966
UNION SELECT 'MESH__ 100.0% POLYESTER__ FACE +   ',299.701,57.966
UNION SELECT 'BACK: FILAMENT 100.0% POLYESTER    ',308.739,57.966
UNION SELECT '1/75/72 X1 DENIER, MICROFIBER, SEMI   ',317.778,57.966
UNION SELECT 'DULL, FALSE-TWIST, ROUND__ MM    ',326.816,57.966
UNION SELECT 'INHERENT ON FACE AND BACK__    ',335.855,57.966
UNION SELECT 'DISPERSE DYED PIECE DYED__    ',344.893,57.966
UNION SELECT 'TECHNICAL FACE=FACE__ 28GG__    ',353.931,57.966
UNION SELECT 'W:152.0000 CENTIMETER__ MINIMUM    ',362.97,57.966
UNION SELECT 'CUTTABLE__ WT:78G/M2__ ZONED    ',372.008,57.966
UNION SELECT 'COOLING QUALIFIED__    ',381.047,57.966
UNION SELECT 'FORMOSA TAFFETA DIV 1    ',401.534,57.966
UNION SELECT '*** A028948    ',410.572,57.966
UNION SELECT 'E29 -0114U000__ PM#028948__    ',428.649,57.966
UNION SELECT '"FROG"__ 1/1 TAFFETA__ 100.0%    ',437.687,57.966
UNION SELECT 'POLYESTER__ FILL: FILAMENT 100.0%    ',446.726,57.966
UNION SELECT 'POLYESTER 1/75/36 DENIER, SEMI    ',455.764,57.966
UNION SELECT 'DULL, TEXTURED, FLAT + WARP:    ',464.803,57.966
UNION SELECT 'FILAMENT 100.0% POLYESTER 1/75/36    ',473.841,57.966
UNION SELECT 'DENIER, SEMI DULL, FLAT, ROUND__    ',482.879,57.966
UNION SELECT 'SOFTENER - GENERIC ON BACK, CIRE    ',491.918,57.966
UNION SELECT 'ON BACK (1 PASSES)__ DISPERSE    ',500.956,57.966
UNION SELECT 'DYED PIECE DYED__ TECHNICAL    ',509.995,57.966
UNION SELECT 'FACE=FACE__ 114X96__    ',519.033,57.966
UNION SELECT 'W:150.00000000000 CENTIMETER__    ',528.072,57.966
UNION SELECT 'MINIMUM CUTTABLE__ WT:75G/M2__    ',537.11,57.966
UNION SELECT 'CORE MATERIAL    ',546.148,57.966
UNION SELECT 'MEN CHUEN    ',566.635,57.966
UNION SELECT '*** A321870    ',575.674,57.966
UNION SELECT 'PCR-1408__ PM#058661__ 1X1 DOUBLE    ',593.751,57.966
UNION SELECT 'KNIT__ RIB__ 100.0% POLYESTER__    ',602.789,57.966
UNION SELECT 'FACE + BACK: FILAMENT 100.0%    ',611.828,57.966
UNION SELECT 'POLYESTER 1/150/36 X2 DENIER, SEMI    ',620.866,57.966
UNION SELECT 'DULL, FALSE-TWIST, ROUND__ PIECE    ',629.904,57.966
UNION SELECT 'DYED__ TECHNICAL FACE=FACE__    ',638.943,57.966
UNION SELECT '14GG__ W:137.16 CENTIMETER__    ',647.981,57.966
UNION SELECT 'MINIMUM CUTTABLE__ WT:305G/M2__    ',657.02,57.966
) ZSELECT * FROM #T ORDER BY X1,Y1DROP TABLE #T

前后两条 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
)
SELECT cte.fid,cte.x1,cte.p_x1
,cte.x1-cte.p_x1 FROM cte WHERE LEFT(cte.fid,3)='***' AND p_x1>0 GROUP BY cte.fid,cte.x1,cte.p_x1
上面的结果：fid x1 p_x1 (No column name)
*** A028948     410.572 401.534 9.038
*** A123970 263.547 254.509 9.038
*** A321870     575.674 566.635 9.039

不是的。是要把FID组合在一个变量.
变量1='LITTLE KING
*** A123970
A012-1H__ PM#123970__ DRI-FIT CP__
SINGLE KNIT__ TRICOT__ WARP KNIT
SINGLE KNIT__ TRICOT__ WARP KNIT
MESH__ 100.0% POLYESTER__ FACE +
BACK: FILAMENT 100.0% POLYESTER
1/75/72 X1 DENIER, MICROFIBER, SEMI
'DULL, FALSE-TWIST, ROUND__ MM
INHERENT ON FACE AND BACK__
DISPERSE DYED PIECE DYED__
TECHNICAL FACE=FACE__ 28GG__
W:152.0000 CENTIMETER__ MINIMUM
CUTTABLE__ WT:78G/M2__ ZONED
COOLING QUALIFIED__    '变量2='FORMOSA TAFFETA DIV 1
*** A028948
E29 -0114U000__ PM#028948__
"FROG"__ 1/1 TAFFETA__ 100.0%
POLYESTER__ FILL: FILAMENT 100.0%
POLYESTER 1/75/36 DENIER, SEMI    DULL, TEXTURED, FLAT + WARP:
FILAMENT 100.0% POLYESTER 1/75/36
DENIER, SEMI DULL, FLAT, ROUND__
SOFTENER - GENERIC ON BACK, CIRE
ON BACK (1 PASSES)__ DISPERSE
DYED PIECE DYED__ TECHNICAL
FACE=FACE__ 114X96__
W:150.00000000000 CENTIMETER__
MINIMUM CUTTABLE__ WT:75G/M2__
CORE MATERIAL  '
....

是要把对应的字符累加起来
变量1='LITTLE KING
*** A123970
A012-1H__ PM#123970__ DRI-FIT CP__
SINGLE KNIT__ TRICOT__ WARP KNIT
SINGLE KNIT__ TRICOT__ WARP KNIT
MESH__ 100.0% POLYESTER__ FACE +
BACK: FILAMENT 100.0% POLYESTER
1/75/72 X1 DENIER, MICROFIBER, SEMI
'DULL, FALSE-TWIST, ROUND__ MM
INHERENT ON FACE AND BACK__
DISPERSE DYED PIECE DYED__
TECHNICAL FACE=FACE__ 28GG__
W:152.0000 CENTIMETER__ MINIMUM
CUTTABLE__ WT:78G/M2__ ZONED
COOLING QUALIFIED__    '变量2='FORMOSA TAFFETA DIV 1
*** A028948
E29 -0114U000__ PM#028948__
"FROG"__ 1/1 TAFFETA__ 100.0%
POLYESTER__ FILL: FILAMENT 100.0%
POLYESTER 1/75/36 DENIER, SEMI    DULL, TEXTURED, FLAT + WARP:
FILAMENT 100.0% POLYESTER 1/75/36
DENIER, SEMI DULL, FLAT, ROUND__
SOFTENER - GENERIC ON BACK, CIRE
ON BACK (1 PASSES)__ DISPERSE
DYED PIECE DYED__ TECHNICAL
FACE=FACE__ 114X96__
W:150.00000000000 CENTIMETER__
MINIMUM CUTTABLE__ WT:75G/M2__
CORE MATERIAL  '

[code= with cte as
(SELECT *,ROW_NUMBER()over(order by x1,y1) as n FROM #T ),
cte1 as
(select *, 1 as groupid from cte where n=1
  union all
   select c1.*,case when (select fid from cte as c3 where
    c3.n=c1.n+1   )like '***%'
    then c2.groupid+1 else c2.groupid end as groupid from cte as c1 join cte1 as c2 on
    c1.n=c2.n+1  )
   select GROUPid , STUFF((select ''+fid from cte1 as a
   where a.groupid=b.groupid for XML path('')),1,1,'')
    as 变量 from  cte1 as b
    group by b.groupid
   ][/code]
结果

GROUPid     变量
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           ITTLE KING*** A123970A012-1H__ PM#123970__ DRI-FIT CP__    SINGLE KNIT__ TRICOT__ WARP KNIT   SINGLE KNIT__ TRICOT__ WARP KNIT   MESH__ 100.0% POLYESTER__ FACE +   BACK: FILAMENT 100.0% POLYESTER    1/75/72 X1 DENIER, MICROFIBER, SEMI   DULL, FALSE-TWIST,
2           ORMOSA TAFFETA DIV 1    *** A028948    E29 -0114U000__ PM#028948__    "FROG"__ 1/1 TAFFETA__ 100.0%    POLYESTER__ FILL: FILAMENT 100.0%    POLYESTER 1/75/36 DENIER, SEMI    DULL, TEXTURED, FLAT + WARP:    FILAMENT 100.0% POLYESTER 1/75/36    DENIER, SEMI
3           EN CHUEN    *** A321870    PCR-1408__ PM#058661__ 1X1 DOUBLE    KNIT__ RIB__ 100.0% POLYESTER__    FACE + BACK: FILAMENT 100.0%    POLYESTER 1/150/36 X2 DENIER, SEMI    DULL, FALSE-TWIST, ROUND__ PIECE    DYED__ TECHNICAL FACE=FACE__    14GG__ W:137.16 CEN(3 行受影响)

谢谢alimake大神再问一下。如果我在里面多插入了三行数据，规则变了，按A所在的X1坐标取整-2为变量的开始。要怎么改呢？
UNION SELECT 'A        ',568.443,36.515
UNION SELECT 'A        ',403.341,36.515
UNION SELECT 'A        ',256.317,36.515

with cte as
(SELECT *,ROW_NUMBER()over(order by x1,y1) as n FROM #T ),
t as
(select * from cte where fid='a'),
cte1 as
(select *, 1 as groupid from cte where n=1
  union all
   select c1.*,case when exists  (select 1
    from  t  where FLOOR(t.x1)=FLOOR(c1.x1)+2)
    then c2.groupid+1 else c2.groupid end as groupid from cte as c1 join cte1 as c2 on
    c1.n=c2.n+1  )
       select GROUPid , STUFF((select ''+fid from cte1 as a
   where a.groupid=b.groupid for XML path('')),1,1,'')
    as 变量 from  cte1 as b
    group by b.groupid找了半天错误发现你插入A后面有很多空格导致查不到记录。
你测试时候先用
select * from #T where fid='a' 看看能不能出来后面插入的3行。

[code=sql]select * from #T where fid='A '
就可以，不过我A是不要累加进去的。
是以A的X1坐标取整-2。Y1坐标=57就累计进去的
因为我当中还会有很多别的字符，但不用累计进去，所以要以坐标来取

[code=sql]select * from #T where fid='A '
就可以，不过我A是不要累加进去的。
是以A的X1坐标取整-2。Y1坐标=57就累计进去的
因为我当中还会有很多别的字符，但不用累计进去，所以要以坐标来取
没看懂啥意思你，就是表里面实际是没有这3条A的数据是吧。这对于第一组来说就是X1取整-2不就是566吗，和Y1有啥关系？

[code=sql]select * from #T where fid='A '
就可以，不过我A是不要累加进去的。
是以A的X1坐标取整-2。Y1坐标=57就累计进去的
因为我当中还会有很多别的字符，但不用累计进去，所以要以坐标来取
没看懂啥意思你，就是表里面实际是没有这3条A的数据是吧。这对于第一组来说就是X1取整-2不就是566吗，和Y1有啥关系？就是我数据中不止这些数据，A的数据也是有的，还有一些别的，但不需要累加起来。
只有Y1坐标=57的所有数据才需要累加。
比如取到第一条A的X1坐标是256-2=254那么第一个就从X1坐标254开始到401的上一条结束，并且Y1=57，
第二条A的X1坐标是403-2=401就是第二个就从X1坐标401开始到566的上一条结束，并且Y1=57。
以此类推。

[code=sql]select * from #T where fid='A '
就可以，不过我A是不要累加进去的。
是以A的X1坐标取整-2。Y1坐标=57就累计进去的
因为我当中还会有很多别的字符，但不用累计进去，所以要以坐标来取
没看懂啥意思你，就是表里面实际是没有这3条A的数据是吧。这对于第一组来说就是X1取整-2不就是566吗，和Y1有啥关系？就是我数据中不止这些数据，A的数据也是有的，还有一些别的，但不需要累加起来。
只有Y1坐标=57的所有数据才需要累加。
比如取到第一条A的X1坐标是256-2=254那么第一个就从X1坐标254开始到401的上一条结束，并且Y1=57，
第二条A的X1坐标是403-2=401就是第二个就从X1坐标401开始到566的上一条结束，并且Y1=57。
以此类推。[code=sql]select * from #T where fid='A '
就可以，不过我A是不要累加进去的。
是以A的X1坐标取整-2。Y1坐标=57就累计进去的
因为我当中还会有很多别的字符，但不用累计进去，所以要以坐标来取
没看懂啥意思你，就是表里面实际是没有这3条A的数据是吧。这对于第一组来说就是X1取整-2不就是566吗，和Y1有啥关系？我重新写好了。非常感谢你

调试易

SQL 组合数据。分不多，求大神~~~

解决方案 »