匹配数据，数据 [英] Matching data, datable

问题描述

我有这个数据表dt3，看起来像：

 ADminNo PaperNo 
 111411H 3 
 111411H 18 
 172828z 3 
 172828z 18 
 111380Y 93 
 111938S 10 

如何得到类似的东西：

 ConflictingPaper Numberofstudents AdminNo 
 3：8 2 111411H 
 172828z 

等等。

填充数据网格视图。

i尝试类似：

  Dim  curadminno  As   String  =  String  .Empty 
 curadminno = dr3（  AdminNo）。ToString 
  Dim  z，k  As  整数 
 对于 z =  0    dt3.Rows.Count 
 如果 dt3.Rows（z）.Item（ 0 ）= curadminno 然后 
 DataGridView3.Rows.Add（ String  .Format（  { 1}：{1}，_ 
 dr3（  PaperNo ）.ToString（）））

虽然错了。



感谢任何人都可以帮我解决这个问题！

谢谢！

解决方案

如果使用SQL Server那么你可以尝试类似的东西这：

  DECLARE   @ tmp  < span class =code-keyword> TABLE （ADminNo  VARCHAR （ 30 ），PaperNo  INT ）
 
  INSERT   INTO   @ tmp （AdminNo，PaperNo）
  SELECT  '  111411H'， 3  
  UNION   ALL   SELECT   '  111411H'， 18  
  UNION   ALL   SELECT  ' <温泉n class =code-string> 172828z'， 3  
  UNION   ALL   SELECT  '  172828z'， 18  
  UNION   ALL   SELECT  '  111380Y'， 93  
  UNION   ALL   SELECT  '  111938S'， 10  
 
  SELECT  t1.AdminNo，COUNT（t1。 AdminNo） AS  NumberOfStudents，STUFF（（ SELECT  ' ，' +  CONVERT （ VARCHAR （  5 ），[PaperNo]） AS  '  text（）' 
  FROM   @ tmp   AS  t2 
  WHERE  t2.AdminNo = t1.AdminNo 
  FOR  XML PATH（' '）） ， 1 ， 1 ，' '） AS  [ConflictingPaper] 
  FROM   @ tmp   AS  t1 
  GROUP   BY  AdminNo 
  HAVING  COUNT（AdminNo）> 1 

结果：

 AdminNo NumberOfStudents ConflictingPaper 
 111411H 2 3,18 
 172828z 2 3,18 

12345_abcde写道：

数据从访问数据库中检索并存储在datatable中。使用vb.net

所以...你可以使用以下查询：

  SELECT  t1.AdminNo，COUNT（t1.AdminNo） AS  NumberOfStudents 
  FROM   @ tmp   AS  t1 
  GROUP   BY  AdminNo 
  HAVING  COUNT（AdminNo ）> 1 

它将返回与上面相同的结果，但没有 ConflictingPaper 列。



如何从MS Access数据库中获取数据？请阅读：从.NET应用程序访问Microsoft Office数据 [ ^ ]



[/ EDIT]

试试这个：

您所需的数据将被存储到数据表dt ....

  Dim  dt 作为 新 DataTable 
 ' 在DataTable中创建四个类型列。 
 dt.Columns.Add（  ConflictingPaper， GetType （ String ））
 dt.Columns.Add（  Numberofstudents， GetType （ String ））
 dt.Columns.Add（  AdminNo， GetType （ String ））
 
  Dim  query1 =（来自_a  In  dt3 
 Group Convert.ToString（_a。字段（   Int32 ）（ < span class =code-string> PaperNo））
 By AdminNo = _a.Field（ of   String ）（  AdminNo）
进入组
 选择 dt.LoadDataRow（新 对象（）{字符串 .Join（ ：，Group.ToArray（）），Group.Count（），AdminNo}， False ）
）。ToList（）。Count（）
 

FYI进口额：

  Imports 系统
  Imports  System.Data 
  Imports  System.Math 
  Imports  System.Linq 

Hi, i have this datatable dt3 which looks like:

ADminNo     PaperNo
111411H     3
111411H     18
172828z     3
172828z     18
111380Y     93
111938S     10

How to i get something like:

ConflictingPaper     Numberofstudents    AdminNo
3 : 8                2                   111411H
                                         172828z

so on and so forth.
To populate in a datagridview.
i tried something like:

Dim curadminno As String = String.Empty
                        curadminno = dr3("AdminNo").ToString
                        Dim z, k As Integer
                        For z = 0 To dt3.Rows.Count
                            If dt3.Rows(z).Item(0) = curadminno Then
                                DataGridView3.Rows.Add(String.Format("{1}:{1}", _
                                                   dr3("PaperNo").ToString()))

its wrong though.

appreciate if anyone could help me out with this!
Thanks!

解决方案

If you use SQL Server, then you can try something like this:

DECLARE @tmp TABLE (ADminNo VARCHAR(30), PaperNo INT)

INSERT INTO @tmp (AdminNo, PaperNo)
SELECT '111411H', 3
UNION ALL SELECT '111411H', 18
UNION ALL SELECT '172828z', 3
UNION ALL SELECT '172828z', 18
UNION ALL SELECT '111380Y', 93
UNION ALL SELECT '111938S', 10

SELECT t1.AdminNo, COUNT(t1.AdminNo) AS NumberOfStudents, STUFF( (SELECT ', ' + CONVERT(VARCHAR(5),[PaperNo]) AS 'text()' 
                  FROM @tmp AS t2
                  WHERE t2.AdminNo = t1.AdminNo
                  FOR XML PATH('')), 1, 1, '') AS [ConflictingPaper]
FROM @tmp AS t1
GROUP BY AdminNo
HAVING COUNT(AdminNo)>1

Result:

AdminNo    NumberOfStudents     ConflictingPaper
111411H    2                    3, 18
172828z    2                    3, 18

[EDIT]

12345_abcde wrote:

data is retrieved from access database and stored in datatable. using with vb.net

So... You can use below query:

SELECT t1.AdminNo, COUNT(t1.AdminNo) AS NumberOfStudents
FROM @tmp AS t1
GROUP BY AdminNo
HAVING COUNT(AdminNo)>1

It will return the same result as above, but without the ConflictingPaper column.

How to fetch data from MS Access database? Please, read this: Accessing Microsoft Office Data from .NET Applications[^]

[/EDIT]

Try this:
Your required data will be stored into the datatable dt....

Dim dt As New DataTable
  ' Create four typed columns in the DataTable.
  dt.Columns.Add("ConflictingPaper", GetType(String))
  dt.Columns.Add("Numberofstudents", GetType(String))
  dt.Columns.Add("AdminNo", GetType(String))

  Dim query1 = (From _a In dt3
          Group Convert.ToString(_a.Field(Of Int32)("PaperNo"))
          By AdminNo = _a.Field(Of String)("AdminNo")
          Into Group
          Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)
          ).ToList().Count()

FYI The Imports:

Imports System
Imports System.Data
Imports System.Math
Imports System.Linq

这篇关于匹配数据，数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！