如何识别表中的重复项并在列表视图中显示它们 [英] how to identify duplicates in a table and display them in a list view

问题描述

我有一个带有一个表'学生'的sql server数据库。

i have a sql server database with one table 'Students'.

student_id     student_fullname       student_subject
1              aaa                     java
2              bbb                     c
3              ccc                     java
4              ddd                     javascript
5              eee                     html
6              bbb                     c++
7              ddd                     c++  

我需要vb.net代码来遍历表格，并在列表视图控件中识别并显示那些具有多个条目的学生。例如：在表格中，我们有学生'bbb'和'ddd'，其中包含重复的条目。因此列表视图必须显示以下数据。

I need vb.net code to loop through the table and identify and display those students with more than one entry in a listview control. For eg: in the table, we have students 'bbb' and 'ddd' with duplicate entries. Therefore the list view must display the following data.

student_id      student_fullname         student_subject
2               bbb                       c
6               bbb                       c++
4               ddd                       javascript
7               ddd                       c++

推荐答案

获取SQL来执行为你举重。下面显示的表单的T-SQL将为您提供所需的数据，并且您的VB在检索到的数据表中的数据行上简化为每个数据。

Get SQL to do the heavy lifting for you. T-SQL of the form shown below will give you the data as you want it and your VB reduces to a simple for-each over the datarows in your retrieved datatable.

select
  students.id, 
  students.fullname, 
  students.subject
from students
inner join (select fullname from students
            group by fullname having count(subject) > 1)
      as multiple on multiple.fullname = students.fullname
order by fullname, subject asc

测试它：

Test it:

DECLARE @Students TABLE (student_id INT IDENTITY(1,1), student_fullname NVARCHAR(30), student_subject NVARCHAR(30))
INSERT INTO @Students (student_fullname, student_subject)
VALUES('aaa', 'java'), ('bbb', 'c'),
('ccc', 'java'), ('ddd', 'javascript'),
('eee', 'html'), ('bbb', 'c++'), ('ddd', 'c++')
 
SELECT t1.student_id, t1.student_fullname, t1.student_subject 
FROM @Students AS t1 INNER JOIN (
	SELECT student_fullname
	FROM @Students
	GROUP BY student_fullname
	HAVING COUNT(student_fullname)>1
	) AS t2 ON t1.student_fullname = t2.student_fullname

结果：

Result:

student_id	student_fullname	student_subject
2			bbb					c
6			bbb					c++
4			ddd					javascript
7			ddd					c++

看看这里：如何使用任何ADO.Net数据集填充ListView [ ^ ]

SELECT * FROM Students s  join (select  student_fullname   from Students GROUP BY student_fullname HAVING count(*) > 1) a on s.student_fullname = a.student_fullname

这篇关于如何识别表中的重复项并在列表视图中显示它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！