Sql选择查询问题。 [英] Sql Select Query Issue.

问题描述

我有一个像这样的查询的SQL查询组。

  SELECT  cusName，cellPerBlock，SUM （块），SUM（单元格） FROM  [tbCus]  GROUP   BY  cusName，cellPerBlock 

但是我需要一个类似下面代码的查询。

  if （cell >  = cellPerBlock）
 {
 block + = cell / cellPerBlock; 
 cell％= cellPerBlock; 
} 
  while （cell <   0 ）
 {
 cell + = cellPerBlock; 
 block--; 
} 

感谢提前。

解决方案

嗯也许像...

  SELECT  
 
 cusName，
 cellPerBlock，
 < span class =code-keyword> case  当 cell> = cellPerBlock 然后块+（cell / cellPerBlock）当 cell<  0  然后块 - （（floor（（cell * -1）/ cellPerBlock））* 1）< span class =code-keyword> else  block  end  '  block'，
  case    cell> = cellPerBlock   floor（cell / cellPerBlock）  cell<  0    cell +（cellPerBlock *（（floor（（cell * -1）/ cellPerBlock））* 1）） else  cell  end  ' < span class =code-string> cell' 
 
  FROM  [tbCus] 

或者如果你需要group by子句，请使用嵌套查询

  FROM  
（
  SELECT  cusName，cellPerBlock，SUM（块）'  block'，SUM（单元格）'  cell' < span class =code-keyword> FROM  [tbCus]  GROUP   BY  cusName，cellPerBlock 
）

既然你说了一个查询，我把它作为一个陈述。



我取代了w用我认为数学等价物的循环。 （我不得不对你的数据做一些假设）



因此对于while循环

  while （cell <   0 ）
 {
 cell + = cellPerBlock; 
 block--; 
} 

cell = -10

cellPerBlock = 2

block = 10



结果：cell = 0，block = 5



在查询中，

< pre lang =sql> 当 cell< 0 cell +（（cellPerBlock *（（floor（（cell * -1）/ cellPerBlock）） * 1）） else cell end ' cell'

cell = -10 +（2 *（（ - 10 * -1）％2） * 1）= 0

 当单元格<  0时 然后块 - （（floor（（cell * -1）/ cellPerBlock））* 1） else  block  end  '  block' 

block = 10 - （（（ - 10 * -1）％2）* 1）= 5



最后的* 1是处理事件，当你的while循环运行一次，但我的公式将提供0的商。我认为我的括号和数学是正确的，没有测试它。

I have a sql query group by query like this.

SELECT cusName, cellPerBlock, SUM(block), SUM(cell) FROM [tbCus] GROUP BY cusName, cellPerBlock

But I need a query that will perform like the following code.

if(cell >= cellPerBlock)
{
      block += cell / cellPerBlock;
      cell %= cellPerBlock;
}
while (cell < 0)
{
     cell += cellPerBlock;
     block--;
} 

Thank's In advance.

解决方案

Umm maybe something like...

SELECT

    cusName,
    cellPerBlock,
    case when cell >= cellPerBlock then block + (cell / cellPerBlock) when cell < 0 then block - ((floor((cell * -1) / cellPerBlock))*1) else block end 'block',
    case when cell >= cellPerBlock then floor(cell / cellPerBlock) when cell < 0 then cell + (cellPerBlock * ((floor((cell * -1) / cellPerBlock))*1)) else cell end 'cell'

FROM [tbCus]

or if you need the group by clause, use a nested query

FROM
(
    SELECT cusName, cellPerBlock, SUM(block) 'block', SUM(cell) 'cell' FROM [tbCus] GROUP BY cusName, cellPerBlock
)

Since you said a query, I kept it as one statement.

I replaced the while loop with what I think a mathematical equivalent is. (I had to make some assumptions about your data)

So for the while loop

while (cell < 0)
{
     cell += cellPerBlock;
     block--;
}

cell = -10
cellPerBlock = 2
block = 10

Result: cell = 0, block = 5

In the query,

when cell < 0 then cell + ((cellPerBlock * ((floor((cell * -1)/ cellPerBlock))*1)) else cell end 'cell'

cell = -10 + (2 * ((-10*-1) % 2)*1) = 0

when cell < 0 then block - ((floor((cell * -1)/cellPerBlock))*1) else block end 'block'

block = 10 - (((-10*-1) % 2)*1) = 5

The *1 at the end is to handle the event when your while loop would have ran once but my formula would deliver a quotient of 0. I think my brackets and maths is right, not tested it.

这篇关于Sql选择查询问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！