求一个查询语句，关于日绩效统计的最效率语句

[lightmusic]

不考虑其他没用的字段
只用到的字段为
UserID 销售ID
SellPrice 销售价
SellTime 出售时间

要得到的结果是，每个销售员每天售出总额
用这个结果我自己的一个公式计算得到他当天的奖金（日销售总是1000-2000奖励20元，2000-3000奖励50元等等）

难点不是每天统计，就是每月统计一次，就是这个月统计上个月的，但是这个统计是按日结算的，所以
最终的结果是每个用户的日奖金数是每天奖金的总和。

[reenjie]

先按天統計每天的獎金，然後再sum一下，就得到整月的

[中国风]

用以下格式显示测测是否你要的效果

SELECT  ISNULL(MM,'小计') AS MM ,
        ISNULL(DD,'')  AS DD,
        SUM(CASE WHEN SellPrice >= 1000
                      AND t.SellPrice < 2000 THEN 20
                 WHEN SellPrice >= 2000
                      AND t.SellPrice < 5000 THEN 50
                 WHEN SellPrice >= 5000 THEN 100
                 ELSE 0
            END) AS SellPrice
FROM    ( SELECT    CONVERT(VARCHAR(7), SellTime, 120) AS MM ,
                    CONVERT(VARCHAR(10), SellTime, 120) AS DD ,
                    SUM(SellPrice) AS SellPrice
          FROM      TabName
          GROUP BY  CONVERT(VARCHAR(7), SellTime, 120) ,
                    CONVERT(VARCHAR(10), SellTime, 120)
        ) AS t
GROUP BY MM ,
        DD
        WITH ROLLUP
HAVING  GROUPING(MM) = 1
        OR GROUPING(DD) = 0

[reenjie]

select UserId,CONVERT(varchar(10),SellTime,23) as SellDay, case when sum(SellPrice) between 1000 and 2000 then 20 when sum(SellPrice) between 2001 and 3000 then 50 ...... end as JM from tablename where CONVERT(varchar(7), DATEADD(M,-1,GETDATE()),23)=CONVERT(varchar(7),SellTime,23) group by UserId,CONVERT(varchar(10),SellTime,23)

[lightmusic]

引用 2 楼 roy_88 的回复:

用以下格式显示测测是否你要的效果

SELECT  ISNULL(MM,'小计') AS MM ,
        ISNULL(DD,'')  AS DD,
        SUM(CASE WHEN SellPrice >= 1000
                      AND t.SellPrice < 2000 THEN 20
                 WHEN SellPrice >= 2000
                      AND t.SellPrice < 5000 THEN 50
                 WHEN SellPrice >= 5000 THEN 100
                 ELSE 0
            END) AS SellPrice
FROM    ( SELECT    CONVERT(VARCHAR(7), SellTime, 120) AS MM ,
                    CONVERT(VARCHAR(10), SellTime, 120) AS DD ,
                    SUM(SellPrice) AS SellPrice
          FROM      TabName
          GROUP BY  CONVERT(VARCHAR(7), SellTime, 120) ,
                    CONVERT(VARCHAR(10), SellTime, 120)
        ) AS t
GROUP BY MM ,
        DD
        WITH ROLLUP
HAVING  GROUPING(MM) = 1
        OR GROUPING(DD) = 0

感谢版主，经过修改你的代码实现了功能哈哈~~解决了我大问题~结贴了啊~ 修改后后的代码：

select CreateUserID,SUM(Bonus) from
(
SELECT  MM ,
        DD,
        CreateUserID,
        SellPrice,
        dbo.GetBonus(SUM(SellPrice)) AS Bonus
FROM    ( SELECT    MONTH(UpdateTime) AS MM ,
                    DAY(PayTime) AS DD ,
                    CreateUserID,
                    SUM(SellPrice) AS SellPrice
          FROM      Jc_Article
          where PayClass>0
          GROUP BY  MONTH(UpdateTime) ,
                    DAY(PayTime),
                    CreateUserID
        ) AS t
GROUP BY MM ,
        DD,
        SellPrice,
        CreateUserID
        ) as c
        group by CreateUserID

奖励函数：

ALTER FUNCTION [dbo].[GetBonus] 
(
	@SellPrice decimal(11,4)
)
RETURNS decimal(11,4)
AS
BEGIN
	IF @SellPrice >=1500 AND @SellPrice <2000
	BEGIN
		RETURN 20
	END
	IF @SellPrice >=2000 AND @SellPrice <3000
	BEGIN
		RETURN 50
	END
	IF @SellPrice >=3000 AND @SellPrice <4000
	BEGIN
		RETURN 80
	END
	IF @SellPrice >=4000 AND @SellPrice <5000
	BEGIN
		RETURN 150
	END
	RETURN 0
END