假设有表数据如下:WLRN1 WLRN2 WLRN3 WLRN4 WLRN5 WLRN6
----------- ----------- ----------- ----------- ----------- -----------
0 9 12 30 29 11
38 89 75 27 33 28
2 50 26 59 47 3
12 89 79 54 51 6
99 88 57 29 6 45
19 0 49 49 15 0
80 57 62 10 30 33
35 36 12 44 52 28
11 14 52 45 5 27
1 19 78 32 39 24
55 63 11 37 3 2
35 18 50 23 27 11
58 51 62 15 20 2
84 87 4 10 26 49
42 72 55 59 11 18
69 0 7 57 31 27
94 1 24 44 19 37
64 17 19 22 25 37
73 7 30 43 3 26
39 85 39 18 51 41
56 61 32 57 30 15
95 68 41 8 13 18
61 40 42 53 37 4
92 64 16 32 0 41
6 72 30 32 55 26
51 75 39 64 26 40
31 78 40 58 10 7
49 86 39 16 44 46
96 17 28 34 28 17
95 12 52 21 50 34
61 2 67 13 16 9
84 63 49 58 41 38
84 79 18 12 35 22
63 12 33 67 27 44
73 75 42 23 27 33
93 58 3 32 44 1
49 66 53 55 34 15
25 38 9 16 14 28
89 37 18 57 17 12
85 7 39 15 5 34
22 16 57 59 24 21
39 68 14 61 34 12
41 37 8 15 6 45
23 73 51 31 47 0
65 15 45 34 37 9
25 36 30 58 8 41 如何统计表中所有数字的个数以如下形式返回数字 个数
----------- -----------
0 xx
1 xx
2 xx
3 xx
4 xx
5 xx
... ...
----------- ----------- ----------- ----------- ----------- -----------
0 9 12 30 29 11
38 89 75 27 33 28
2 50 26 59 47 3
12 89 79 54 51 6
99 88 57 29 6 45
19 0 49 49 15 0
80 57 62 10 30 33
35 36 12 44 52 28
11 14 52 45 5 27
1 19 78 32 39 24
55 63 11 37 3 2
35 18 50 23 27 11
58 51 62 15 20 2
84 87 4 10 26 49
42 72 55 59 11 18
69 0 7 57 31 27
94 1 24 44 19 37
64 17 19 22 25 37
73 7 30 43 3 26
39 85 39 18 51 41
56 61 32 57 30 15
95 68 41 8 13 18
61 40 42 53 37 4
92 64 16 32 0 41
6 72 30 32 55 26
51 75 39 64 26 40
31 78 40 58 10 7
49 86 39 16 44 46
96 17 28 34 28 17
95 12 52 21 50 34
61 2 67 13 16 9
84 63 49 58 41 38
84 79 18 12 35 22
63 12 33 67 27 44
73 75 42 23 27 33
93 58 3 32 44 1
49 66 53 55 34 15
25 38 9 16 14 28
89 37 18 57 17 12
85 7 39 15 5 34
22 16 57 59 24 21
39 68 14 61 34 12
41 37 8 15 6 45
23 73 51 31 47 0
65 15 45 34 37 9
25 36 30 58 8 41 如何统计表中所有数字的个数以如下形式返回数字 个数
----------- -----------
0 xx
1 xx
2 xx
3 xx
4 xx
5 xx
... ...
解决方案 »
- SQL2005 EXPRESS不能通过第三方程序进行远程访问(通过SQL SERVER MANAGERMENT 可以)
- 多表运算的存储过程
- [一个怪异的问题]关于BCP导出导入的怪问题,搜索了帖子不能解决,请各位大虾进来看看
- 過程中的top 1裡面,變量的問題。
- 大家都来看看,这个SQL语句怎么写
- T-sql问题
- 高分求助一个简单问题~~~~!
- 菜鸟问题一问?(送分题)Online,马上结贴
- ********一个很菜的问题(在线等待)**********
- 我的sql企业管理器打不开了~~!是mmc问题?
- sqlserver2008 的用户能对应到不同的视图么?急等回复~~~~~~~~
- 我的数据库丢失了,
from
(
select WLRN1 as WLRN from tb
union all
select WLRN2 from tb
union all
select WLRN3 from tb
union all
select WLRN4 from tb
union all
select WLRN5 from tb
union all
select WLRN6 from tb
) t
group by WLRN
from
(
select WLRN1 as WLRN from tb
union all
select WLRN2 from tb
union all
select WLRN3 from tb
union all
select WLRN4 from tb
union all
select WLRN5 from tb
union all
select WLRN6 from tb
) t
group by WLRN
不是应该这样吗?
select WLRN AS 数字,count(1) as 个数
from
(
select WLRN1 as WLRN from tb
union all
select WLRN2 as WLRN tb
union all
select WLRN3 as WLRN tb
union all
select WLRN4 as WLRN tb
union all
select WLRN5 as WLRN tb
union all
select WLRN6 as WLRN tb
) t
group by WLRN
select WLRN AS 数字,count(1) as 个数
from
(
select WLRN1 as WLRN from tb
union all
select WLRN2 as WLRN from tb
union all
select WLRN3 as WLRN from tb
union all
select WLRN4 as WLRN from tb
union all
select WLRN5 as WLRN from tb
union all
select WLRN6 as WLRN from tb
) t
group by WLRN
from (
select wlrn1 as no from TB
union all
select wlrn2 as no from TB
union all
select wlrn3 as no from TB
union all
select wlrn4 as no from TB
union all
select wlrn5 as no from TB
union all
select wlrn6 as no from TB)T
group by no
/*
no 次数
----------- -----------
0 6
1 3
2 4
3 4
4 2
5 2
6 4
7 4
8 3
9 4
10 3
11 5
12 8
13 2
14 3
15 7
16 5
17 4
18 6
19 4
20 1
21 2
22 3
23 3
24 3
25 3
26 5
27 6
28 5
29 2
30 6
31 3
32 5
33 4
34 6
35 3
36 2
37 7
38 3
39 7
40 3
41 6
42 3
43 1
44 5
45 4
46 1
47 2
49 6
50 3
51 5
52 3
53 2
54 1
55 4
56 1
57 6
58 5
59 3
61 4
62 2
63 3
64 3
65 1
66 1
67 2
68 2
69 1
72 2
73 3
75 3
78 2
79 2
80 1
84 3
85 2
86 1
87 1
88 1
89 3
92 1
93 1
94 1
95 2
96 1
99 1(86 行受影响)
*/