sql语句

Question

模型需求计算问题，现有一个表TA，结构如下：
modelcj    model    modelps    size0    size1    size2    size3    size4    size5    heji
liu        813      6          11       19       24       23       13       8         98
chen       605      9           5       17       28       21       19       12        102
..........................................................................................如何通过计算得到下列结果：实际上就是把表TA中各个size除以modelps,若能整除就得这个商，若不能整除，则size除以modelps取整后再加1 ，请问各位该如何用语句实现？？？
modelcj    model    ysize0    ysize1    ysize2    ysize3    ysize4    ysize5    yheji
liu        813        2        4        4        4         3         2          19
chen       605        1        2        4        3         3         2          15

Answer 1

select modelcj ,model,[ysize0],[ysize1],[ysize2],[ysize3],[ysize4],[ysize5],heji=[ysize0]+[ysize1]+[ysize2]+[ysize3]+[ysize4]+[ysize5]
from (select modelcj ,model,
[ysize0]=case when size0%modelps=0 then size0/modelps else (size0/modelps)+1 end,
[ysize1]=case when size1%modelps=0 then size1/modelps else (size1/modelps)+1 end,
[ysize2]=case when size2%modelps=0 then size2/modelps else (size2/modelps)+1 end,
[ysize3]=case when size3%modelps=0 then size3/modelps else (size3/modelps)+1 end,
[ysize4]=case when size4%modelps=0 then size4/modelps else (size4/modelps)+1 end,
[ysize5]=case when size5%modelps=0 then size5/modelps else (size5/modelps)+1 end
from ta) a

Answer 2

create table aaaa ( modelps int,size0 int,size2 int) 
insert into aaaa values (6,11,24)
insert into aaaa values (9,5,28)select case when size0 % modelps=0 then size0%modelps else size0/modelps + 1 end from aaaa

Answer 3

 DECLARE @T1 TABLE(modelcj nvarchar(50),  model INT,  modelps INT,  size0 INT, size1 INT, size2 INT, size3 INT, size4 INT, size5 INT, heji INT) 
INSERT @T1 SELECT 'liu' ,813 ,     6 ,         11,      19,      24,      23 ,     13 ,     8  ,      98 
INSERT @T1 SELECT 'chen ',     605 ,     9  ,        5 ,    17 ,     28  ,    21  ,    19  ,    12  ,      102 
select modelcj, model, ceiling(size0*1.0/modelps) as ysize0 , ceiling(size1*1.0/modelps) as    ysize1 , ceiling(size2*1.0/modelps) as   ysize2 , ceiling(size3*1.0/modelps) as   ysize3 , ceiling(size4*1.0/modelps) as   ysize4, ceiling(size5*1.0/modelps) as    ysize5, ceiling(heji*1.0/modelps) as    yheji from @T1
 结果：(1 行受影响)(1 行受影响)
modelcj                                            model       ysize0                                  ysize1                                  ysize2                                  ysize3                                  ysize4                                  ysize5                                  yheji
-------------------------------------------------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
liu                                                813         2                                       4                                       4                                       4                                       3                                       2                                       17
chen                                               605         1                                       2                                       4                                       3                                       3                                       2                                       12(2 行受影响)

Answer 4

DECLARE @T1 TABLE(modelcj nvarchar(50),  model INT,  modelps INT,  size0 INT, size1 INT, size2 INT, size3 INT, size4 INT, size5 INT, heji INT) 
INSERT @T1 SELECT 'liu' ,813 ,     6 ,         11,      19,      24,      23 ,     13 ,     8  ,      98 
INSERT @T1 SELECT 'chen ',     605 ,     9  ,        5 ,    17 ,     28  ,    21  ,    19  ,    12  ,      102 
select modelcj, model, ceiling(size0*1.0/modelps) as ysize0 , ceiling(size1*1.0/modelps) as    ysize1 , ceiling(size2*1.0/modelps) as   ysize2 , ceiling(size3*1.0/modelps) as   ysize3 , ceiling(size4*1.0/modelps) as   ysize4, ceiling(size5*1.0/modelps) as    ysize5, (ceiling(size0*1.0/modelps)+ceiling(size1*1.0/modelps)+ceiling(size2*1.0/modelps)+ceiling(size3*1.0/modelps)+ceiling(size4*1.0/modelps)+ceiling(size5*1.0/modelps)) as    yheji from @T1
 结果：(1 行受影响)(1 行受影响)
modelcj                                            model       ysize0                                  ysize1                                  ysize2                                  ysize3                                  ysize4                                  ysize5                                  yheji
-------------------------------------------------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
liu                                                813         2                                       4                                       4                                       4                                       3                                       2                                       19
chen                                               605         1                                       2                                       4                                       3                                       3                                       2                                       15(2 行受影响)