There is broken, I want to write SQL statements by grouping the found out in the middle of the broken, the following results, everyone a great god have what good method, please guide, thank you:
CodePudding user response:
The CREATE TABLE # A
(
Fonds number VARCHAR (10),
Directory VARCHAR (10),
The books, INT
)
# INSERT INTO A VALUES (' A001 ', '001', 1)
# INSERT INTO A VALUES (' A001 ', '001', 2)
# INSERT INTO A VALUES (' A001 ', '001', 6)
# INSERT INTO A VALUES (' A001 ', '001', 7)
# INSERT INTO A VALUES (' A001 ', '001', 8)
# INSERT INTO A VALUES (' A001 ', '001', 10)
# INSERT INTO A VALUES (' A001 ', '001', 12)
# INSERT INTO A VALUES (' A001 ', '002', 1)
# INSERT INTO A VALUES (' A001 ', '002', 2)
# INSERT INTO A VALUES (' A001 ', '002', 4)
# INSERT INTO A VALUES (' A001 ', '002', 5)
;
WITH ct
AS
(
SELECT fonds number, catalog number, MIN (the books) AS mi, MAX (the books) AS ma FROM
(
SELECT *, and the books - ROW_NUMBER () OVER (PARTITION BY fonds number, catalog number the ORDER BY the books) AS independence idx FROM # A
) S
GROUP BY fonds number, catalog number, independence idx
)
The SELECT fonds number, catalog number, STUFF (
(
SELECT the ', '+ CASE WHEN ct. Ma=mi THEN CONVERT (VARCHAR (10), ct. Mi) ELSE CONVERT (VARCHAR (10), ct. Mi) +' - '+ CONVERT (VARCHAR (10), ct. Ma) END
The FROM ct
WHERE fonds number=a. fonds number AND number=a. directory,
The ORDER BY mi
FOR XML PATH (')), 1, 1, '
) AS lack of interval number
The FROM ct a
GROUP BY fonds number, catalog number
DROP TABLE # A
CodePudding user response:
The CREATE TABLE # A (
ID INT,
NUM INT
)
# INSERT INTO A VALUES (1, 1), (1, 2), (1, 3), (1, 6), (2, 1), (2, 5), (2, 10), (2, 11)
The SELECT XX. ID,
STUFF ((SELECT the ', '+ XXX. Lack of number
The FROM (
The SELECT XX1. ID, CAST (XX2. NUM + 1 AS VARCHAR (10)) + '-' + CAST (XX1. NUM - 1 AS VARCHAR (10)) number of
The FROM
(SELECT *, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY NUM) ROWID FROM # aa) XX1,
(SELECT *, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY NUM) ROWID FROM # aa) XX2
WHERE XX1. ID=XX2. ID AND XX1. The ROWID=XX2. + 1 AND the ROWID (XX1. NUM - XX2. NUM)!=1 AND XX1. ID=XX. ID) XXX
FOR XML PATH (')), 1, 1, ' ') lack of interval number
The FROM (
The SELECT XX1. ID, CAST (XX2. NUM + 1 AS VARCHAR (10)) + '-' + CAST (XX1. NUM - 1 AS VARCHAR (10)) number of
The FROM
(SELECT *, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY NUM) ROWID FROM # aa) XX1,
(SELECT *, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY NUM) ROWID FROM # aa) XX2
WHERE XX1. ID=XX2. ID AND XX1. The ROWID=XX2. + 1 AND the ROWID (XX1. NUM - XX2. NUM)! XX
=1)GROUP BY XX. ID
DROP TABLE # A
Bosses such as optimizing the
CodePudding user response:
The CREATE TABLE # T
(
Fonds number VARCHAR (10),
Directory VARCHAR (10),
The books, INT
)
INSERT INTO # T VALUES (' A001 ', '001', 3)
INSERT INTO # T VALUES (' A001 ', '001', 5)
INSERT INTO # T VALUES (' A001 ', '001', 6)
INSERT INTO # T VALUES (' A001 ', '001', 7)
INSERT INTO # T VALUES (' A001 ', '001', 10)
INSERT INTO # T VALUES (' A001 ', '001', 12)
INSERT INTO # T VALUES (' A001 ', '001', 20)
INSERT INTO # T VALUES (' A001 ', '001', 21)
INSERT INTO # T VALUES (' A001 ', '001', 8)
INSERT INTO # T VALUES (' A001 ', '001', 30)
INSERT INTO # T VALUES (' A001 ', '002', 1)
INSERT INTO # T VALUES (' A001 ', '002', 2)
INSERT INTO # T VALUES (' A001 ', '002', 4)
INSERT INTO # T VALUES (' A001 ', '002', 5)
INSERT INTO # T VALUES (' A001 ', '002', 10)
INSERT INTO # T VALUES (' A001 ', '002', 11)
INSERT INTO # T VALUES (' A001 ', '002', 16)
WITH CTE_1
AS
(SELECT a. fonds number, catalog number, B.n umber FROM
(SELECT fonds number, catalog number, MAX (the books) AS END_NO
The FROM # T
GROUP BY fonds number, catalog number) AS A
JOIN the MASTER. The DBO. SPT_VALUES B ON B.n umber<=A.E ND_NO
WHERE TYPE='P' AND number> 0),
CTE_2
AS
(SELECT fonds number, catalog number,
CASE the WHEN MIN (NUMBER)=MAX (NUMBER) THEN CAST (MIN (NUMBER) AS VARCHAR) ELSE CAST (MIN (NUMBER) AS VARCHAR) + '-' + CAST (MAX (NUMBER) AS VARCHAR) END AS NO_GAP,
MIN (NUMBER) AS SORT
The FROM
(SELECT A. *, ROW_NUMBER () OVER (PARTITION BY a. fonds NUMBER, a. directory NUMBER ORDER BY NUMBER) AS an RN
The FROM CTE_1 A
LEFT the JOIN # T B ON a. directory number=b. directory number AND a. fonds number=b. fonds number AND A.n umber=b. the books,
WHERE b. fonds number IS NULL) AS C
GROUP BY fonds NUMBER, catalog NUMBER, RN NUMBER)
The SELECT fonds number, catalog number, STUFF ((SELECT ', '+ NO_GAP FROM CTE_2 WHERE a. directory number=directory number AND a. fonds number=fonds number ORDER BY SORT FOR XML PATH ('')), 1, 1, ' ') AS NO_GAP
The FROM CTE_2 AS A
GROUP BY fonds number, catalog number