对多关联查询,查询多中的记录,但是返回一的结果集

子查询语法

--一对多关联查询,查询多中的记录,但是返回一的结果集SELECT C.*  FROM (SELECT A.BasicIDFROM [tbiz_PuzzleBasic] A LEFT JOIN [tbiz_PuzzleWork] B ON B.BasicID = A.BasicIDWHERE B.Position LIKE '%经理%'GROUP BY A.BasicID ) T LEFT JOIN [tbiz_PuzzleBasic] C ON T.BasicID = C.BasicID

 

SqlBulkCopy

//string str = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();//SqlConnection conn = new SqlConnection(str);DataTable dtTdAnswerStorage = new DataTable();dtTdAnswerStorage.Columns.Add("QUESTIONID", typeof(int));dtTdAnswerStorage.Columns.Add("QUESTIONNAME", typeof(string));dtTdAnswerStorage.Columns.Add("SORTNUM", typeof(int));strQid = dt.Rows[0]["Qid"].ToString();foreach (OptionItem option in questionItem.OptionItems){    DataRow dr = dtTdAnswerStorage.NewRow();    dr["QUESTIONID"] = strQid;    dr["QUESTIONNAME"] = option.OptionTitle;    dr["SORTNUM"] = option.SortNum;    dtTdAnswerStorage.Rows.Add(dr);}using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con)){    bulkCopy.BatchSize = 5000;    bulkCopy.BulkCopyTimeout = 60;    bulkCopy.DestinationTableName = "TD_ANSWER_STORAGE";    bulkCopy.ColumnMappings.Add("QUESTIONID", "QUESTIONID");    bulkCopy.ColumnMappings.Add("QUESTIONNAME", "QUESTIONNAME");    bulkCopy.ColumnMappings.Add("SORTNUM", "SORTNUM");    bulkCopy.WriteToServer(dtTdAnswerStorage);}

INSERT FROM SELECT

Insert Into TD_ANSWER(ANSWERID,QUESTIONID,SCALEID,QUESTIONNAME,SORTNUM) Select ANSWERID,QUESTIONID,{
   0},QUESTIONNAME,SORTNUM From TD_ANSWER_STORAGE WHERE QUESTIONID ={
   1};

UPDATE FROM SELECT

例子1:子查询嵌套+自增序列

UPDATE TD_QUESTION SET SORTNUM=M.IFROM(SELECT QUESTIONID,ROW_NUMBER() OVER(ORDER BY QUESTIONID ASC) As I FROM TD_QUESTION BWHERE SCALEID=21) MWHERE SCALEID=21

//所谓的链接子查询写法

UPDATE TD_QUESTION SET SORTNUM=M.IFROM TD_QUESTION A INNER JOIN(SELECT QUESTIONID,ROW_NUMBER() OVER(ORDER BY QUESTIONID ASC) As I FROM TD_QUESTION BWHERE SCALEID=21) M ON A.QUESTIONID= M.QUESTIONIDWHERE SCALEID=21

 处理NULL相关函数

isnull(check_expression , replacement_value ) ISNULL( T.recommendCount, 0 ) AS recommendCount,

CASE WHEN TBIZ_Contract.ContractID IS NULL

Group By

SELECT RoleID,ISNULL(Count(*),0) NUM FROM tbiz_UserInfo WHERE CompanyID=177 GROUP BY  RoleID HAVING RoleID=6