sql将一个月的数据随机划分至每天

wuxixujia 2012-11-14 08:54:34

例如
2012年12月的总用电量为1000度
现在要把1000度划分到12月的31天里
不能平均划分
要是随机的
但是31天之和必须等于1000
请教高手
怎么写？

...全文

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

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

wuxixujia 2013-01-10

打赏
举报

引用 4 楼 coderabbit 的回复:

仅供参考 SQL code?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970select SUM(c) from ( select d,1000/34*c a……

貌似不行，不过还是谢谢

wuxixujia 2013-01-10

打赏
举报

引用 3 楼 DBA_Huangzj 的回复:

思路：用CTE嵌套循环，把1000度随机减少，最后那天就用1000-前30天总数。另外你这功能也太恶心了点

哈哈，客户要求没办法

wuxixujia 2013-01-10

打赏
举报

引用 2 楼 niss 的回复:

写个小程序吧,数据库是用来存取数据的,不是用来实现程序逻辑的,你用水果刀杀猪了

呵呵，比喻很形象，客户要求没办法

代码兔 2012-11-16

打赏
举报

仅供参考



select SUM(c) from (

select d,1000/34*c as c from (
	select d,c from (
		select 1 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 4 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 7 as d,ceiling(rand()*6)/10+0 as c
		UNION
		select 11 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 14 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 17 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 21 as d,ceiling(rand()*6)/10+0 as c
		UNION
		select 24 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 27 as d,ceiling(rand()*6)/10+1 as c) d1
		UNION
	select d,c from (
		select 2 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 5 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 8 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 12 as d,ceiling(rand()*6)/10+0 as c
		UNION
		select 15 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 18 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 22 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 25 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 28 as d,ceiling(rand()*5)/10+1 as c) d2
		UNION
	select d,c from (
		select 3 as d,ceiling(rand()*5)/10+1 as c
		UNION
		select 6 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 9 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 13 as d,ceiling(rand()*6)/10+0 as c
		UNION
		select 16 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 19 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 23 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 26 as d,ceiling(rand()*6)/10+0 as c
		UNION
		select 29 as d,ceiling(rand()*6)/10+1 as c
		UNION
		select 10 as d,abs(ceiling(rand()*3)/10-1) as c
		UNION
		select 20 as d,abs(ceiling(rand()*3)/10-1) as c
		UNION
		select 30 as d,abs(ceiling(rand()*3)/10-1) as c
		) d3
	) days
) dsum

----前面返回30天的结果，第31天可以用1000减去前30天的和。