分不够，SQL核算消费还差一个条件如何是好？

2018-09-13 09:58:54 246 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

清水鱼1984 墨衍会员 · AI 创作全网分发关注 + 加入社区

数字孪生ROI核算实战：成本收益可量化、可审计、可复用数字孪生不是3D可视化展示，而是面向资产运营的闭环价值系统。其核心原理在于通过物理模型、实时传感与业务规则融合，将设备状态、工艺参数和运维决策映射为可计算的财务指标。技术价值体现在降低非计划停机、压降备件库存、优化能耗与延长设备寿命等刚性收益上，但必须建立在数据质量达标率≥92%、模型持续标定、人机协同流程重构等前提之上。典型应用场景覆盖重资产制造业、基础设施运维与流程工业，尤其在焊接机器人预警、地铁信号根因定位、精馏塔蒸汽优化等高ROI环节已验证实效。本文聚焦数字孪生落地中最易被低估的传感器部署成本与模型阅读详情

数据结构是
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元

...全文

GAN网络详解析介绍目前常见的GAN网络，以及GAN网络的基本组成阅读详情

清水鱼1984

非技术版 11,847 成员 33,667 内容

+ 关注 + 加入社区

切换为时间正序

mingqing6364 2018-09-13

打赏
举报

引用 5 楼 yaoniwan1983 的回复:

多谢你对的！可惜我没分

分无所谓的，不过我那条SQL有个错误
name = '张三'会导致每个人都按张三的消费来减免
把外部和子查询的表别名一下
然后类似版主的，a.name = b.name

清水鱼1984 2018-09-13

打赏
举报

引用 2 楼 yenange 的回复:

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

*/

多谢版主给我思路！

吉普赛的歌 2018-09-13

打赏
举报

不谢，没事就结贴吧

清水鱼1984 2018-09-13

打赏
举报

多谢各位提供思路，我研究一下再回复！

清水鱼1984 2018-09-13

打赏
举报

引用 3 楼 mingqing6364 的回复:

设计不合理，所以语句很长

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

	name

name bef aft
------ ----- -----
张三 58 46
(1 rows affected)

我对需求的理解是：
每天的中餐和晚餐，只要有一笔消费大于等于3元，就可以享受一次满减优惠
如果中餐第一笔消费2元，第二笔消费3元，则第二笔消费可以满减
中餐满减之后，晚餐也可以满减一次，一天最多满减两次
所以算出来的结果是58-3*4=46
不知道理解对了没有

多谢你对的！可惜我没分

mingqing6364 2018-09-13

打赏
举报

设计不合理，所以语句很长

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

	name

吉普赛的歌 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

打赏
举报

引用 9 楼 RINK_1 的回复:

[quote=引用 1 楼 RINK_1 的回复:]
你先根据具体时间给每条记录生成一个用餐类型的标志，这个标志只有两种值，分别就是”早餐和夜宵“、”中餐和晚餐“，然后根据日期和这个标志用ROW_NUMBER函数去生成一个序号，最终就是序号是1且”中餐和晚餐“的可以使用减3元的机制，其余就保留原值。

一开始的理解错了，受#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

[/quote]

受教了，呵呵！

RINK_1 2018-09-13

打赏
举报

引用 1 楼 RINK_1 的回复:

你先根据具体时间给每条记录生成一个用餐类型的标志，这个标志只有两种值，分别就是”早餐和夜宵“、”中餐和晚餐“，然后根据日期和这个标志用ROW_NUMBER函数去生成一个序号，最终就是序号是1且”中餐和晚餐“的可以使用减3元的机制，其余就保留原值。

一开始的理解错了，受#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

分不够，SQL核算消费还差一个条件如何是好？

相关推荐