如何在不使用游标和循环的情况下将记录存储在表组中。< [英] How can I stored record in the group of table without using cursor and loops.<
本文介绍了如何在不使用游标和循环的情况下将记录存储在表组中。<的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
WITH CTE AS
(
SELECT FETCHNEXT。*
,RN = ROW_NUMBER()OVER(ID ID ORDER BY ID分段)
来自FETCHNEXT
)
SELECT *
来自CTE
WHERE RN = 1
UNION ALL
SELECT *
来自CTE
WHERE RN = 2
UNION ALL
SELECT *
来自CTE
WHERE RN = 3
我尝试过:
WITH CTE AS
(
SELECT FETCHNEXT。*
,RN = ROW_NUMBER()OVER(ID ID ORDER BY ID)
FROM FETCHNEXT
)
SELECT *
FROM CTE
WHERE RN = 1
UNION ALL
SELECT *
来自CTE
WHERE RN = 2
UNION ALL
SELECT *
FROM CTE
WHERE RN = 3
解决方案
我用过这个数据CREATE TABLE #ITEMSOFSTORES
(
ITEM INT ,
STORE INT
)
INSERT INTO #ITEMSOFSTORES(ITEM,STORE) VALUES ( 1 , 1 ),( 1 , 3 ),( 4 , 1 ),( 4 , 3 ),( 5 , 1 ),( 5 , 3 ),( 2 , 1 ),( 2 , 2 ),( 2 , 4 ),(< span class =code-digit> 3 , 1 ),( 3 ,< span class =code-digit> 2 ),( 3 , 4 );我创建了另一个临时表
CREATE TABLE #groups(ITEM int ,STORE int ,存储 varchar ( 125 ))然后我用这个查询填充:
INSERT INTO #groups(item,store,stores)
SELECT ITEM,STORE,STUFF
(
(
SELECT ' ,' + CAST(STORE AS VARCHAR )
FROM #ITEMSOFSTORES B
WHERE A.ITEM = B.ITEM
ORDER BY STORE
FOR XML PATH(' ')
), 1 , 1 ,' '
) AS 存储
FROM #ITEMSOFSTORES A这基本上列出了所有项目,其中包含可以找到它的商店的逗号分隔列表。查询
选择 DISTINCT ITEM,STORES 来自 #groups给出以下结果:
ITEM STORES
1 1,3
2 1,2,4
3 1,2,4
4 1,3
5 1,3和
选择 ROW_NUMBER() OVER ( ORDER BY STORES),STORES 来自 #groups GROUP BY STORES将为每个分组提供一个数字,以便您可以将它们组合起来以获得结果
选择 DISTINCT 项目,N 来自 #groups A
INNER JOIN
( s选举 ROW_NUMBER() OVER ( ORDER BY STORES) AS N,STORES 来自 #groups GROUP BY STORES)B ON A.STORES = B.STORES
WITH CTE AS ( SELECT FETCHNEXT.* , RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) FROM FETCHNEXT ) SELECT * FROM CTE WHERE RN = 1 UNION ALL SELECT * FROM CTE WHERE RN = 2 UNION ALL SELECT * FROM CTE WHERE RN = 3
What I have tried:
WITH CTE AS ( SELECT FETCHNEXT.* , RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) FROM FETCHNEXT ) SELECT * FROM CTE WHERE RN = 1 UNION ALL SELECT * FROM CTE WHERE RN = 2 UNION ALL SELECT * FROM CTE WHERE RN = 3
解决方案
I used this dataCREATE TABLE #ITEMSOFSTORES ( ITEM INT, STORE INT ) INSERT INTO #ITEMSOFSTORES (ITEM,STORE) VALUES (1,1),(1,3),(4,1),(4,3),(5,1),(5,3),(2,1),(2,2),(2,4),(3,1),(3,2),(3,4);And I created another temporary table
CREATE TABLE #groups (ITEM int, STORE int, stores varchar(125))Which I then populated with this query:
INSERT INTO #groups (item, store, stores) SELECT ITEM, STORE, STUFF ( ( SELECT ',' + CAST(STORE AS VARCHAR) FROM #ITEMSOFSTORES B WHERE A.ITEM = B.ITEM ORDER BY STORE FOR XML PATH('') ), 1, 1, '' ) AS stores FROM #ITEMSOFSTORES AThis essentially lists all of the items with a comma separated list of the stores in which it can be found. The query
select DISTINCT ITEM, STORES from #groupsgives these results:
ITEM STORES 1 1,3 2 1,2,4 3 1,2,4 4 1,3 5 1,3and
select ROW_NUMBER() OVER (ORDER BY STORES), STORES from #groups GROUP BY STORESwill give each of those "groupings" a number so you can combine them to get your results
select DISTINCT ITEM, N from #groups A INNER JOIN (select ROW_NUMBER() OVER (ORDER BY STORES) AS N, STORES from #groups GROUP BY STORES) B ON A.STORES = B.STORES
这篇关于如何在不使用游标和循环的情况下将记录存储在表组中。<的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文