SQL/T-SQL实例参考-2

 

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

子查询语法

--一对多关联查询,查询多中的记录,但是返回一的结果集
SELECT C.*  FROM (
SELECT A.BasicID
FROM [tbiz_PuzzleBasic] A LEFT JOIN [tbiz_PuzzleWork] B ON B.BasicID = A.BasicID
WHERE 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.I
FROM
(
SELECT QUESTIONID,ROW_NUMBER() OVER(ORDER BY QUESTIONID ASC) As I FROM TD_QUESTION B
WHERE SCALEID=21
) M
WHERE SCALEID=21

例子2

UPDATE sheet1 SET ID=M.I
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY 账单编号 ) AS I,账单编号 FROM sheet1
) M
WHERE sheet1.账单编号 = M.账单编号UPDATE sheet1 SET ID=M.I
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY 账单编号 ) AS I,账单编号 FROM sheet1
) M
WHERE sheet1.账单编号 = M.账单编号

 

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

UPDATE TD_QUESTION SET SORTNUM=M.I
FROM TD_QUESTION A INNER JOIN
(
SELECT QUESTIONID,ROW_NUMBER() OVER(ORDER BY QUESTIONID ASC) As I FROM TD_QUESTION B
WHERE SCALEID=21
) M ON A.QUESTIONID= M.QUESTIONID
WHERE 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

 

SQL/T-SQL实例参考-1

CASE

SELECT [staff_no] 
	  ,CASE WHEN ISNUMERIC([staff_no])=0 THEN 0 ELSE CONVERT(numeric(18,0),[staff_no]) END staff_no_num 
      ,[staff_name]
      ,[role]
      ,[dept_no]
      ,[dept_name]
      ,[team_no]
      ,[team_name]
  FROM [tStaff_temp] ORDER BY staff_no_num DESC