SELECT *
INTO #tb
FROM
(
SELECT '1' AS 数据,1 AS OrderNO UNION ALL
SELECT '1' AS 数据,2 AS OrderNO UNION ALL
SELECT '1' AS 数据,3 AS OrderNO UNION ALL
SELECT '2' AS 数据,4 AS OrderNO UNION ALL
SELECT '1' AS 数据,5 AS OrderNO UNION ALL
SELECT '1' AS 数据,6 AS OrderNO
) AS TSELECT [数据],CAST(COUNT(1) OVER(PARTITION BY [数据]) AS VARCHAR(10)) AS [累计]
FROM #tb
ORDER BY OrderNoDROP TABLE #tb数据 累计
---- ----------
1 5
1 5
1 5
2 1
1 5
1 5希望能达成效果
数据 累计
---- ----------
1 3
1 3
1 3
2 1
1 2
1 2SQL累计
INTO #tb
FROM
(
SELECT '1' AS 数据,1 AS OrderNO UNION ALL
SELECT '1' AS 数据,2 AS OrderNO UNION ALL
SELECT '1' AS 数据,3 AS OrderNO UNION ALL
SELECT '2' AS 数据,4 AS OrderNO UNION ALL
SELECT '1' AS 数据,5 AS OrderNO UNION ALL
SELECT '1' AS 数据,6 AS OrderNO
) AS TSELECT [数据],CAST(COUNT(1) OVER(PARTITION BY [数据]) AS VARCHAR(10)) AS [累计]
FROM #tb
ORDER BY OrderNoDROP TABLE #tb数据 累计
---- ----------
1 5
1 5
1 5
2 1
1 5
1 5希望能达成效果
数据 累计
---- ----------
1 3
1 3
1 3
2 1
1 2
1 2SQL累计
FROM #tb where OrderNO<=(select OrderNO from #tb where [数据]<>1)
union all
SELECT [数据],CAST(COUNT(1) OVER(PARTITION BY [数据]) AS VARCHAR(10)) AS [累计]
FROM #tb where OrderNO>(select OrderNO from #tb where [数据]<>1)
INTO #tb
FROM
(
SELECT '1' AS 数据,1 AS OrderNO UNION ALL
SELECT '1' AS 数据,2 AS OrderNO UNION ALL
SELECT '1' AS 数据,3 AS OrderNO UNION ALL
SELECT '2' AS 数据,4 AS OrderNO UNION ALL
SELECT '1' AS 数据,5 AS OrderNO UNION ALL
SELECT '1' AS 数据,6 AS OrderNO UNION ALL
SELECT '2' AS 数据,4 AS OrderNO UNION ALL
SELECT '3' AS 数据,5 AS OrderNO UNION ALL
SELECT '2' AS 数据,6 AS OrderNO UNION ALL
SELECT '3' AS 数据,4 AS OrderNO UNION ALL
SELECT '3' AS 数据,5 AS OrderNO UNION ALL
SELECT '1' AS 数据,6 AS OrderNO
) AS TSELECT [数据],CAST(COUNT(1) OVER(PARTITION BY [数据]) AS VARCHAR(10)) AS [累计]
FROM #tb
ORDER BY OrderNoDROP TABLE #tb
SELECT *,IDENTITY(int,1,1) id
INTO #tb
FROM
(
SELECT '1' AS 数据,1 AS OrderNO UNION ALL
SELECT '1' AS 数据,2 AS OrderNO UNION ALL
SELECT '1' AS 数据,3 AS OrderNO UNION ALL
SELECT '2' AS 数据,4 AS OrderNO UNION ALL
SELECT '1' AS 数据,5 AS OrderNO UNION ALL
SELECT '1' AS 数据,6 AS OrderNO UNION ALL
SELECT '2' AS 数据,4 AS OrderNO UNION ALL
SELECT '3' AS 数据,5 AS OrderNO UNION ALL
SELECT '2' AS 数据,6 AS OrderNO UNION ALL
SELECT '3' AS 数据,4 AS OrderNO UNION ALL
SELECT '3' AS 数据,5 AS OrderNO UNION ALL
SELECT '1' AS 数据,6 AS OrderNO
) AS T
;with cte as(
SELECT [数据],orderno,ROW_NUMBER() OVER(ORDER BY id)rn FROM #tb
)
,cte1 as(
select a.*,b.rn as rnb,b.数据 as data from cte a left join cte b on a.数据=b.数据 and a.rn=b.rn+1
)
,cte2 as(
select *,ROW_NUMBER() OVER(ORDER BY rn)row from cte1 where rnb is null
union all
select b.数据,b.OrderNO,b.rn,b.rnb,b.data,a.row from cte2 a inner join cte1 b on a.数据=b.data and a.rn=b.rnb
)select 数据,row,COUNT(1)num from cte2 group by 数据,row order by row/*
数据 row num
1 1 3
2 2 1
1 3 2
2 4 1
3 5 1
2 6 1
3 7 2
1 8 1
*/
SELECT
ROW_NUMBER() OVER (ORDER BY l.OrderNo,s.OrderNo) AS 序号,
dbo.Fn_EmptyToDash(s.PileMark)AS 现场号,
CAST(COUNT(1) OVER(PARTITION BY s.PileMark) AS VARCHAR(10)) AS 计数,
dbo.Fn_EmptyToDash(s.Distance) AS 横距
FROM dbo.D_ConsignBaseXianChang2_LuJYSD AS l INNER JOIN
dbo.D_ConsignBaseXianChang2_LuJYSD_Sub AS s ON s.ConsignID = l.ConsignID
WHERE l.ConsignID ='3D723FDE-F4EB-4A93-B59B-455FFFA93EF2'
ORDER BY l.OrderNo,s.OrderNo效果是
/*
序号 现场号 计数 横距
1 1232 1 3
2 3 1 2
3 22 3 22
4 22 3 1
5 22 3 11
*/
按顺序放入游标中,abc初始值赋予1,当 数据值发生变化是 abc+1,这样遍历一次表,就完成了下面的表,那样在做一个统计就结束了,没看出效率低在哪,关键这个一个循环就结束真心不清楚多在哪?SELECT *
INTO #tb
FROM
(
SELECT '1' AS 数据,1 AS abc UNION ALL
SELECT '1' AS 数据,1 AS abc UNION ALL
SELECT '1' AS 数据,1 AS abc UNION ALL
SELECT '2' AS 数据,2 AS abc UNION ALL
SELECT '1' AS 数据,3 AS abc UNION ALL
SELECT '1' AS 数据,3 AS abc UNION ALL
SELECT '2' AS 数据,4 AS abc UNION ALL
SELECT '3' AS 数据,5 AS abc UNION ALL
SELECT '2' AS 数据,6 AS abc UNION ALL
SELECT '3' AS 数据,7 AS abc UNION ALL
SELECT '3' AS 数据,7 AS abc UNION ALL
SELECT '1' AS 数据,8 AS abc
) AS T SELECT [数据],CAST(COUNT(1) OVER(PARTITION BY [数据],abc) AS VARCHAR(10)) AS [累计]
FROM #tb
ORDER BY abc DROP TABLE #tb