澄清两个相同名称的列

本文关键字：两个 | 更新日期: 2023-09-27 18:15:00

我有这样的查询:

SELECT CableNumber, CblType, ApprxmtLngth, ME.EquipmentNumber, ME2.EquipmentNumber, CblStatus, InstallMthd, S.PrdtCd FROM CableId C
 INNER JOIN MajorEquipment ME ON C.FromLoc = ME.MEId
 INNER JOIN MajorEquipment ME2 ON C.ToLoc = ME2.MEId
 INNER JOIN SupplierInfo S ON C.SupplierId = S.SupplierId WHERE
 (@CblType IS NULL OR CblType LIKE @CblType)
 AND (@CblStatus IS NULL OR CblStatus LIKE @CblStatus)
 AND (@FromLoc IS NULL OR ME.EquipmentNumber LIKE @FromLoc)
 AND (@ToLoc IS NULL OR ME2.EquipmentNumber LIKE @ToLoc);

当我将结果传递给模型时，我如何定义哪个EquipmentNumber进入哪个变量?

while (mySqlReader.Read())
{
    CableID_Controller.CList.Add(new CableID_Model
    {
        CableNumber = Convert.ToString(mySqlReader["CableNumber"]),
        CableType = Convert.ToString(mySqlReader["CblType"]),
        SupplierPart = Convert.ToString(mySqlReader["PrdtCd"]),
        CableLength = Convert.ToInt32(mySqlReader["ApprxmtLngth"]),
        InstallMethod = Convert.ToString(mySqlReader["InstallMthd"]),
        Origin = Convert.ToString(mySqlReader["ME.EquipmentNumber"]),
        Destination = Convert.ToString(mySqlReader["ME2.EquipmentNumber"]),
        Status = Convert.ToString(mySqlReader["CblStatus"])
     });
}

因为ME.EquipmentNumber和ME2.EquipmentNumber不是标题的实际名称

澄清两个相同名称的列

描述

只需在查询中给出不同的列别名(EquipmentNumber1和EquipmentNumber2)，并使用as操作符进行编码。您还必须删除代码中的ME1和ME2。

示例

SELECT CableNumber, CblType, ApprxmtLngth, ME.EquipmentNumber as EquipmentNumber1, ME2.EquipmentNumber as EquipmentNumber2, CblStatus, InstallMthd, S.PrdtCd FROM CableId C
 INNER JOIN MajorEquipment ME ON C.FromLoc = ME.MEId
 INNER JOIN MajorEquipment ME2 ON C.ToLoc = ME2.MEId
 INNER JOIN SupplierInfo S ON C.SupplierId = S.SupplierId WHERE
 (@CblType IS NULL OR CblType LIKE @CblType)
 AND (@CblStatus IS NULL OR CblStatus LIKE @CblStatus)
 AND (@FromLoc IS NULL OR ME.EquipmentNumber LIKE @FromLoc)
 AND (@ToLoc IS NULL OR ME2.EquipmentNumber LIKE @ToLoc);
while (mySqlReader.Read())
{
    CableID_Controller.CList.Add(new CableID_Model
    {
        CableNumber = Convert.ToString(mySqlReader["CableNumber"]),
        CableType = Convert.ToString(mySqlReader["CblType"]),
        SupplierPart = Convert.ToString(mySqlReader["PrdtCd"]),
        CableLength = Convert.ToInt32(mySqlReader["ApprxmtLngth"]),
        InstallMethod = Convert.ToString(mySqlReader["InstallMthd"]),
        Origin = Convert.ToString(mySqlReader["EquipmentNumber1"]),
        Destination = Convert.ToString(mySqlReader["EquipmentNumber2"]),
        Status = Convert.ToString(mySqlReader["CblStatus"])
     });
}

的更多信息
    MySQL - ALIAS

可以不给列不同的别名吗?

SQL别名用于临时重命名表或列标题。

之类的
SELECT 
 CableNumber, 
 CblType, 
 ApprxmtLngth, 
 ME.EquipmentNumber as MEEquipmentNumber, 
 ME2.EquipmentNumber as ME2EquipmentNumber,  
 CblStatus, 
 InstallMthd, 
 S.PrdtCd 
...

然后在c#代码中使用这些别名。就像

while (mySqlReader.Read())
{
    CableID_Controller.CList.Add(new CableID_Model
    {
        CableNumber = Convert.ToString(mySqlReader["CableNumber"]),
        CableType = Convert.ToString(mySqlReader["CblType"]),
        SupplierPart = Convert.ToString(mySqlReader["PrdtCd"]),
        CableLength = Convert.ToInt32(mySqlReader["ApprxmtLngth"]),
        InstallMethod = Convert.ToString(mySqlReader["InstallMthd"]),
        Origin = Convert.ToString(mySqlReader["MEEquipmentNumber"]),
        Destination = Convert.ToString(mySqlReader["ME2EquipmentNumber"]),
        Status = Convert.ToString(mySqlReader["CblStatus"])
     });
}

通过向返回列添加别名来修改查询:

SELECT CableNumber,
       CblType,
       ApprxmtLngth,
       ME.EquipmentNumber AS EquipmentNumber1,
       ME2.EquipmentNumber AS EquipmentNumber2,
       CblStatus,
       InstallMthd, [...]

您可以分别参考EquipmentNumber1和EquipmentNumber2。

相关文章：