C#中 join使用

winform的join语句使用

     public DataTable GetPostStationByLine(string line)
        {  
            string sql = string.Format("SELECT A.STATION_TYPE,B.STATION_TYPE_DESC FROM IMES.M_POST_LINE_STATION A left join IMES.M_STATION_TYPE B " +
                "ON B.STATION_TYPE = A.STATION_TYPE  WHERE A.ENABLED = 'Y' AND A.LINE ='{0}'", line);
            return utility.Query(sql);
        }
        //取两个表值的另一种写法
     public ExecuteResult ShowValues(bool Filter,string sFieldName,string sFieldText,int Enable)
        {
            try
            {
                exeRes = new ExecuteResult();
                //string sqlStr = @" select  a.*  from imes.m_line a  where  1=1 ";
                string sqlStr = @" select  a.*,b.SHOP  from imes.m_line a  left join imes.m_shop b on b.ID=a.SHOP_ID  where  1=1 ";

                if (Filter)
                {
                    sqlStr = sqlStr + " and " + sFieldName + " like '%"+ sFieldText + "%'";
                }

                if (Enable == 0)
                {
                    sqlStr = sqlStr + " and a.enabled = 'Y' ";
                }
                else if (Enable == 1)
                {
                    sqlStr = sqlStr + " and a.enabled = 'N' ";
                }
                sqlStr = sqlStr + " order by  a.update_time desc ";
                object[] para ;

                if (Filter)
                {
                    exeRes.Anything = utility.Query(sqlStr);
                }
                else
                {
                    exeRes.Anything = utility.Query(sqlStr);

                }

                exeRes.Status = true;
            }
            catch (Exception ex)
            {
                exeRes.Message = "Error:" + ex.Message;
                exeRes.Status = false;
            }
            return exeRes;
        }

C#MVC中的使用

var getList =(from x in db.EMS_R_CALL.Where(x=>x.PLANT_ID==plantId && x.STATUS==1 && GrpType.Contains(x.DEFECT_TYPE))
                          join d in db.EMS_R_CALL_DEFECT.Where(y=>y.BEGIN_REPAIR_NO==null) on x.CALL_REPAIR_NO equals d.CALL_REPAIR_NO into dd
                          from T_D in dd.DefaultIfEmpty()
                          join p in db.EMS_SYS_PARAM_VALUE.Where(y=>y.DELETEFLAG ==0 && y.PARAM_NAME == "DEFECT_TYPE") on x.DEFECT_TYPE equals p.PARAM_VALUE into pp
                          from T_P in pp.DefaultIfEmpty()
                          select new CallRepair
                          {
                              Id = (int)x.ID,
                              Call_Repair_No = x.CALL_REPAIR_NO,
                              Repair_Type = x.REPAIR_TYPE,
                              Call_Location = x.LINE.LINE1,
                              Line_Id = x.LINE_ID,
                              Line_Name = x.LINE.CODE,
                              Call_ByN = db.ACCOUNT.Where(t => t.ID == x.CALL_BY).Select(t => t.NAME).FirstOrDefault(),
                              Call_Time = x.CALL_TIME,
                              MachineId =T_D.MACHINE_ID,
                              MESCode = T_D.MACHINE.ITEM_NO,
                              MachineNo = T_D.MACHINE.CUSTODYNO,
                              Machine_Name=T_D.MACHINE.DEVICENAME,
                              Defect_Type_Name = T_D.TROUBLES.NAME,
                              Defect_Desc_Plus = T_D.DEFECT_DESC_PLUS,
                              Defect_Type = T_P.PARAM_DESC,
                              DefaultGrpId = DefaultGroupId,
                          }).OrderByDescending(x => x.Call_Time).ToList();
            return getList;

#SQL语句
这是比较熟悉的3张表的连接
SELECT *
FROM test.contract a
JOIN test.customer b ON a.Num = b.Num2
JOIN test.customer3 c ON a.num = c.num3;

连接不一定join后面都是和第一张表的连接。
SELECT *
FROM test.contract a
JOIN test.customer b ON a.Num = b.Num2
LEFT JOIN test.customer4 d ON b.num2 = d.num4;