如何添加备用行并在不同列中显示它们 [英] How to add alternative rows and display them in different column

问题描述

 StudentID Fname CountofCorr Obj1 obj2 
 1 Butler 5 5 
 2 Katelyn 5 5 
 3 Tran 4 9 
 4 Jonathan 5 10 
 5 Jerewy 5 14 
 6 Hamilton 5 15 
 7 Dananh 2 16 
 8 Sameen 4 19 
 9 Devgan 4 20 

我想添加所有奇数行的计数并显示在obj1中甚至obj2中的行可以任何一个帮助谢谢



[edit]已添加代码块 - OriginalGriff [/ edit]

解决方案

尝试

  SELECT  StudentID，Fname，CountofCorr，
（ SELECT   CASE   WHEN  a.StudentID％ 2  =  1  那么 SUM（b.CountofCorr） ELSE   NULL   END   FROM  YourTable b  WHERE  b.StudentID< = a.StudentID  AND  b.StudentID％ 2  =  1 ） AS  Obj1，
（ SELECT   CASE  < span class =code-keyword> WHEN  a.StudentID％ 2  =  0  < span class =code-keyword> THEN  SUM（b.CountofCorr） ELSE   NULL   END   FROM  YourTable b  WHERE  b.StudentID < = a.StudentID  AND  b.StudentID％ 2  =  0 ） AS  Obj2 
  FROM  YourTable a 

这是我为测试而创建的示例

 CREATE TABLE #Sample 
（
 StudentID INT，
 Fname VARCHAR（100），
 CountofCorr INT 
）
 
 INSERT INTO #Sample 
 SELECT 1，''Butler''，5 UNION ALL 
 SELECT 2，''Katelyn''，5 UNION ALL 
 SELECT 3， ''Tran''，4 UNION ALL 
 SELECT 4，''Jonathan''，5 UNION ALL 
 SELECT 5，''Jerewy''，5 UNION ALL 
 SELECT 6，'' Hamilton''，5 UNION ALL 
 SELECT 7，''Dananh''，2 UNION ALL 
 SELECT 8，''Sameen''，4 UNION ALL 
 SELECT 9，''Devgan' '，4 
 
 
  - 卖家*来自#Sample 
 
 
 SELECT *，
（选择案例时a.StudentID％2 = 1 THEN SUM（b.CountofCorr）ELSE NULL END FROM #Sample b WHERE b.StudentID< = a.StudentID AND b.StudentID％2 = 1）AS Obj1，
（SELECT CASE WHEN.StudentID％2 = 0 THEN SUM（b.CountofCorr）ELSE NULL END FROM #Sample b WHERE b.StudentID< ; = a.StudentID AND b.StudentID％2 = 0）AS Obj2 
 FROM #Sample a 
 
 
 DROP TABLE #Sample 

我做的是我采用了一个自动增加的新列，然后我使用了解决方案，几乎没有修改。

  alter   table  New_Text_Document  add  NewColumn  int   identity （ 1 ，< span class =code-digit> 1 ）
  SELECT  *，
（ SELECT   CASE   WHEN  a.NewColumn％ 2  =  1  那么 SUM（b.correct_answercount） ELSE   NULL   END   FROM  New_Text_Document b  WHERE  b.NewColumn& lt; = a.NewColumn  AND  b.NewColumn％ 2  =  1 ） AS  Obj1，
（ SELECT   CASE   WHEN  a.NewColumn％ 2  =  0  那么 SUM（b.correct_answercount） ELSE   NULL   END   FROM  New_Text_Document b  WHERE  b.NewCo lumn& lt; = a.NewColumn  AND  b.NewColumn％ 2  =  0 ） AS  Obj2 
  FROM  New_Text_Document a< / pre> ; 

StudentID	Fname	CountofCorr	Obj1	obj2
1	        Butler	       5	 5	
2	        Katelyn	       5		 5
3	        Tran	       4	 9	
4	        Jonathan       5		 10
5	        Jerewy	       5	14	
6	       Hamilton	       5		15
7	       Dananh	       2	16	
8	      Sameen	       4		19
9	      Devgan	       4	20

I want to add the count of all the odd rows and display in obj1 and even rows in obj2 can any one help thank you

[edit]Code block added - OriginalGriff[/edit]

解决方案

Try

SELECT StudentID, Fname, CountofCorr, 
(SELECT CASE WHEN a.StudentID % 2 = 1 THEN  SUM(b.CountofCorr)  ELSE NULL END  FROM YourTable b WHERE b.StudentID <= a.StudentID AND b.StudentID % 2 = 1) AS Obj1,
(SELECT CASE WHEN a.StudentID % 2 = 0 THEN  SUM(b.CountofCorr)  ELSE NULL END  FROM YourTable b WHERE b.StudentID <= a.StudentID AND b.StudentID % 2 = 0) AS Obj2
FROM YourTable a

Here is a sample i created for testing

CREATE TABLE #Sample
(
	StudentID INT,
	Fname VARCHAR(100),
	CountofCorr INT
)

INSERT INTO #Sample
SELECT 1, ''Butler'',5  UNION ALL
SELECT 2, ''Katelyn'',5  UNION ALL		
SELECT 3, ''Tran'',4	   UNION ALL
SELECT 4, ''Jonathan'',5	  UNION ALL
SELECT 5, ''Jerewy'',5	  UNION ALL
SELECT 6, ''Hamilton'', 5	  UNION ALL
SELECT 7, ''Dananh'',2	  UNION ALL
SELECT 8, ''Sameen'',4	  UNION ALL
SELECT 9, ''Devgan'',4	  


--SELECT * FROM #Sample


SELECT *, 
(SELECT CASE WHEN a.StudentID % 2 = 1 THEN  SUM(b.CountofCorr)  ELSE NULL END  FROM #Sample b WHERE b.StudentID <= a.StudentID AND b.StudentID % 2 = 1) AS Obj1,
(SELECT CASE WHEN a.StudentID % 2 = 0 THEN  SUM(b.CountofCorr)  ELSE NULL END  FROM #Sample b WHERE b.StudentID <= a.StudentID AND b.StudentID % 2 = 0) AS Obj2
FROM #Sample a


DROP TABLE #Sample

What i did is i took a new column which will auto increment and then i used the solution with little modification.

alter table New_Text_Document add NewColumn int identity(1,1)
SELECT *,
(SELECT CASE WHEN a.NewColumn % 2 = 1 THEN  SUM(b.correct_answercount)  ELSE NULL END  FROM New_Text_Document b WHERE b.NewColumn <= a.NewColumn AND b.NewColumn % 2 = 1) AS Obj1,
(SELECT CASE WHEN a.NewColumn % 2 = 0 THEN  SUM(b.correct_answercount)  ELSE NULL END  FROM New_Text_Document b WHERE b.NewColumn <= a.NewColumn AND b.NewColumn % 2 = 0) AS Obj2
FROM New_Text_Document a</pre>

这篇关于如何添加备用行并在不同列中显示它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！