Sql选择查询问题。

本文介绍了Sql选择查询问题。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个像这样的查询的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， case 当 cell> = cellPerBlock 然后块+（cell / cellPerBlock）当 cell< 0 然后块 - （（floor（（cell * -1）/ cellPerBlock））* 1） else block end ' block'， case cell> = cellPerBlock floor（cell / cellPerBlock） cell< 0 cell +（cellPerBlock *（（floor（（cell * -1）/ cellPerBlock））* 1）） else cell end ' cell' FROM [tbCus]

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

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

既然你说了一个查询，我把它作为一个陈述。 我取代了w用我认为数学等价物的循环。 （我不得不对你的数据做一些假设） 因此对于while循环

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

cell = -10 cellPerBlock = 2 block = 10 结果：cell = 0，block = 5 在查询中， 当 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.