从多个表中选择名称,并使用相同的 ID 进行排序
本文关键字:ID 排序 选择 | 更新日期: 2023-09-27 18:36:26
>我有一个问题。我有 2 张表,看起来像这样:
表1:国家
Name: Germany
ID: 1
Name: France
ID: 2
表2:城市
Name: Frankfurt
ID: 1
Name: Paris
ID: 2
我想写一个选择语句,在其中比较 id 并对具有相同 id 的正确国家/地区的城市进行排序。输出应采用 json 格式。
到目前为止,我的代码如下所示:
public class StadtHelper
{
public class STADT
{
public string StadtName { get; set; }
public string RegionID { get; set; }
}
internal static List<STADT> Stadt()
{
List<STADT> stadtObject = new List<STADT>();
using (SqlConnection con = new SqlConnection(@"Data Source=Localhost'SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
using (SqlCommand cmd = new SqlCommand(@"SELECT NAME, REGION_ID FROM STADT",con))
{
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (rdr["NAME"] != DBNull.Value && rdr["REGION_ID"] != DBNull.Value)
{
stadtObject.Add(new STADT()
{
StadtName = rdr["NAME"].ToString(),
RegionID = rdr["REGION_ID"].ToString()
});
}
}
}
}
return stadtObject;
}
}
提前致谢
我编辑了我的代码:
public class StadtHelper
{
public class STADT
{
public string StadtName { get; set; }
public string RegionName { get; set; }
}
internal static List<STADT> Stadt()
{
List<STADT> stadtObject = new List<STADT>();
using (SqlConnection con = new SqlConnection(@"Data Source=Localhost'SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
using (SqlCommand cmd = new SqlCommand(@"SELECT REGION.NAME, STADT.NAME FROM REGION, STADT WHERE REGION.ID = STADT.REGION_ID ORDER BY REGION.NAME" , con))
{
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (rdr["REGION.NAME"] != DBNull.Value && rdr["STADT.NAME"] != DBNull.Value)
{
stadtObject.Add(new STADT()
{
RegionName = rdr["REGION.NAME"].ToString(),
StadtName = rdr["STADT"].ToString()
});
}
}
}
}
return stadtObject;
}
}
仍然有一个错误,我的网络服务给了我一个解释:
System.IndexOutOfRangeException: REGION.NAME
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at StadtHelper.Stadt() in C:'Users'Yeah'Documents'Visual Studio 2010'Projects'WebService1'WebService1'StadtHelper.cs:line 31
at WebService1.Service1.Stadt() in C:'Users'Yeah'Documents'Visual Studio 2010'Projects'WebService1'WebService1'Service1.asmx.cs:line 77
给定您的表,TSQL 如下所示:
select ci.Name, co.Name
from Cities ci
inner join Country co on co.ID = ci.ID
order by ci.Name, co.Name