select avg(min_lvl*1.0/max_lvl ) form tb where (min_lvl <> 0 or min_lvl is not null) and (max_lvl <> 0 or max_lvl is not null)
if object_id('tb')is not null drop table tb go create table tb(min_lvl int, max_lvl int) insert tb select 5 , 8 insert tb select 6 , 4 insert tb select null , null insert tb select 0 , 0 select sum(min_lvl)*1.0/sum(case when isnull(min_lvl,0)<>0 then 1 else 0 end), sum(max_lvl)*1.0/sum(case when isnull(max_lvl,0)<>0 then 1 else 0 end) from tb /*-------------------------- -------------------------- 5.500000000000 6.000000000000(影響 1 個資料列)*/
create table tb(min_lvl int, max_lvl int) insert into tb values(5 , 8 ) insert into tb values(6 , 4 ) insert into tb values(null , null) insert into tb values(0 , 0 )goselect avg(min_lvl*1.0/max_lvl) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)drop table tb/*
create table tb(min_lvl int, max_lvl int) insert into tb values(5 , 8 ) insert into tb values(6 , 4 ) insert into tb values(null , null) insert into tb values(0 , 0 )go select * , min_lvl*1.0/max_lvl from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null) /* min_lvl max_lvl ----------- ----------- -------------------------- 5 8 .625000000000 6 4 1.500000000000(所影响的行数为 2 行) */select avg(min_lvl*1.0/max_lvl) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null) /*
---------------------------------------- 1.062500000000(所影响的行数为 1 行) */ drop table tb
create table tb(min_lvl int, max_lvl int) insert into tb values(5 , 8 ) insert into tb values(6 , 4 ) insert into tb values(null , null) insert into tb values(0 , 0 ) goselect * , min_lvl*1.0/max_lvl from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null) /* min_lvl max_lvl ----------- ----------- -------------------------- 5 8 .625000000000 6 4 1.500000000000(所影响的行数为 2 行) */select avg(min_lvl*1.0/max_lvl) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null) /*
---------------------------------------- 1.062500000000(所影响的行数为 1 行) */select avg(min_lvl*1.0) , avg(max_lvl*1.0) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null) /*
select * ,a=(case when min_lvl=null then 0 when min_lvl=0 then 0 when max_lvl=null then 0 when max_lvl=null then 0 else min_lvl/max_lvl end) group by min_lvl,max_lvl
select * ,a=(case when min_lvl=null then 0 when min_lvl=0 then 0 when max_lvl=null then 0 when max_lvl=null then 0 else min_lvl/max_lvl end) from tb group by min_lvl,max_lvl
string sqld = "select avg(sdtimep*1.0/stime) as cc from Jx_momorderp where (sdtimep <> 0 or sdtimep is not null) and (stime <> 0 or stime is not null)"; 放在程序里报错?在SQL里没问题 ?
select avg(min_lvl*1.0/max_lvl ) form tb where (min_lvl <> 0 or min_lvl is not null) and (max_lvl <> 0 or max_lvl is not null)
go
create table tb(min_lvl int, max_lvl int)
insert tb select 5 , 8
insert tb select 6 , 4
insert tb select null , null
insert tb select 0 , 0
select sum(min_lvl)*1.0/sum(case when isnull(min_lvl,0)<>0 then 1 else 0 end),
sum(max_lvl)*1.0/sum(case when isnull(max_lvl,0)<>0 then 1 else 0 end)
from tb
/*-------------------------- --------------------------
5.500000000000 6.000000000000(影響 1 個資料列)*/
insert into tb values(5 , 8 )
insert into tb values(6 , 4 )
insert into tb values(null , null)
insert into tb values(0 , 0 )goselect avg(min_lvl*1.0/max_lvl) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)drop table tb/*
----------------------------------------
1.062500000000(所影响的行数为 1 行)
*/
insert into tb values(5 , 8 )
insert into tb values(6 , 4 )
insert into tb values(null , null)
insert into tb values(0 , 0 )go
select * , min_lvl*1.0/max_lvl from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
/*
min_lvl max_lvl
----------- ----------- --------------------------
5 8 .625000000000
6 4 1.500000000000(所影响的行数为 2 行)
*/select avg(min_lvl*1.0/max_lvl) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
/*
----------------------------------------
1.062500000000(所影响的行数为 1 行)
*/
drop table tb
insert into tb values(5 , 8 )
insert into tb values(6 , 4 )
insert into tb values(null , null)
insert into tb values(0 , 0 )
goselect * , min_lvl*1.0/max_lvl from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
/*
min_lvl max_lvl
----------- ----------- --------------------------
5 8 .625000000000
6 4 1.500000000000(所影响的行数为 2 行)
*/select avg(min_lvl*1.0/max_lvl) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
/*
----------------------------------------
1.062500000000(所影响的行数为 1 行)
*/select avg(min_lvl*1.0) , avg(max_lvl*1.0) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
/*
---------------------------------------- ----------------------------------------
5.500000 6.000000(所影响的行数为 1 行)*/drop table tb
from tb
group by min_lvl,max_lvl
放在程序里报错?在SQL里没问题 ?
min_lvl max_lvl
5 8
6 4
null null
0 0
我要的结果是
min_lvl max_lvl aa
5 8 0.652
null null
0 0 0
等于在表的结构里加入计算!! 谢谢