如何计算列并在另一列中显示其计数 [英] how to count the column and show its count in another column

问题描述

Item_groupDetail_PK ItemGroup_PK Sortorder

46737 NULL NULL

49292 NULL NULL

40550 3 NULL

38036 4 NULL

15125 4 NULL

58310 4 NULL

61653 5 NULL

13250 5 NULL

10821 5 NULL

53087 6 NULL

25301 6 NULL

36975 7 NULL

35759 8 NULL

55940 8 NULL

8205 8 NULL

32834 8 NULL

26404 8 NULL

29371 8 NULL

49478 8 NULL

19169 8 NULL

2 0911 9 NULL

21167 9 NULL

21235 9 NULL





你好所有我有一个如上所示的小桌子我想看到我的专栏中的任何条目，并保持在排序顺序我将如何做到这一点？例如，在上面的表中为3排序顺序它显示1然后为4它应该首先有1然后2,3,4在排序顺序。我希望sortorder列具有第二列中显示的数据计数。任何人都可以提前感谢

Item_groupDetail_PK ItemGroup_PK Sortorder
46737 NULL NULL
49292 NULL NULL
40550 3 NULL
38036 4 NULL
15125 4 NULL
58310 4 NULL
61653 5 NULL
13250 5 NULL
10821 5 NULL
53087 6 NULL
25301 6 NULL
36975 7 NULL
35759 8 NULL
55940 8 NULL
8205 8 NULL
32834 8 NULL
26404 8 NULL
29371 8 NULL
49478 8 NULL
19169 8 NULL
20911 9 NULL
21167 9 NULL
21235 9 NULL


Hello All I have a small table as shown above i want to just see the ever entry in my column and keep it count in sortorder how wil i do it?? for example in above table for 3 in sortorder it shouls show 1 then for 4 it should first have 1 then 2,3,4 in sortorder. i want the sortorder column to have the count of data which is shown in 2nd column . can any one help thanks in advance

推荐答案

尝试

Try

SELECT ROW_NUMBER() OVER (PARTITION BY ItemGroup_PK ORDER BY Item_groupDetail_PK) AS SortOrder, * FROM YourTableName

编辑

更新排序器的示例方法

Edit
Sample approach to update sortorder

CREATE TABLE #Sample
(
Item_groupDetail_PK INT,
ItemGroup_PK INT,
Sortorder INT
)

INSERT INTO #Sample
SELECT 46737, NULL, NULL UNION ALL
SELECT 49292, NULL, NULL UNION ALL
SELECT 40550, 3, NULL UNION ALL
SELECT 38036, 4, NULL UNION ALL
SELECT 15125, 4, NULL UNION ALL
SELECT 58310, 4, NULL UNION ALL
SELECT 61653, 5, NULL UNION ALL
SELECT 13250, 5, NULL UNION ALL
SELECT 10821, 5, NULL UNION ALL
SELECT 53087, 6, NULL UNION ALL
SELECT 25301, 6, NULL UNION ALL
SELECT 36975, 7, NULL UNION ALL
SELECT 35759, 8, NULL UNION ALL
SELECT 55940, 8, NULL UNION ALL
SELECT 8205, 8, NULL UNION ALL
SELECT 32834, 8, NULL UNION ALL
SELECT 26404, 8, NULL UNION ALL
SELECT 29371, 8, NULL UNION ALL
SELECT 49478, 8, NULL UNION ALL
SELECT 19169, 8, NULL UNION ALL
SELECT 20911, 9, NULL UNION ALL
SELECT 21167, 9, NULL UNION ALL
SELECT 21235, 9, NULL

--Before update
SELECT * FROM #Sample

UPDATE #Sample
SET Sortorder = T.NewSortOrder
FROM
#Sample S
INNER JOIN
(
	SELECT ROW_NUMBER() OVER (PARTITION BY ItemGroup_PK ORDER BY Item_groupDetail_PK) AS NewSortOrder, * FROM #Sample
) T ON S.Item_groupDetail_PK = T.Item_groupDetail_PK

--After update
SELECT * FROM #Sample


DROP TABLE #Sample

这篇关于如何计算列并在另一列中显示其计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！