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