用于分组数据的SQL查询

I have a data set some thing like below.

我有一个数据集，如下所示。

Code          Values
521            1
522            1
523            1
524            0
525            0
526            1
527            1

I am expecting a output something like below:

我希望输出如下:

Group     CD        Values
G1      521-523       1
G2      524-525       0
G3      526-527       1

3 个解决方案

#1

Try this solution :

试试这个解决方案:

DECLARE @YourTable TABLE (Code INT, Value INT)

INSERT INTO @YourTable
VALUES
(521,1),
(522,1),
(523,1),
(524,0),
(525,0),
(526,1),
(527,1)

;WITH CTE AS
(
    SELECT  *, ROW_NUMBER() OVER(ORDER BY CODE) - ROW_NUMBER() OVER(PARTITION BY Value Order BY CODE) Grp
    FROM    @YourTable
)
SELECT  ROW_NUMBER() OVER(ORDER BY(SELECT MIN(Code))) Grp
        ,CAST(MIN(Code) AS VARCHAR(30)) +'-'+ CAST(MAX(Code) AS VARCHAR(30)) CD
        ,MAX(Value) Val
FROm    CTE
GROUP   BY Grp

#2

I would be inclined to use the difference of row numbers approach:

我倾向于采用行号差法:

select concat('G', row_number() over (order by min(value)) as grp,
       concat(min(code), '-', max(code)) as codes,
       value
from (select t.*,
             row_number() over (order by code) as seqnum_c,
             row_number() over (partition by value order by code) as seqnum_vc
      from t
     ) t
group by value, (seqnum_c - seqnum_vc);

#3

Try using LEAD() , ROW_NUMBER() , and conditonal aggregation :

尝试使用LEAD()、ROW_NUMBER()和条件聚合:

SELECT 'G' + ROUND((p.rnk + 1) / 2) as group,
       MAX(CASE WHEN p.rnk%2 = 1 THEN p.code END) + '-' +
       MAX(CASE WHEN p.rnk%2 = 0 THEN p.code END) 
FROM (
    SELECT s.*,
           ROW_NUMBER() OVER(ORDER BY s.code) as rnk
    FROM (
        select t.*,
               LEAD(t.values,1) OVER(ORDER BY t.code DESC) as last_val
        FROM YourTable t) s
    WHERE s.values <> s.last_val ) p
GROUP BY 'G' + ROUND((p.rnk + 1) / 2)

#1