一种很神奇但是也很有意思的分组方式

1.需求

现在有这样一张表，

ID	FBatchNo	FQTY
1	01/01	11
1	01/02	12
2	01/01	23
2	02/01	55
3	02/02	20
3	02/03	55
4	02/01	15
3	03/01	100

要把它分组求FQTY的和，讲道理就是一个group by的事。但是分组的依据既不是ID也不是FBatchNo，而是“ID或FBatchNo”。
也就是说：
第一行和第二行要合并求和（因为ID相同）；
第一行和第三行也要合并求和（因为FBatchNo相同）；
所以第一二三行要合并求和。接着往下，第四行也要并进去，第七行（ID=4那行）也要并进去。最后ID为1、2、4的FQTY都是11+12+23+55+15=116，而ID为3的数据因为FBatchNo并没有和其他行存在相同，所以它的FQTY只有20+55+100=175。结果如下：

ID	FQTY
1	116
2	116
3	116
4	175

2.测试数据

测试数据的代码不是我写的，是从楼里复制来的，稍微改了点儿。感谢二月十六版主的测试数据的创建代码。

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[FBatchNo] nvarchar(25),[FQTY] int)
Insert #T
select 1,N'01/01',11 union all
select 1,N'01/02',12 union all
select 2,N'01/01',23 union all
select 2,N'02/01',55 union all
select 3,N'02/02',20 union all
select 3,N'02/03',55 union all
select 4,N'02/01',15 union all
select 3,N'03/01',100
Go

3.思路历程

惭愧的很，想了一会儿才意识到这个问题麻烦在哪，本来打算用join处理的。

因为一开始是这样想的：

A和1、2相关（ID1有FBatchNo1、FBatchNo2），我先找到A的相关（FBatchNo1、FBatchNo2），在找到A的相关的相关（与FBatchNo1或FBatchNo2有对应关系的ID），就找到B了，然后求FQTY和就行了。

于是我就开始写了，

--查出最后结果的几列
SELECT A.ID,SUM(ISNULL(A.FQTY,0))+SUM(ISNULL(C.FQTY,0)) FQTY 
--首先FROM第一张表，自己
FROM #T A 
--自连接on A.FBatchNo = B.FBatchNo AND A.ID != B.ID，就是上面说的找到A相关的相关
LEFT JOIN #T B ON A.FBatchNo = B.FBatchNo AND A.ID != B.ID
--这个其实就是找自连接出来的表的FQTY，就是已经找到A相关的相关，只要对FQTY求和就行了
--这里的sum是A相关的相关的FQTY的和，再加上A的FQTY的和就可以了
--就是第一行的SUM(ISNULL(A.FQTY,0))+SUM(ISNULL(C.FQTY,0))
LEFT JOIN (
SELECT T.ID,SUM(T.FQTY) FQTY FROM #T T GROUP BY T.ID
) C ON B.ID = C.ID 
GROUP BY A.ID

好像没什么问题，实际上用题主的测试数据运行这段SQL得出的也确实是他想要的答案。但是好像有点不对劲，一是一横，二是两横，三是三横，万怎么写？

发现的问题如下：

很明显，如果想把A和D没有直接关联，我得找A的相关（1、2）的相关（B）的相关（1、4）的相关（D），得多join几次了，如果到Z呢，不是得join死，而且我怎么知道要join几次。所以上面的代码其实并不正确。

4.思路历程2

这会儿说这种话有点事后诸葛亮了，但是我还是想说，看到这个问题之后我的第一反应就是，用递归。但是我是一个很怕麻烦的人，所以我想试试能不能用更tou好lan的办法来写。但是我没想出来，只能老老实实的用递归做了。

为什么一开始就想用递归呢，因为我看到这个问题，就感觉它和一个东西很像——贪吃蛇。

还是用上面的图举例，我先找到A-1，好，往下，找到A-2，ID相同，A-1把A-2吞并，变成了A-1,2；再往下，是B-1，A-1,2里有1，吃掉，变成A,B-1,2，然后吃吃吃吃吃…会变成A,B,D-1,2,4,5，也就是A,B,D都要用1,2,4,5的FQTY来求和了。如果要这样一行一行的分析处理，我好像只会用递归。

--CTE只是把原表加个行号，毕竟唯一标识还是要有的，而且递归里经常用到A.RN>B.RN或A.RN=B.RN+1这种条件
;WITH CTE AS (
SELECT *,ROW_NUMBER() OVER(ORDER BY A.ID,A.FBATCHNO) RN FROM #T A 
),
--“贪吃蛇”
CTE1 AS (
SELECT 
--第一条数据的本体id就是他自己的id
CAST(A.ID AS VARCHAR(MAX)) IDS,--本体命名为IDS（本来打算用stuff（（','+ID））写的）
* FROM CTE A WHERE A.RN = 1
UNION ALL
--一个简单的case when判断
SELECT CASE WHEN 
--如果和前面的数据存在ID或FBatchNo相同....
A.ID = B.ID OR A.FBatchNo = B.FBatchNo
--就会被吃，变成别人身体的一部分，所以你的本体就是别人的本体id了
THEN B.IDS 
--如果没有被吃，那你的本体id还是你自己的id
ELSE CAST(A.ID AS VARCHAR(MAX)) END,
A.* FROM CTE A , CTE1 B 
--这里应该可以优化下，不然CTE1的数据会非常非常非常之多，但是我懒得想别的条件了
--（关于此，我确信已发现了一种美妙的条件，可惜这里空白的地方太小，写不下）
WHERE A.RN > B.RN 
),
--CTE2的存在意义就是，CTE1运行完会出现这样的情况：大蛇（ABC）吃中蛇(AC)，中蛇吃小蛇(C)
--然后小蛇会有好多本体，因为它既能被大蛇吃也能被中蛇吃
--要找吃掉每个最小蛇（单独的数据）的最大蛇本体IDS，其实就是最小IDS，因为在越在前边出现的大蛇吃的就越多
CTE2 AS(
SELECT MIN(IDS) IDS,A.ID,A.FBatchNo,A.FQTY 
FROM CTE1 A 
GROUP BY A.RN,A.ID,A.FBatchNo,A.FQTY)
--接下来就是根据大蛇的本体IDS分组求和了，然后再把计算结果join一下处理成想看到的样子就好了
SELECT A.ID,B.FQTY FROM CTE2 A 
LEFT JOIN (SELECT X.IDS,SUM(FQTY) FQTY FROM CTE2 X GROUP BY X.IDS) B ON A.IDS = B.IDS
GROUP BY A.ID,B.FQTY

5.原贴

链接: 一个超级复杂难住无数程序员的SQL问题.

虽然才疏学浅，但是看到这种“奇葩”问题还是会见猎心喜，忍不住思考一下。只是最后还是用了递归，总觉得有点不甘心（虽然贪吃蛇也挺有意思的）。如果有大神无聊的时候看到了这篇低技术力的博客，还望不吝赐教，感谢感谢！