sql 统计数量

[_小黑_]

--原数据
;WITH TEMP AS
(
	SELECT 'WES4300719103000582' SERVICE_SHEET_NO,	'WI-C300' MODEL_CODE,	'8020410' SERIAL_NO,	'WI-C310' MOBILE_MODEL_CODE,	'0017876'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES1200019103001546' SERVICE_SHEET_NO,	'WI-C300' MODEL_CODE,	'8008351' SERIAL_NO,	'WI-C310' MOBILE_MODEL_CODE,	'0016674'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES1200019103001550' SERVICE_SHEET_NO,	'WH-CH500' MODEL_CODE,	'8017811' SERIAL_NO,	'WH-CH500' MOBILE_MODEL_CODE,	'8018930'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES1200719110700081' SERVICE_SHEET_NO,	'NW-WS413' MODEL_CODE,	'6073313' SERIAL_NO,	'NW-WS413' MOBILE_MODEL_CODE,	'6117484'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES3301119091500140' SERVICE_SHEET_NO,	'WI-C600N' MODEL_CODE,	'5061351' SERIAL_NO,	'WI-C600N' MOBILE_MODEL_CODE,	'5080268'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES4200419091600102' SERVICE_SHEET_NO,	'WI-1000X' MODEL_CODE,	'5383857' SERIAL_NO,	'WI-1000X' MOBILE_MODEL_CODE,	'5463800'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES4200419091600102' SERVICE_SHEET_NO,	'WI-1000X' MODEL_CODE,	'5463800' SERIAL_NO,	 NULL MOBILE_MODEL_CODE,	     NULL MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES2201219101900283' SERVICE_SHEET_NO,	'NW-WS623' MODEL_CODE,	'5421453' SERIAL_NO,	'NW-WS623' MOBILE_MODEL_CODE,	'5601874'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES2201219101900284' SERVICE_SHEET_NO,	'NW-WS623' MODEL_CODE,	'5601874' SERIAL_NO,	'NW-WS623' MOBILE_MODEL_CODE,	'5601875'MOBILE_SERIAL_NO UNION ALL
	SELECT 'WES2201219101900285' SERVICE_SHEET_NO,	'NW-WS623' MODEL_CODE,	'5601875' SERIAL_NO,	'NW-WS623' MOBILE_MODEL_CODE,	'5601876'MOBILE_SERIAL_NO UNION ALL
	SELECT 'TEST' SERVICE_SHEET_NO,	'TEST-WS623' MODEL_CODE,	'TEST_5421453' SERIAL_NO,	NULL MOBILE_MODEL_CODE,	NULL MOBILE_SERIAL_NO 
),B AS
(
SELECT SERVICE_SHEET_NO,MODEL_CODE,SERIAL_NO,MOBILE_MODEL_CODE,MOBILE_SERIAL_NO FROM TEMP A
WHERE MOBILE_MODEL_CODE IS NOT NULL AND MOBILE_SERIAL_NO IS NOT NULL
--WHERE A.MODEL_CODE = 'NW-WS623' AND A.SERIAL_NO = '5421453'
UNION ALL
SELECT A.SERVICE_SHEET_NO,A.MODEL_CODE,A.SERIAL_NO,A.MOBILE_MODEL_CODE,A.MOBILE_SERIAL_NO FROM TEMP A
INNER JOIN TEMP B ON A.MODEL_CODE = B.MOBILE_MODEL_CODE AND A.SERIAL_NO = B.MOBILE_SERIAL_NO
WHERE A.MOBILE_MODEL_CODE IS NOT NULL AND A.MOBILE_SERIAL_NO IS NOT NULL
)
SELECT * FROM TEMP

--想要的结果
;WITH TEMP AS
(
	SELECT 'WES4300719103000582' SERVICE_SHEET_NO,'WI-C300' MODEL_CODE,'8020410' SERIAL_NO,	'WI-C310' MOBILE_MODEL_CODE,'0017876'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'WES1200019103001546' SERVICE_SHEET_NO,'WI-C300' MODEL_CODE,'8008351' SERIAL_NO,	'WI-C310' MOBILE_MODEL_CODE,'0016674'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'WES1200019103001550' SERVICE_SHEET_NO,'WH-CH500' MODEL_CODE,'8017811' SERIAL_NO,'WH-CH500' MOBILE_MODEL_CODE,'8018930'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'WES1200719110700081' SERVICE_SHEET_NO,'NW-WS413' MODEL_CODE,'6073313' SERIAL_NO,'NW-WS413' MOBILE_MODEL_CODE,'6117484'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'WES3301119091500140' SERVICE_SHEET_NO,'WI-C600N' MODEL_CODE,'5061351' SERIAL_NO,'WI-C600N' MOBILE_MODEL_CODE,'5080268'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'WES4200419091600102' SERVICE_SHEET_NO,'WI-1000X' MODEL_CODE,'5383857' SERIAL_NO,'WI-1000X' MOBILE_MODEL_CODE,'5463800'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'WES4200419091600102' SERVICE_SHEET_NO,'WI-1000X' MODEL_CODE,'5463800' SERIAL_NO, NULL MOBILE_MODEL_CODE,	  NULL MOBILE_SERIAL_NO ,0 [COUNT] UNION ALL
	SELECT 'WES2201219101900283' SERVICE_SHEET_NO,'NW-WS623' MODEL_CODE,'5421453' SERIAL_NO,'NW-WS623' MOBILE_MODEL_CODE,'5601874'MOBILE_SERIAL_NO ,3 [COUNT] UNION ALL
	SELECT 'WES2201219101900284' SERVICE_SHEET_NO,'NW-WS623' MODEL_CODE,'5601874' SERIAL_NO,'NW-WS623' MOBILE_MODEL_CODE,'5601875'MOBILE_SERIAL_NO ,2 [COUNT] UNION ALL
	SELECT 'WES2201219101900285' SERVICE_SHEET_NO,'NW-WS623' MODEL_CODE,'5601875' SERIAL_NO,'NW-WS623' MOBILE_MODEL_CODE,'5601876'MOBILE_SERIAL_NO ,1 [COUNT] UNION ALL
	SELECT 'TEST' SERVICE_SHEET_NO,	'TEST-WS623' MODEL_CODE,	'TEST_5421453' SERIAL_NO,	NULL MOBILE_MODEL_CODE,	NULL MOBILE_SERIAL_NO  ,0 [COUNT]
),B AS
(
SELECT SERVICE_SHEET_NO,MODEL_CODE,SERIAL_NO,MOBILE_MODEL_CODE,MOBILE_SERIAL_NO FROM TEMP A
WHERE MOBILE_MODEL_CODE IS NOT NULL AND MOBILE_SERIAL_NO IS NOT NULL
--WHERE A.MODEL_CODE = 'NW-WS623' AND A.SERIAL_NO = '5421453'
UNION ALL
SELECT A.SERVICE_SHEET_NO,A.MODEL_CODE,A.SERIAL_NO,A.MOBILE_MODEL_CODE,A.MOBILE_SERIAL_NO FROM TEMP A
INNER JOIN TEMP B ON A.MODEL_CODE = B.MOBILE_MODEL_CODE AND A.SERIAL_NO = B.MOBILE_SERIAL_NO
WHERE A.MOBILE_MODEL_CODE IS NOT NULL AND A.MOBILE_SERIAL_NO IS NOT NULL
)
SELECT * FROM TEMP

[_小黑_]

--MOBILE_MODEL_CODE 与 MOBILE_SERIAL_NO 有值时 对应的 MODEL_CODE与SERIAL_NO 的数量为1，否则为0
--比如 MODEL_CODE = 'NW-WS623' 与 SERIAL_NO = '5421453' 对应的数量是3，
--因为 初始是MODEL_CODE = 'NW-WS623' 与 SERIAL_NO = '5421453' 经过了 3次换机
-- 而 MODEL_CODE = 'WI-1000X' 与 SERIAL_NO = '5383857' 只有1次换机

[Hello World,]

就是加一列Count?
如果是这样的话用子查询就可以了，子查询的条件大概是：
SERVICE_SHEET_NO>=当前记录的SERVICE_SHEET_NO，MOBILE_MODEL_CODE=当前记录MODEL_CODE

[_小黑_]

引用 2 楼 Hello World, 的回复:

就是加一列Count? 如果是这样的话用子查询就可以了，子查询的条件大概是： SERVICE_SHEET_NO>=当前记录的SERVICE_SHEET_NO，MOBILE_MODEL_CODE=当前记录MODEL_CODE

方便具体说一下吗？ 感谢

[Hello World,]

又发了一份？另外一个贴里有回复，思路就是用CET先查询出没有换过的，再依次查询换的顺序，累计加1即可
省略原始数据：

with    A AS (SELECT  *,
                    CASE WHEN MOBILE_SERIAL_NO IS NULL THEN 0 ELSE 1 END AS [Count]
            FROM    TEMP t1
            WHERE   NOT EXISTS (SELECT  1 FROM  TEMP t2 WHERE   t1.MOBILE_SERIAL_NO = t2.SERIAL_NO)
            UNION ALL
            SELECT  t3.*,t4.[Count]+1
            FROM    TEMP t3
                    INNER JOIN A t4 ON t3.MODEL_CODE = t4.MODEL_CODE 
					AND   t3.MOBILE_SERIAL_NO = t4.SERIAL_NO)
SELECT  * FROM  A ORDER BY A.MODEL_CODE,A.SERVICE_SHEET_NO,A.[Count] DESC