如果总值是特定的，那么如何获得行号。在sql server中 [英] how to get row no, if total value is a prticular no. in sql server

问题描述

大家好，

我在sql中的表格看起来像这样

 Sno HallNo Capacity 
 ------------------- 
 1 101 20 
 2 105 17 
 3 106 18 
 4 107 20 
 5 108 15 
 6 109 18 
 $ 110 19 

8 111 20



例如我给total = 50然后我应该得到行no。通过添加容量列值。在这个例子中，我应该得到3（bcoz：20 + 17 + 18 = 55，大于55）

例如我给总数= 60，那么答案应该是4 。

请用select语句帮助我，我在select语句中尝试使用sum函数，但我无法获得行号。



提前致谢



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

解决方案

< blockquote>它可能效率不高，但是......

  DECLARE   @ SUM   INT  
  DECLARE   @ ROWN   INT  
  DECLARE   @ LIMIT   INT  
  SET   @ S UM  =  0  
  SET   @ROWN  =  0  
  SET   @LIMIT  =  50  
  SELECT  @ ROWN = @ ROWN +（< span class =code-keyword> CASE   WHEN   @ SUM >  @ LIMIT  那么  0   ELSE   1   END ），@ SUM = @ SUM + Capacity 
  FROM  MyTable  ORDER   BY  Sno 
  SELECT   @ ROWN  

Hi Everyone,
My table in sql is look like this

Sno HallNo Capacity
-------------------
1    101      20
2    105      17
3    106      18
4    107      20 
5    108      15
6    109      18
7    110      19

8 111 20

For example I give total=50 then I should get the row no. by adding the "capacity" column values. In this example I should get "3"(bcoz: 20+17+18=55, which is greater than 55)
For example I give total=60, then the answer should be "4".
Kindly help me with select statement, I have tried using "sum" function in select statement but I am unable to get the row no..

Thanks in advance

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

解决方案

It's probably not very efficient, but...

DECLARE @SUM INT
DECLARE @ROWN INT
DECLARE @LIMIT INT
SET @SUM = 0
SET @ROWN = 0
SET @LIMIT = 50
SELECT @ROWN=@ROWN + (CASE WHEN @SUM > @LIMIT THEN 0 ELSE 1 END), @SUM=@SUM+Capacity
 FROM MyTable ORDER BY Sno
SELECT @ROWN

这篇关于如果总值是特定的，那么如何获得行号。在sql server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！