select distinct level_id, count(distinct cust_id)as cust_count,sum(dkye) as dkye,sum(case when zzfljg=3 or zzfljg=4 or zzfljg=5 then dkye end) as bldkye,sum(case when zzfljg=3 or zzfljg=4 or zzfljg=5 then 1 end) as blcust_count //该处要过滤掉重复的记录from contInfowhere ten_id = 2004123group by level_id
----
怎么算是一条?不管其他字段的值?
你的语句好像只计算了zzfljg=3 or zzfljg=4 or zzfljg=5的记录数了,zzfljg不是3、4、5的时候计算吗?
如果用下面的话case when zzfljg=3 or zzfljg=4 or zzfljg=5 then 1 end就会得道3个纪录
level_id,
cust_count = count(distinct cust_id),
dkye = sum(dkye),
bldkye = sum(case when zzfljg=3 or zzfljg=4 or zzfljg=5 then dkye end),
blcust_count = (select
count(distinct zzfljg)
from
contInfo
where
ten_id=a.ten_id
and
level_id=a.level_id
and
zzfljg in(3,4,5) )
from
contInfo a
where
ten_id = 2004123
group by
level_id
如下:
select
level_id,
cust_count = count(distinct cust_id),
dkye = sum(dkye),
bldkye = sum(case when zzfljg=3 or zzfljg=4 or zzfljg=5 then dkye end),
blcust_count = ((select
count(zzfljg)
from
contInfo
where
ten_id=a.ten_id
and
level_id=a.level_id
and
zzfljg in(3,4,5) )+9999)/10000
from
contInfo a
where
ten_id = 2004123
group by
level_id
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestFun]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[TestFun]
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCREATE FUNCTION TestFun(@i int)
RETURNS int
AS
BEGIN
if (@i=3 or @i=4 or @i=5)
return 3
return @i
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
然后构造如下SQL:
select
level_id,
cust_count = count(distinct cust_id),
dkye = sum(dkye),
bldkye = sum(case when zzfljg=3 or zzfljg=4 or zzfljg=5 then dkye end),
blcust_count = count(distinct DBO.TestFun(zzfljg))
from
contInfo
where
ten_id = 2004123
group by
level_id//不过以上要求在level_id相同的情况下zzfljg不能有重复