使用查询结果获取列中重复值的计数 [英] Get the count of the duplicate values in a column with the result of a query

问题描述

我想获得查询结果中一列的结果中重复了多少个值的计数.

I want to get the count how many values are repeating in the result in a column in result of a query.

我从复杂查询中获得的结果集是-

The result set I am getting from a complex query is -

svn rvn eng     count(*)
1   1   Boy            1
2   1   Teacher        1
3   1   Chair          1
3   2   Chairwoman      1
3   3   Chairperson     1
4   1   Without         1
4   2   Without fail    1
5   1   Anyone          1
5   2   Anyone else     1
6   1   Permission      1

我只想获取第四列中SVN列中重复值的数量.

I just want to get the number of duplicate values in SVN coloumn in fourth coloumn.

即

svn rvn eng     count(*)
1   1   Boy            1
2   1   Teacher        1

3   1   Chair          3
3   2   Chairwoman      3
3   3   Chairperson     3

4   1   Without         2
4   2   Without fail    2

5   1   Anyone          2
5   2   Anyone else     2
6   1   Permission      1

请帮我解决这个问题，也请告诉我这会对查询效率产生什么影响?

Please Help me out on this, Also please tell me what effect this can cause on the efficiency on the query??

* 注意-* 我想要对结果集中选取的值进行计数. 表中还有svn 1,2,4,6的更多条目.但只计算已选择的数量.

*Note - * I want the count of the values that are picked up in the result set. there are more entries with svn 1,2,4,6 in the table. but count only how many has been selected.

先谢谢您了:)

EDIT1

这是我当前的查询:-

SELECT `svn` , `rvn` ,`eng` , count(*) FROM 
        (SELECT `svn`, `rvn`, `eng`, `hin` 
              FROM `table1` 
              WHERE `SN` = @sn
            UNION DISTINCT
            SELECT `table1_refer`.`sn_svn` AS 'svn',  
                           `table1`.`rvn`, `table1`.`eng` ,  
                          `vocab_rel`.`hin`
            FROM  `table1` 
            JOIN  `table1_refer` 
            WHERE  `table1_refer`.`rSN` =  `table1`.`SN` 
            AND  `table1_refer`.`svn` =  `table1`.`svn` 
            AND `table1_refer`.`SN` = @sn
        ) AS SUBQUERY
        GROUP BY `svn`,`rvn`
        ORDER BY `svn`, `rvn`

推荐答案

您可以在当前的复杂查询中使用相关的子查询来做到这一点:

You can use a correlated subquery inside your current complex query to do this like so:

SELECT
  svn, 
  rvn,
  eng,
  (SELECT count(t2.svn)
   FROM Tablename t2
   WHERE t2.svn = t1.svn
  ) AS Count
FROM Tablename t1
....

SQL小提琴演示

这将为您提供:

SQL Fiddle Demo

This will give you:

| SVN | RVN |         ENG | COUNT |
-----------------------------------
|   1 |   1 |         Boy |     1 |
|   2 |   1 |     Teacher |     1 |
|   3 |   1 |       Chair |     3 |
|   3 |   2 |  Chairwoman |     3 |
|   3 |   3 | Chairperson |     3 |
|   4 |   1 |     Without |     2 |
|   4 |   2 | Withoutfail |     2 |
|   5 |   1 |      Anyone |     2 |
|   5 |   2 |  Anyoneelse |     2 |
|   6 |   1 |  Permission |     1 |

这篇关于使用查询结果获取列中重复值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！