统计表中相同值的个数(不是字段，是全表)
假设有表数据如下:
- SQL code
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
如何统计表中所有数字的个数以如下形式返回
- SQL code
数字 个数----------- ----------- 0 xx 1 xx 2 xx 3 xx 4 xx 5 xx ... ...
------解决方案--------------------
- SQL code
select WLRN AS 数字,count(1) as 个数from(select WLRN1 as WLRN from tbunion allselect WLRN2 from tbunion allselect WLRN3 from tb union allselect WLRN4 from tb union allselect WLRN5 from tb union allselect WLRN6 from tb ) tgroup by WLRN
[解决办法]
[引用:]
- SQL code
select WLRN AS 数字,count(1) as 个数from(select WLRN1 as WLRN from tbunion allselect WLRN2 from tbunion allselect WLRN3 from tb union allselect WLRN4 from tb union allselect WLRN5 from tb union allselect WLRN6 from tb ) tgroup by WLRN
[解决办法]
- SQL code
select no,COUNT(1) as 次数from (select wlrn1 as no from TBunion allselect wlrn2 as no from TBunion allselect wlrn3 as no from TBunion allselect wlrn4 as no from TBunion allselect wlrn5 as no from TBunion allselect wlrn6 as no from TB)Tgroup by no/*no 次数----------- -----------0 61 32 43 44 25 26 47 48 39 410 311 512 813 214 315 716 517 418 619 420 121 222 323 324 325 326 527 628 529 230 631 332 533 434 635 336 237 738 339 740 341 642 343 144 545 446 147 249 650 351 552 353 254 155 456 157 658 559 361 462 263 364 365 166 167 268 269 172 273 375 378 279 280 184 385 286 187 188 189 392 193 194 195 296 199 1(86 行受影响)*/