逗号分隔65000条记录的逻辑。 [英] Comma Seperated logic over a 65000 Records.

问题描述

嘿伙计们，



请帮我构建一个逻辑，根据3列和65000记录集提供逗号分隔的结果集。

我尝试过使用STUFF，COALESCE，SUBSTRING，当查询完成执行时，我可以泡一杯茶.. :(：P。



注意：

SQL Server：MSSQL 2005.

类型国家年文件名FileID 
新加坡2010 A. xls 20 
 A新加坡2010 B.xls 21 
 A新加坡2010 C.Xls 22 
 A马来西亚2008 A.xls 55 
 A马来西亚2008 B.xls 53 
 B Malaysia 2008 Cat.xls 88 
 B Malaysia 2008 doll.xls 78 
 
 
预期产出
类型国家年份文件名FileID 
新加坡2010 a。 xls，b.xls，c.xls 20,21,22 
 A Malaysia 2008 a.xls，b.xls 55,53 
 B Malaysia 2008 cat.xls，doll.xls 88,78 

解决方案

任何ef堡垒？



无论如何，这应该有效：

 SELECT 
 [type]，
 Country，
 [Year]，
 fileName = 
 STUFF（（SELECT'，'+ fileName 
 FROM Records FileNameRecords 
 WHERE 
 FileNameRecords。[type] = Records。[type] 
 AND FileNameRecords.Country = Records.Country 
 AND FileNameRecords。[Year] = Records。[Year] 
 FOR XML PATH （''）），1,2，''），
 FileId = 
 STUFF（（SELECT'，'+ FileId 
 FROM FROM FileNameRecords 
 WHERE 
 FileNameRecords 。[type] =记录。[type] 
 AND FileNameRecords.Country = Records.Country 
 AND FileNameRecords。[Year] = Records。[Year] 
 FOR XML PATH（''）） ，1,2，''）
 FROM 
记录
 GROUP BY 
 [类型]，国家，[年] 
订购
类型，年份DESC 

如果您想使用CTE，请参阅：

< pre lang =sql> DECLARE @ tmp TABLE （atype VARCHAR （ 5 ），国家 VARCHAR （ 30 ），aYar INT ，afilename VARCHAR （ 30 ），FileID INT ）

INSERT INTO @ tmp （atype，Country，aYear ，afilename，FileID）
VALUES （' '，' 新加坡'， 2010 ，' A.xls'， 20 ），
（' A'，' Singapore'， 2010 ，' B.xls'， 21 ），
（< span class =code-string>' A'，' 新加坡'， 2010 ，' C.Xls'， 22 ），
（' A'，' Malaysia'，< span class =code-digit> 2008 ，' A.xls'，< span class =code-digit> 55 ），
（' A'，' 马来西亚'， 2008 ，' B.xls'， 53 ），
（' B'，' 马来西亚'， 2008 ，' Cat.xls'， 88 ），
（' B'， 马来西亚'， 2008 ，' doll。 xls'， 78 ）

; WITH CTE AS
（
SELECT at at ype，Country，aYear，afilename， CONVERT （ VARCHAR （MAX），afilename） AS fnames，FileID， CONVERT （ VARCHAR （MAX），FileID ） AS fIds， 1 AS LoopNo
FROM @ tmp
UNION ALL
SELECT C.atype，C.Country，C.aYear，C .afilename，C.fnames + ' ，' + T.afilename AS fnames，C.FileID，C.fids + ' ，' + T .FileId AS fIds，LoopNo + 1 AS LoopNo
FROM CTE AS C INNER JOIN （
SELECT atype，Country，aYear， CONVERT （ VARCHAR （MAX），afilename） AS afilename， CONVERT （ VARCHAR （MAX），FileID） AS FileID
FROM @ tmp
） AS T ON C.aType = T.aType AND C.Country = T.Country AND C.aYear = T.aYear AND C.FileID< T.FileID
WHERE CHARINDEX（ CONVERT （ NVARCHAR （ 10 ），T.FileID），C.fIds）= 0
）
SELECT C.atype，C.Country，C.aYear，fnames，fIds
FROM CTE AS C
ORDER BY C.atype，C.Country ，C.aYear

以上查询产生：

 A Malaysia 2008 A.xls 55 
 A Malaysia 2008 B.xls 53 
 A Malaysia 2008 B.xls，A.xls 53,55 
 A Singapore 2010 B.xls，C.Xls 21,22 
 A Singapore 2010 A. xls，B.xls 20,21 
 A新加坡2010 A.xls，C.Xls 20,22 
新加坡2010 A.xls，C.Xls，B.xls 20,22,21 
 A新加坡2010 A.xls，B.xls，C.Xls 20,21,22 
 A新加坡2010 A.xls 20 
 A新加坡2010 B.xls 21 
 A新加坡2010 C.Xls 22 
 B Mala ysia 2008 Cat.xls 88 
 B Malaysia 2008 doll.xls 78 
 B Malaysia 2008 doll.xls，Cat.xls 78,88 

无法保证CTE比内置SQL函数更快。根据需要更改代码。



注意：上面的示例代码显示了CTE的工作原理。



有关CTE的更多信息，请参阅：

使用common_table_expression（Transact-SQL） [ ^ ]

CTE（公用表格表达式） [ ^ ]

CTE in SQL Server [ ^ ]

Maciejs解决方案提醒我，几年前我解决了这个问题。 链接 [ ^ ]

根据您的需求调整它看起来类似于this：

  CREATE   TABLE 记录
（[类型]  varchar （ 1 ），[Country] < span class =code-keyword> varchar （ 9 ），[年]  int  ，[filename]  varchar （ 8 ），[FileID]  int ）
; 
 
  CREATE   INDEX  records_ix 
  ON 记录
（[类型]，[国家]，[年份]，[文件名]）
 INCLUDE（ [写到FileID]）; 
 
  INSERT   INTO 记录
（[ type ]，[Country]，[Year]，[filename]，[FileID]）
  VALUES  
（'  A'，' < span class =code-string> Singapore'， 2010 ，'  A.xls'， 20 ），
（'  A'，'  Singapore'， 2010 ，'  B.xls'， 21 ），
（'  A '，' 新加坡'， 2010 ，'  C.Xls'， 22 ），
（'  '，' 马来西亚'， 2008 ，'  A.xls'， 55 ），
（'  A'，'  Malaysia'， 2008 ，'  B.xls'， 53 ），
（'  B'，' 马来西亚'， 2008 ，'  Cat.xls'， 88 ），
（ '  B'，' 马来西亚'， 2008 ，'  doll.xls'， 78 ）
; 
 
  WITH 排名 AS （
  SELECT  [ type ]，[Country]，[Year]，[filename]，[FileID]，Rank（） over （ PARTITION   BY  [ type ]，[Country]，[Year]  ORDER   BY  [filename] ）Rnk 
  FROM 记录
）
，cte（[ type  ]，[Country]，[Year]，FileNames，FileIDs，rnk） as  
（
  SELECT  [ type ] 
，[Country] 
，[Year] 
，cast（[FileName]  as   varchar （max）） as 文件名
，强制转换（[FileID]  as   varchar （max）） as  FileID 
，rnk 
  FROM 排名r 
  WHERE  rnk =  1  
  UNION   ALL  
  SELECT  cte。[ type ] 
，cte。[Country] 
，cte。[Year] 
，FileNames + ' < span class =code-string>，' + [FileName] 
，FileIDs + ' ，' + cast（[FileID]  as   varchar （max））
，r.rnk 
  FROM  cte 
  INNER   JOIN 排名r 
  ON  cte。[ type ] = r。[ type ] 
  AND  cte。[Country] = r。[Country] 
  AND  cte。[Year] = r 。[年] 
  AND  cte.Rnk = r.rnk  -   1  
 ）
  SELECT  [ type ]，[Country]，[Year]，Max（FileNames） FileNames，Max（FileIDs）FileIDs，Max（rnk）
  FROM  cte 
  GROUP   BY  [ type ]，[Country]，[Year] 
  ORDER   BY  [ type ]，[年]  desc  

请注意记录表上的覆盖复合索引。

仍然无法保证它更快，但适当的索引很重要。

Hey guys,

Please help me in building a logic that gives a comma seperated result set based on 3 columns and over a 65000 record set.
I've tried using STUFF, COALESCE, SUBSTRING, by the time the query completes execution, I can make a cup of tea.. :( :P.

Note:
SQL Server : MSSQL 2005.

type	Country	        Year	filename	FileID
A	Singapore	2010	A.xls	        20
A	Singapore	2010	B.xls	        21
A	Singapore	2010	C.Xls	        22
A	Malaysia	2008	A.xls	        55
A	Malaysia	2008	B.xls	        53
B	Malaysia	2008	Cat.xls	        88
B	Malaysia	2008	doll.xls	78
				
				
Expected output				
type	Country	        Year	filename	        FileID
A	Singapore	2010	a.xls,b.xls,c.xls	20,21,22
A	Malaysia	2008	a.xls,b.xls	        55,53
B	Malaysia	2008	cat.xls,doll.xls	88,78

解决方案

Any effort?

Anyway, this should work:

SELECT 
	[type], 
	Country, 
	[Year], 
	fileName = 
		STUFF((SELECT ', ' + fileName
           FROM Records FileNameRecords
           WHERE 
				FileNameRecords.[type] = Records.[type]
				AND FileNameRecords.Country = Records.Country
				AND FileNameRecords.[Year] = Records.[Year]
          FOR XML PATH('')), 1, 2, ''),
    FileId = 
		STUFF((SELECT ', ' + FileId
           FROM Records FileNameRecords
           WHERE 
				FileNameRecords.[type] = Records.[type]
				AND FileNameRecords.Country = Records.Country
				AND FileNameRecords.[Year] = Records.[Year]
          FOR XML PATH('')), 1, 2, '')
FROM	
	Records
GROUP BY
	[type], Country, [Year]
ORDER BY
	type, Year DESC

If you want to use CTE, please see:

DECLARE @tmp TABLE (atype VARCHAR(5), Country VARCHAR(30), aYear INT, afilename VARCHAR(30), FileID INT)

INSERT INTO @tmp (atype, Country, aYear, afilename, FileID)
VALUES('A', 'Singapore', 2010, 'A.xls', 20),
('A', 'Singapore', 2010, 'B.xls', 21),
('A', 'Singapore', 2010, 'C.Xls', 22),
('A', 'Malaysia', 2008, 'A.xls', 55),
('A', 'Malaysia', 2008, 'B.xls', 53),
('B', 'Malaysia', 2008, 'Cat.xls', 88),
('B', 'Malaysia', 2008, 'doll.xls', 78)

;WITH CTE AS
(
    SELECT atype, Country, aYear, afilename, CONVERT(VARCHAR(MAX),afilename) AS fnames, FileID, CONVERT(VARCHAR(MAX),FileID) AS fIds, 1 AS LoopNo
    FROM @tmp
    UNION ALL
    SELECT C.atype, C.Country, C.aYear, C.afilename, C.fnames + ',' + T.afilename AS fnames, C.FileID, C.fids + ',' + T.FileId AS fIds, LoopNo + 1 AS LoopNo
    FROM CTE AS C INNER JOIN (
        SELECT atype, Country, aYear, CONVERT(VARCHAR(MAX),afilename) AS afilename, CONVERT(VARCHAR(MAX),FileID) AS FileID
        FROM @tmp
        ) AS T ON C.aType = T.aType AND C.Country = T.Country AND C.aYear = T.aYear AND C.FileID < T.FileID
    WHERE CHARINDEX(CONVERT(NVARCHAR(10),T.FileID), C.fIds)=0
)
SELECT C.atype, C.Country, C.aYear, fnames, fIds
FROM CTE AS C
ORDER BY C.atype, C.Country, C.aYear

Above query produces:

A	Malaysia	2008	A.xls			55
A	Malaysia	2008	B.xls			53
A	Malaysia	2008	B.xls,A.xls		53,55
A	Singapore	2010	B.xls,C.Xls		21,22
A	Singapore	2010	A.xls,B.xls		20,21
A	Singapore	2010	A.xls,C.Xls		20,22
A	Singapore	2010	A.xls,C.Xls,B.xls	20,22,21
A	Singapore	2010	A.xls,B.xls,C.Xls	20,21,22
A	Singapore	2010	A.xls			20
A	Singapore	2010	B.xls			21
A	Singapore	2010	C.Xls			22
B	Malaysia	2008	Cat.xls			88
B	Malaysia	2008	doll.xls		78
B	Malaysia	2008	doll.xls,Cat.xls	78,88

There is no guarantee that CTE will be faster than in-build SQL functions. Change the code to your needs.

Note: Above sample code shows how CTE work.

For further information about CTE, please see:
WITH common_table_expression (Transact-SQL)[^]
CTEs (Common Table Expressions)[^]
CTE In SQL Server[^]

Maciejs solution reminded me that I solved that problem a couple of years ago. Link[^]
Adjusted for your needs it would look similar to this:

CREATE TABLE Records
	([type] varchar(1), [Country] varchar(9), [Year] int, [filename] varchar(8), [FileID] int)
;

CREATE INDEX records_ix
     ON Records
       ([type], [Country], [Year], [filename])
     INCLUDE ([FileID]);
	
INSERT INTO Records
	([type], [Country], [Year], [filename], [FileID])
VALUES
	('A', 'Singapore', 2010, 'A.xls', 20),
	('A', 'Singapore', 2010, 'B.xls', 21),
	('A', 'Singapore', 2010, 'C.Xls', 22),
	('A', 'Malaysia', 2008, 'A.xls', 55),
	('A', 'Malaysia', 2008, 'B.xls', 53),
	('B', 'Malaysia', 2008, 'Cat.xls', 88),
	('B', 'Malaysia', 2008, 'doll.xls', 78)
;

WITH ranked AS (
    SELECT  [type], [Country], [Year], [filename], [FileID], Rank() over (PARTITION BY [type], [Country], [Year] ORDER BY [filename]) Rnk 
    FROM    Records
    )
,cte ([type], [Country], [Year], FileNames, FileIDs, rnk) as
    (
    SELECT  [type]
           ,[Country]
           ,[Year]
           ,cast([FileName] as varchar(max)) as Filenames
           ,cast([FileID] as varchar(max)) as FileIDs
           ,rnk
    FROM    Ranked r
    WHERE   rnk = 1
    UNION ALL
    SELECT  cte.[type]
           ,cte.[Country]
           ,cte.[Year]
           ,FileNames + ', ' + [FileName]
           ,FileIDs + ', ' + cast([FileID] as varchar(max))
           ,r.rnk
    FROM    cte
    INNER JOIN ranked r
        ON  cte.[type] = r.[type]
        AND cte.[Country] = r.[Country]
        AND cte.[Year] = r.[Year]
        AND cte.Rnk = r.rnk - 1
    )
SELECT  [type], [Country], [Year], Max(FileNames) FileNames, Max(FileIDs) FileIDs, Max(rnk)
FROM    cte
GROUP BY [type], [Country], [Year]
ORDER BY [type], [Year] desc

Note the covering composite Index on the record table.
Still no guarantee that it's faster, but a proper index is important.

这篇关于逗号分隔65000条记录的逻辑。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！