如何找到以下栏目？ [英] How do I find the below column ?

问题描述

表1



id玩家重复

1 Sachin 3

2 Ganguly 2

3 Dravid 4



考虑上面的表。现在我需要创建一个具有以下输出的新表：



id玩家重复

1 Sachin 1

1 Sachin 1

1 Sachin 1

2 Ganguly 1

2 Ganguly 1

3 Dravid 1

3 Dravid 1

3 Dravid 1
3 Dravid 1



在表1中，如果repeat是3，那么在第二个表中重复将是值3的3倍。



请帮助解决方案。



谢谢



我尝试了什么：



我尝试过等级，密集等级多维数据集等但没有重复

解决方案

您可以将CROSS APPLY与数字表一起使用。本文向您展示了一种创建数字表的方法在SQL中生成序列 [ ^ ]或更多内容在这里深入了解 SQL Server数字表，解释 - 部分1 [ ^ ]

然后你可以做这样的事情

  create   table  #src（id  int  身份（ 1 ， 1 ），播放器 varchar （ 20 ），[repeat]  int ）
  insert  进入 #src（播放器，[重复]）值 
（ '  Sachin'， 3 ），
（'  Ganguly'， 2 ），
（'  Dravid'， 4 ）
 
 声明  @ m   int  =（ SELECT  Max（[repeat]）来自 #src）
 
;  WITH  q  AS  
（
  SELECT   1   AS  num 
  UNION   ALL  
  SELECT  num +  1  
  FROM  q 
  WHERE  num<  @ m  
）
  SELECT  num  INTO  #nos  FROM  q 
 
  SELECT  A. * 
  FROM  #src A 
  CROSS   APPLY  #nos B 
  WHERE  B.num< = A. [repeat] 
  ORDER   BY  id 

给出以下结果：

 1 Sachin 3 
 1 Sachin 3 
 1 Sachin 3 
 2 Ganguly 2 
 2 Ganguly 2 
 3 Dravid 4 
 3 Dravid 4 
 3 Dravid 4 
 3 Dravid 4 

只是重新阅读我的解决方案 - 您可能希望最后一次选择

  SELECT  A.id，A.player， 1   as  [repeat] 
 
 
除了 CHill60的解决方案1  [ ^ ] ，我更喜欢只使用Common Table Expressions（不使用CROSS APPLY）。例如：
 
 
 
 
< pre> DECLARE  @ tmp   TABLE （id  INT   IDENTITY （ 1 ， 1 ），播放器 VARCHAR （ 30 ），[repeat]  INT ）
 
  INSERT   INTO   @ tmp （播放器，[重复]）
  VALUES （'  Sachin'， 3 ），
（'  Ganguly'，< span class =code-digit> 2 ），
（'  Dravid'， 4 ）
 
;  WITH  CTE  AS  
（
   -  -   初始部分 
  SELECT  id，player， 1  作为 CurrVal，[repeat]， 1   AS  myCounter 
  FROM   @ tmp  
  WHERE  [repeat]> 0 
   -   递归部分 
  UNION   ALL  
   -    myCounter用于定义退出循环的条件 
  SELECT  id，player， 1  作为 CurrVal，[repeat]，myCounter +  1   AS  myCounter 
  FROM  CTE 
  WHERE  myCounter< [repeat] 
）
  SELECT  id，player，CurrVal  AS  [repeat] 
  FROM  CTE 
  ORDER   BY  id，myCounter 
 
 
 
 
 
 
结果：
 
 
 id player repeat 
 1 Sachin 1 
 1 Sachin 1 
 1 Sachin 1 
 2 Ganguly 1 
 2 Ganguly 1 
 3 Dravid 1 
 3 Dravid 1 
 3 Dravid 1 
 3 Dravid 1 
 
 
 
 
欲了解更多详情，请参阅：
 
 使用公用表格式 [ ^ ] 
 
  WITH common_table_expression（Transact-SQL）| Microsoft Docs  [ ^ ] 
 
 使用公用表表达式的递归查询 [ ^ ] 
 
 
Table1



id	player	repeat

1	Sachin	3

2	Ganguly	2

3	Dravid	4



Consider the above table.Now I need to create a new table with the following output:



id	player	repeat

1	Sachin	1

1	Sachin	1

1	Sachin	1

2       Ganguly 1

2       Ganguly 1

3	Dravid	1

3	Dravid	1

3	Dravid	1

3	Dravid	1



Here in Table1, if repeat is 3 ,then in second table repeat will be 3 times with value 1.



Please help with the solution.



Thanks



What I have tried:



I have tried rank ,dense rank cube etc but no reslult
解决方案
You can use CROSS APPLY with a numbers table. This article shows you one way to create a numbers table Generating a Sequence in SQL[^] or more in depth here The SQL Server Numbers Table, Explained - Part 1[^] 

Then you can do something like this

create table #src (id int identity(1,1), player varchar(20), [repeat] int)
insert into #src (player, [repeat]) values
('Sachin', 3),
('Ganguly', 2),
('Dravid', 4)

declare @m int = (SELECT Max([repeat]) from #src)

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    q
    WHERE num < @m
    )
SELECT num INTO #nos FROM q

SELECT A.*
FROM #src A
CROSS APPLY #nos B 
WHERE B.num <= A.[repeat]
ORDER BY id
Giving the following results:
1	Sachin	3
1	Sachin	3
1	Sachin	3
2	Ganguly	2
2	Ganguly	2
3	Dravid	4
3	Dravid	4
3	Dravid	4
3	Dravid	4
[EDIT] just re-read my solution against your post - you probably want that last select to be 
SELECT A.id, A.player, 1 as [repeat]
to get the exact results you mentioned

In addition to solution 1 by CHill60[^], i'd prefer to use Common Table Expressions only (without using CROSS APPLY). For example:



<pre>DECLARE @tmp TABLE(id INT IDENTITY(1,1), player VARCHAR(30), [repeat] INT)

INSERT INTO @tmp(player, [repeat])
VALUES('Sachin', 3),
('Ganguly', 2),
('Dravid', 4)

;WITH CTE AS 
(
    --initial part
    SELECT id, player, 1 As CurrVal, [repeat], 1 AS myCounter
    FROM @tmp
    WHERE [repeat]>0
    --recursive part
    UNION ALL
    --myCounter is used to define the condition to exit from loop
    SELECT id, player, 1 As CurrVal, [repeat], myCounter + 1 AS myCounter
	FROM CTE
	WHERE myCounter<[repeat]
)
SELECT id, player, CurrVal AS [repeat]
FROM CTE
ORDER BY id, myCounter   






Result:

id	player	repeat
1	Sachin	1
1	Sachin	1
1	Sachin	1
2	Ganguly	1
2	Ganguly	1
3	Dravid	1
3	Dravid	1
3	Dravid	1
3	Dravid	1




For further details, please see:

Using Common Table Expressions[^]

WITH common_table_expression (Transact-SQL) | Microsoft Docs[^]

Recursive Queries Using Common Table Expressions[^]


                        
这篇关于如何找到以下栏目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！