public List <GetingeProductPrants> GetGetingeDeptChildrenList(int deptId) { List <GetingeProductPrants> list = new List <GetingeProductPrants>(); string sql = @"WITH ProductTree AS ( SELECT [dept_id] ,[parent_id] ,[dept_name] ,[product_id] FROM [wxtest_db].[dbo].[Getinge_Dept_Product] WHERE [dept_id] = " + deptId + @" --需要查找的父节点 UNION ALL SELECT [wxtest_db].[dbo].[Getinge_Dept_Product].* FROM ProductTree JOIN [wxtest_db].[dbo].[Getinge_Dept_Product] ON ProductTree.[dept_id]= [wxtest_db].[dbo].[Getinge_Dept_Product].parent_id ) SELECT t.*,b.[product_name],b.[product_mark],b.[product_level] FROM ProductTree t LEFT JOIN [wxtest_db].[dbo].[Getinge_Product] b ON t.[product_id]= b.[product_id] ORDER BY [parent_id];"; using (SqlDataReader reader = DBHerlper.GetReader(sql)) { list = GetEcmoUserListByRow(reader); } return(list); }
public List <GetingeProductPrants> GetGetingeProductPrantsList(int productId) { List <GetingeProductPrants> list = new List <GetingeProductPrants>(); string sql = @"WITH ProductTree AS ( SELECT [product_id] , [parent_id] , [dept_id] , [product_name] , [product_mark] , [product_level] FROM [wxtest_db].[dbo].[Getinge_Product] WHERE [product_id] = " + productId + @" --需要查找的子节点 UNION ALL SELECT [wxtest_db].[dbo].[Getinge_Product].* FROM ProductTree JOIN [wxtest_db].[dbo].[Getinge_Product] ON ProductTree.parent_id = [wxtest_db].[dbo].[Getinge_Product].[product_id] ) SELECT [product_id] , [parent_id] , [dept_id] , [product_name] , [product_mark] , [product_level] FROM ProductTree ORDER BY [parent_id];"; using (SqlDataReader reader = DBHerlper.GetReader(sql)) { list = GetEcmoUserListByRow(reader); } return(list); }
/// <summary> /// 查询指定序号后N条 /// </summary> /// <param name="count">条数</param> /// <param name="index">序号</param> /// <returns></returns> public List <EcmoUser> GetEcmoUserList() { List <EcmoUser> list = new List <EcmoUser>(); string sql = @" SELECT d.* , enroll_name FROM [wxtest_db].[dbo].Ecmo_User d LEFT JOIN ( SELECT [openid] , STUFF(( SELECT ',' + enroll_name FROM [wxtest_db].[dbo].[Ecmo_Enroll] a WHERE b.[openid] = a.[openid] FOR XML PATH('') ), 1, 1, '') enroll_name FROM [wxtest_db].[dbo].[Ecmo_Enroll] b GROUP BY [openid] ) c ON d.[openid] = c.[openid]"; using (SqlDataReader reader = DBHerlper.GetReader(sql)) { list = GetEcmoUserListByRow(reader); } return(list); }