11,849
社区成员
发帖
与我相关
我的任务
分享分不够，SQL核算消费还差一个条件如何是好？
数据结构是
Id Name startTime Money
1 张三 2018-08-01 08:00:00.000 4
2 张三 2018-08-01 11:00:00.000 10
3 张三 2018-08-01 11:30:00.000 10
4 张三 2018-08-01 17:00:00.000 10
5 张三 2018-08-02 08:00:00.000 3
6 张三 2018-08-02 11:20:00.000 3
7 张三 2018-08-02 17:10:00.000 9
8 张三 2018-08-02 18:00:00.000 9
计算方式是：早餐和夜宵（夜晚20:00:01- 凌晨10:30:00）不扣除3元
中餐和晚餐（10:30:00-14:00:00 16:30:00-20:00:00）如果金额大于3元，则扣去减去元，少于则当前消费额，同时段消费只能减去一次（重点）
使用的语句是：
select Name,SUM(CASE WHEN moneys>3 and CONVERT(time,startTime) between '10:00:00'and '20:00:00' THEN moneys-3 ELSE moneys END) as Money from test group by Name
结果是：
Name moneys
张三 43
要达到目标金额52(同时段2018-08-01 11:30:00.000，2018-08-02 18:00:00.000不能减去3块), 如果解决同时段消费只能减去一次3元
Id Name startTime Money
1 张三 2018-08-01 08:00:00.000 4
2 张三 2018-08-01 11:00:00.000 10
3 张三 2018-08-01 11:30:00.000 10
4 张三 2018-08-01 17:00:00.000 10
5 张三 2018-08-02 08:00:00.000 3
6 张三 2018-08-02 11:20:00.000 3
7 张三 2018-08-02 17:10:00.000 9
8 张三 2018-08-02 18:00:00.000 9
计算方式是：早餐和夜宵（夜晚20:00:01- 凌晨10:30:00）不扣除3元
中餐和晚餐（10:30:00-14:00:00 16:30:00-20:00:00）如果金额大于3元，则扣去减去元，少于则当前消费额，同时段消费只能减去一次（重点）
使用的语句是：
select Name,SUM(CASE WHEN moneys>3 and CONVERT(time,startTime) between '10:00:00'and '20:00:00' THEN moneys-3 ELSE moneys END) as Money from test group by Name
结果是：
Name moneys
张三 43
要达到目标金额52(同时段2018-08-01 11:30:00.000，2018-08-02 18:00:00.000不能减去3块), 如果解决同时段消费只能减去一次3元
...全文
请发表友善的回复…
发表回复
mingqing6364 2018-09-13
- 打赏
- 举报
分无所谓的，不过我那条SQL有个错误
name = '张三'会导致每个人都按张三的消费来减免
把外部和子查询的表别名一下
然后类似版主的，a.name = b.name
清水鱼1984 2018-09-13
- 打赏
- 举报
多谢版主给我思路！
吉普赛的歌 2018-09-13
- 打赏
- 举报
不谢， 没事就结贴吧 
清水鱼1984 2018-09-13
- 打赏
- 举报
多谢各位提供思路，我研究一下再回复！
清水鱼1984 2018-09-13
- 打赏
- 举报
多谢你对的！可惜我没分
mingqing6364 2018-09-13
- 打赏
- 举报
设计不合理，所以语句很长
name bef aft
------ ----- -----
张三 58 46
(1 rows affected)
我对需求的理解是：
每天的中餐和晚餐，只要有一笔消费大于等于3元，就可以享受一次满减优惠
如果中餐第一笔消费2元，第二笔消费3元，则第二笔消费可以满减
中餐满减之后，晚餐也可以满减一次，一天最多满减两次
所以算出来的结果是58-3*4=46
不知道理解对了没有
SELECT
name,
SUM ( moneys ) bef,
SUM ( moneys ) - (SELECT SUM(3) FROM
(
SELECT
CONVERT(date,startTime) date,
CASE
WHEN CONVERT(time,startTime) BETWEEN '10:30:00' AND '14:00:00' THEN '中餐'
WHEN CONVERT(time,startTime) BETWEEN '16:30:00' AND '20:00:00' THEN '晚餐'
END type,
MAX(moneys) max_money
FROM
test
WHERE
name = '张三'
AND
(
CONVERT(time,startTime) BETWEEN '10:30:00' AND '14:00:00'
OR
CONVERT(time,startTime) BETWEEN '16:30:00' AND '20:00:00'
)
GROUP BY
CONVERT(date,startTime),
CASE
WHEN CONVERT(time,startTime) BETWEEN '10:30:00' AND '14:00:00' THEN '中餐'
WHEN CONVERT(time,startTime) BETWEEN '16:30:00' AND '20:00:00' THEN '晚餐'
END
HAVING
MAX(moneys) >= 3
) t
) aft
FROM
test
GROUP BY
namename bef aft
------ ----- -----
张三 58 46
(1 rows affected)
我对需求的理解是：
每天的中餐和晚餐，只要有一笔消费大于等于3元，就可以享受一次满减优惠
如果中餐第一笔消费2元，第二笔消费3元，则第二笔消费可以满减
中餐满减之后，晚餐也可以满减一次，一天最多满减两次
所以算出来的结果是58-3*4=46
不知道理解对了没有
吉普赛的歌 2018-09-13
- 打赏
- 举报
USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test(
id INT IDENTITY(1,1) PRIMARY KEY,
[name] NVARCHAR(10),
startTime DATETIME,
[moneys] INT
)
GO
SET NOCOUNT ON;
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-01 08:00:00.000',4);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-01 11:00:00.000',10);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-01 11:30:00.000',10);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-01 17:00:00.000',10);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-02 08:00:00.000',3);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-02 11:20:00.000',3);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-02 17:10:00.000',9);
INSERT INTO test([name],[startTime],[moneys]) VALUES('张三','2018-08-02 18:00:00.000',9);
--查询
;WITH cte AS (
select [name],SUM(moneys) as moneys
from test
group by [name]
)
SELECT [name]
,[moneys] AS orginalMoneys
,moneys-(CASE WHEN EXISTS(
SELECT 1 FROM test WHERE test.name=cte.name AND moneys>3 and CONVERT(time,startTime) between '10:00:00'and '20:00:00'
) THEN 3 ELSE 0 END)
AS moneys
FROM cte
/*
name orginalMoneys moneys
---------- ------------- -----------
张三 58 55
*/
RINK_1 2018-09-13
- 打赏
- 举报
你先根据具体时间给每条记录生成一个用餐类型的标志，这个标志只有两种值，分别就是”早餐和夜宵“、”中餐和晚餐“，然后根据日期和这个标志用ROW_NUMBER函数去生成一个序号，最终就是序号是1且”中餐和晚餐“的可以使用减3元的机制，其余就保留原值。
清水鱼1984 2018-09-13
- 打赏
- 举报
分无所谓的，不过我那条SQL有个错误
name = '张三'会导致每个人都按张三的消费来减免
把外部和子查询的表别名一下
然后类似版主的，a.name = b.name[/quote]
单个没问题，多个 为什么我算出来是负数
1 张阳 226.00 88.00
2 张菊英 128.50 -9.50
3 雷泰山 198.50 60.50
4 陈辉平 58.50 -79.50
5 申欢迎 94.50 -43.50
6 龚焕仪 10.00 -128.00
清水鱼1984 2018-09-13
- 打赏
- 举报
还有个问题！如何批量
清水鱼1984 2018-09-13
- 打赏
- 举报
一开始的理解错了，受#3的启发，你试试下面的（借用#2版主的数据）
select name,sum(total-discount)
from
(select name,convert(varchar(10),starttime,23) as singledate,
max(case when qty>1 then 1 else 0 end)*3 as discount,SUM(moneys) AS TOTAL
from
(select *,
sum(case when (convert(varchar(10),starttime,108) between '10:30:00' and '14:00:00'
or convert(varchar(10),starttime,108) between '16:30:00' and '20:00:00')
and moneys>3 then 1 else 0 end) over (partition by name,convert(varchar(10),starttime,23)) as qty
from test) as A
group by name,convert(varchar(10),starttime,23)) as A
group by name
受教了，呵呵！
RINK_1 2018-09-13
- 打赏
- 举报
一开始的理解错了，受#3的启发，你试试下面的（借用#2版主的数据）
select name,sum(total-discount)
from
(select name,convert(varchar(10),starttime,23) as singledate,
max(case when qty>1 then 1 else 0 end)*3 as discount,SUM(moneys) AS TOTAL
from
(select *,
sum(case when (convert(varchar(10),starttime,108) between '10:30:00' and '14:00:00'
or convert(varchar(10),starttime,108) between '16:30:00' and '20:00:00')
and moneys>3 then 1 else 0 end) over (partition by name,convert(varchar(10),starttime,23)) as qty
from test) as A
group by name,convert(varchar(10),starttime,23)) as A
group by name
