count计算比例

SylarZhou 2012-01-13 02:42:15

 写了个查询语句

select t3.FName,sum(case when isnull(t1.FLatePeriod,0)>0 and isnull(t1.FLatePeriod,0)<=12 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt1,

sum(case when isnull(t1.FLatePeriod,0)>12 and isnull(t1.FLatePeriod,0)<=24 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt2,

sum(case when isnull(t1.FLatePeriod,0)>24 and isnull(t1.FLatePeriod,0)<=36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt3,

sum(case when isnull(t1.FLatePeriod,0)>36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt4,

sum(isnull(t1.FLateAmt,0)) SumAmt,sum(isnull(t1.FVDebitAmt,0)) FVDebitAmt,

(count(case when isnull(t1.FLatePeriod,0)<>'0' then t1.fid end)/count(case when isnull(t4.FStatus,0)='1' then t4.fid end)) FYQB

from avw_1598 t1 inner join xd_cust t2 on t1.fid=t2.fid

inner join T_DepartMent t3 on t3.FItemID=t2.FDeptID

inner join XD_DKSQZS t4 on t1.fid=t4.fid 

group by t3.FName

其中最后的那个 count的计算得出的数据为0，但是我把两个count单独分出来执行

select count(case when isnull(FLatePeriod,0)<>'0' then fid end) from avw_1598

select count(case when isnull(FStatus,0)='1' then fid end) from XD_DKSQZS

得到的是 1519  47871 请高人指教 错误在何方

...全文

180 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

唐诗三百首 2012-01-13

打赏
举报

try this,



select t3.FName,sum(case when isnull(t1.FLatePeriod,0)>0 and isnull(t1.FLatePeriod,0)<=12 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt1,

sum(case when isnull(t1.FLatePeriod,0)>12 and isnull(t1.FLatePeriod,0)<=24 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt2,

sum(case when isnull(t1.FLatePeriod,0)>24 and isnull(t1.FLatePeriod,0)<=36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt3,

sum(case when isnull(t1.FLatePeriod,0)>36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt4,

sum(isnull(t1.FLateAmt,0)) SumAmt,sum(isnull(t1.FVDebitAmt,0)) FVDebitAmt,

(cast(count(case when isnull(t1.FLatePeriod,0)<>'0' then t1.fid end) as decimal(9,5))

/count(case when isnull(t4.FStatus,0)='1' then t4.fid end)) FYQB

from avw_1598 t1 

inner join xd_cust t2 on t1.fid=t2.fid

inner join T_DepartMent t3 on t3.FItemID=t2.FDeptID

inner join XD_DKSQZS t4 on t1.fid=t4.fid 

group by t3.FName

叶子 2012-01-13

打赏
举报



declare @i int set @i=2

declare @j int set @j=3



select @i/@j /*0*/



select @i*1.00/@j /*0.6666666666666*/

叶子 2012-01-13

打赏
举报

给你举个例子：



declare @T1 table (col int)

insert into @T1

select 1 union all

select 2 union all

select 3 union all

select 4

select 

COUNT(CASE WHEN ISNULL(t1.col, 0) <> '0' THEN t1.col END) from @T1 t1

/*

4

*/

declare @T2 table (col int)

insert into @T2

select 5 union all

select 6 union all

select 7 union all

select 8



select 

COUNT(CASE WHEN ISNULL(t2.col, 0) <> '0' THEN t2.col END) from @T2 t2

/*

4

*/

select 

COUNT(CASE WHEN ISNULL(t2.col, 0) <> '0' THEN t2.col END)/

(COUNT(CASE WHEN ISNULL(t1.col, 0) <> '0' THEN t1.col END)+1) from @T1 t1 INNER JOIN 

@T2 t2 ON t1.col=t2.col

/*

0

*/

SylarZhou 2012-01-13

打赏
举报

[Quote=引用 3 楼 maco_wang 的回复:]

单独执行和放在里面是不一样的，在里面是 INNER JOIN ，内连接，就相当于多了很多where 条件。
[/Quote]
已经解决了，我把count的数据类型转换成float 就得出百分比了

select t3.FName,sum(case when isnull(t1.FLatePeriod,0)>0 and isnull(t1.FLatePeriod,0)<=12 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt1,

sum(case when isnull(t1.FLatePeriod,0)>12 and isnull(t1.FLatePeriod,0)<=24 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt2,

sum(case when isnull(t1.FLatePeriod,0)>24 and isnull(t1.FLatePeriod,0)<=36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt3,

sum(case when isnull(t1.FLatePeriod,0)>36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt4,

sum(isnull(t1.FLateAmt,0)) SumAmt,sum(isnull(t1.FVDebitAmt,0)) FVDebitAmt,

(cast(count(case when isnull(t1.FLatePeriod,0)<>'0' then t1.fid end) as float)/cast(count(case when isnull(t4.FStatus,0)='1' then t4.fid end) as float)) FYQB

from avw_1598 t1 inner join xd_cust t2 on t1.fid=t2.fid

inner join T_DepartMent t3 on t3.FItemID=t2.FDeptID

inner join XD_DKSQZS t4 on t1.fid=t4.fid 

group by t3.FName

叶子 2012-01-13

打赏
举报

单独执行和放在里面是不一样的，在里面是 INNER JOIN ，内连接，就相当于多了很多where 条件。

SylarZhou 2012-01-13

打赏
举报

[Quote=引用 1 楼 maco_wang 的回复:]

SQL code

--没弄明白你说的是什么意思

[/Quote]

( COUNT(CASE WHEN ISNULL(t1.FLatePeriod, 0) <> '0' THEN t1.fid

                END) / COUNT(CASE WHEN ISNULL(t4.FStatus, 0) = '1' THEN t4.fid

                             END) ) FYQB   就是这句语句执行得到的结果是0  但是他不应该是0

叶子 2012-01-13

打赏
举报



--没弄明白你说的是什么意思

SELECT  t3.FName ,

        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 0

                      AND ISNULL(t1.FLatePeriod, 0) <= 12

                 THEN ISNULL(t1.FLateAmt, 0)

                 ELSE '0'

            END) FLateAmt1 ,

        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 12

                      AND ISNULL(t1.FLatePeriod, 0) <= 24

                 THEN ISNULL(t1.FLateAmt, 0)

                 ELSE '0'

            END) FLateAmt2 ,

        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 24

                      AND ISNULL(t1.FLatePeriod, 0) <= 36

                 THEN ISNULL(t1.FLateAmt, 0)

                 ELSE '0'

            END) FLateAmt3 ,

        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 36

                 THEN ISNULL(t1.FLateAmt, 0)

                 ELSE '0'

            END) FLateAmt4 ,

        SUM(ISNULL(t1.FLateAmt, 0)) SumAmt ,

        SUM(ISNULL(t1.FVDebitAmt, 0)) FVDebitAmt ,

        ( COUNT(CASE WHEN ISNULL(t1.FLatePeriod, 0) <> '0' THEN t1.fid

                END) / COUNT(CASE WHEN ISNULL(t4.FStatus, 0) = '1' THEN t4.fid

                             END) ) FYQB

FROM    avw_1598 t1

        INNER JOIN xd_cust t2 ON t1.fid = t2.fid

        INNER JOIN T_DepartMent t3 ON t3.FItemID = t2.FDeptID

        INNER JOIN XD_DKSQZS t4 ON t1.fid = t4.fid

GROUP BY t3.FName