一个很绕的SQL需求：双向“匀”数

[haitao]

因为这种需求的模拟数据就很难写典型，所以上次没能得到一个双向匀的SQL
现在重新来：

fhid fxid fneed
1111 1 21
1111 2 10
1111 3 7
1111 4 16
2222 11 6
2222 12 13
2222 13 27
2222 14 5
2222 15 14
2222 16 7
3333 21 8
3333 22 15
3333 23 17
3333 24 11
3333 25 13
3333 26 6
3333 27 14



fhid fpid fleft
1111 51 8
1111 52 13
1111 53 17
1111 54 26
1111 55 3
1111 56 4
2222 61 18
2222 62 3
2222 63 12
2222 64 11
2222 65 8
3333 71 11
3333 72 4
3333 73 27
3333 74 16
3333 75 13
3333 76 7

需要得到：第5列为说明信息
fhid fxid fpid fcount
1111 1 51 8 2个p对1个x
1111 1 52 13
1111 2 53 10 2个x对1个p
1111 3 53 7
1111 4 54 16
2222 11 61 6 6<18
2222 12 61 12 13>18-6
2222 12 62 1 13-12<3
2222 13 62 2 27>3-1
2222 13 63 12 27-2>12
2222 13 64 11 27-2-12>11
2222 13 65 3 27-2-12-11=3
3333 21 71 8 8<11
3333 22 71 3 15>11-8
3333 22 72 4 15-3>4
3333 22 73 8 15-4-3<27
3333 23 73 17 17<27-8
3333 24 73 2 11>27-17-8
3333 24 74 9 11-2<16
3333 25 74 7 13>16-9
3333 25 75 6 13-7<13
3333 26 75 6 6<13-6
3333 27 75 1 14>13-6-6
3333 27 76 7 14-1>7


先这样发，等一会我改为利用表变量的SQL

[昵称被占用了]

程序处理

[dawugui]

等你发了,再说明一下.

[haitao]

SQL来了：
declare @x table(fhid int,fxid int,fneed int)
declare @y table(fhid int,fpid int,fleft int)
declare @w table(fhid int,fxid int,fpid int,fc int)

insert @x
select 1111,1,21
union all select 1111,2,10
union all select 1111,3,7
union all select 1111,4,16
union all select 2222,11,6
union all select 2222,12,13
union all select 2222,13,27
union all select 2222,14,5
union all select 2222,15,14
union all select 2222,16,7
union all select 3333,21,8
union all select 3333,22,15
union all select 3333,23,17
union all select 3333,24,11
union all select 3333,25,13
union all select 3333,26,6
union all select 3333,27,14

insert @y
select 1111,51,8
union all select 1111,52,13
union all select 1111,53,17
union all select 1111,54,26
union all select 1111,55,3
union all select 1111,56,4
union all select 2222,61,18
union all select 2222,62,3
union all select 2222,63,12
union all select 2222,64,11
union all select 2222,65,8
union all select 3333,71,11
union all select 3333,72,4
union all select 3333,73,27
union all select 3333,74,16
union all select 3333,75,13
union all select 3333,76,7

insert @w
select 1111,1,51,8	--2个p对1个x
union all select 1111,1,52,13	--
union all select 1111,2,53,10	--2个x对1个p
union all select 1111,3,53,7	--
union all select 1111,4,54,16	--
union all select 2222,11,61,6	--6<18
union all select 2222,12,61,12	--13>18-6
union all select 2222,12,62,1	--13-12<3
union all select 2222,13,62,2	--27>3-1
union all select 2222,13,63,12	--27-2>12
union all select 2222,13,64,11	--27-2-12>11
union all select 2222,13,65,3	--27-2-12-11=3
union all select 3333,21,71,8	--8<11
union all select 3333,22,71,3	--15>11-8
union all select 3333,22,72,4	--15-3>4
union all select 3333,22,73,8	--15-4-3<27
union all select 3333,23,73,17	--17<27-8
union all select 3333,24,73,2	--11>27-17-8
union all select 3333,24,74,9	--11-2<16
union all select 3333,25,74,7	--13>16-9
union all select 3333,25,75,6	--13-7<13
union all select 3333,26,75,6	--6<13-6
union all select 3333,27,75,1	--14>13-6-6
union all select 3333,27,76,7	--14-1>7


select * from @x
select * from @y
select * from @w

[haitao]

上次roy_88已经解出来了：
http://topic.csdn.net/u/20111011/17/7e49e263-5162-4d7b-af6f-7f062d870664.html

[-晴天]

你的匀数规则是什么?

[haitao]

[Quote=引用 5 楼 qianjin036a 的回复:]
你的匀数规则是什么?
[/Quote]

就是以fhid为中心，把2个不直接关联的fxid和fpid对应的数值
人为地“对应”起来
如fhid都是5555记录中，x表有1 3，2 8，3 5，y表有11 7，12 2，13 3
则：
因为3<7，于是
得到1 11 3
2 8再与11 7剩下的11 4比较
因为8>4
得到2 11 4
2 8剩下的2 4再与12 2比较
因为4>2
得到2 12 2
2 8剩下的2 2再与13 3比较
因为2<3
得到2 13 2

[hb_lewzh]

Greenhand,learning....

[haitao]

刚才想到一个非常急转弯的办法，肯定有效，但是效率肯定不行了
就像那个最蛋疼的排序办法：
为100个待排序的数，开100个线程，每个线程执行：sleep(x*100);printf("%d\n",x);//x是线程对应的数

所以，我的办法是：
建一个临时表z（id,fhid,fxid,fpid）
对于fhid,fxid,fneed的每条记录，
对z插入fneed条记录
。。。
这样，z将有sum(fneed)条记录！
然后按fhid,fpid,fleft的每条记录
z表fpid=null记录中，取按id的前fleft条，update set fpid=fpid
。。。
最后
select fhid,fxid,fpid,count(*)
from z
group by fhid,fxid,fpid
就是@w的结果了

——仅仅用于增加搞笑气氛

[-晴天]

[Quote=引用 6 楼 sz_haitao 的回复:]
引用 5 楼 qianjin036a 的回复:
你的匀数规则是什么?


就是以fhid为中心，把2个不直接关联的fxid和fpid对应的数值
人为地“对应”起来
如fhid都是5555记录中，x表有1 3，2 8，3 5，y表有11 7，12 2，13 3
则：
因为3<7，于是
得到1 11 3
2 8再与11 7剩下的11 4比较
因为8>4
得到2 11 4
2 ……
[/Quote]

什么是 5555?
你为什么不直接用你列出的数据做例子呢?

[haitao]

列出的数据 已经有注释说明了，虽然比较简略
而且可能关联的比较多，所以就举了一个数值小一点的

[haitao]

好像搞定了！通过excel对实例数据的反复构造公式。。。。。


declare @x table(fhid int,fxid int,fneed int)
declare @y table(fhid int,fpid int,fleft int)
declare @w table(fxid int,fpid int,fc int)
declare @i int,@h int

set @h=1111
set @i=1
insert @x
select @h,@i,21
union all select @h,@i+1,10
union all select @h,@i+2,7
union all select @h,@i+3,16

set @h=2222
set @i=11
insert @x
select @h,@i,6
union all select @h,@i+1,13
union all select @h,@i+2,27
union all select @h,@i+3,5
union all select @h,@i+4,14
union all select @h,@i+5,7

set @h=3333
set @i=21
insert @x
select @h,@i,8
union all select @h,@i+1,15
union all select @h,@i+2,17
union all select @h,@i+3,11
union all select @h,@i+4,13
union all select @h,@i+5,6
union all select @h,@i+6,14


set @h=1111
set @i=51
insert @y
select @h,@i,8
union all select @h,@i+1,13
union all select @h,@i+2,17
union all select @h,@i+3,26
union all select @h,@i+4,3
union all select @h,@i+5,4

set @h=2222
set @i=61
insert @y
select @h,@i,18
union all select @h,@i+1,3
union all select @h,@i+2,12
union all select @h,@i+3,11
union all select @h,@i+4,8

set @h=3333
set @i=71
insert @y
select @h,@i,11
union all select @h,@i+1,4
union all select @h,@i+2,27
union all select @h,@i+3,16
union all select @h,@i+4,13
union all select @h,@i+5,7


select * from @x
select * from @y



select *
,case when fneed0=fleft0 then 
	--case fneed>fleft then fleft else fneed end 
	dbo.min2(fneed,fleft)
else 
	--min(fneed,fleft,fneed-fleft0+fneed0,fleft-fneed0+fleft0) 
	dbo.min4(fneed,fleft,fneed-fleft0+fneed0,fleft-fneed0+fleft0)
end

from (
	select a.fhid,a.fxid,a.fneed,sum(isnull(b.fneed,0)) fneed0 from @x a
	left join @x b on a.fhid=b.fhid and a.fxid>b.fxid
	group by a.fhid,a.fxid,a.fneed
) a
left join (
	select a.fhid,a.fpid,a.fleft,sum(isnull(b.fleft,0)) fleft0 from @y a
	left join @y b on a.fhid=b.fhid and a.fpid>b.fpid
	group by a.fhid,a.fpid,a.fleft
) b on a.fhid=b.fhid --and (a.fxid=b.fxid and a.fpid>b.fpid or a.fxid>b.fxid)
where fneed0<fleft+fleft0 and fneed+fneed0>fleft0
order by a.fhid,a.fxid,b.fpid

结果是：

fhid	fxid	fneed	fneed0	fhid	fpid	fleft	fleft0	(无列名)
1111	1	21	0	1111	51	8	0	8
1111	1	21	0	1111	52	13	8	13
1111	2	10	21	1111	53	17	21	10
1111	3	7	31	1111	53	17	21	7
1111	4	16	38	1111	54	26	38	16
2222	11	6	0	2222	61	18	0	6
2222	12	13	6	2222	61	18	0	12
2222	12	13	6	2222	62	3	18	1
2222	13	27	19	2222	62	3	18	2
2222	13	27	19	2222	63	12	21	12
2222	13	27	19	2222	64	11	33	11
2222	13	27	19	2222	65	8	44	2
2222	14	5	46	2222	65	8	44	5
2222	15	14	51	2222	65	8	44	1
3333	21	8	0	3333	71	11	0	8
3333	22	15	8	3333	71	11	0	3
3333	22	15	8	3333	72	4	11	4
3333	22	15	8	3333	73	27	15	8
3333	23	17	23	3333	73	27	15	17
3333	24	11	40	3333	73	27	15	2
3333	24	11	40	3333	74	16	42	9
3333	25	13	51	3333	74	16	42	7
3333	25	13	51	3333	75	13	58	6
3333	26	6	64	3333	75	13	58	6
3333	27	14	70	3333	75	13	58	1
3333	27	14	70	3333	76	7	71	7