第一篇:sql多字段排序
252185300.doc 问题：sql 查询多表排序 Table1 Id nun 1 Sdfsd 2 Fsdf 3 Fsdf 4 Sdf 5 Sdfdfg table2 id table1_id num 1 2 3 4 5 6 7 8 9 10 11 12 13 3 3 3 1 1 1 1 1 2 2 2 2 2 sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf 4 5 3 1 2 paixu 我想 查处表 table2 的 记录 但是 想 按 表 table1 的排序方式输出 字段 Table1_id 为表 table1 的 id sql 语句如何写 我用的 是 acc 数据库 回答select table2.* from table2 inner join table1 on table2.table1_id = table1.id order by table1.paixu 就像你写的这样，直接加过滤条件就可以 但是要在字段前加表名前缀以区分两个表的 ID 字段 select table2.* from table2 inner join table1 on table2.table1_id = table1.id order by table1.paixu where table2.id > 3 and table2.id < 6 第 1 页 共 1 页第一篇:sql多字段排序
-- ============================================= -- Author David.Yan -- Create date2007.12.17 -- Description /* 高效通用分页存储过程(双向检索) 敬告：适用于单一主键或存在唯一值列的表或视图 ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 */ -- ============================================= CREATE PROC [dbo].[P_viewPage_job] -- Add the parameters for the stored procedure here @TableName VARCHAR(1000), --表名 @FieldList VARCHAR(2000), --显示列名，如果是全部字段则为* @PrimaryKey VARCHAR(100), --单一主键或唯一值键 @Where VARCHAR(3000), --查询条件 不含'where'字符，如id>10 and len(userid)>9 @Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷 @RecorderCount INT, --记录总数 0:会返回总记录 @PageSize INT, --每页输出的记录数 @PageIndex INT, --当前页数 @TotalCount INT OUTPUT, --记返回总记录 @TotalPageCount INT OUTPUT --返回总页数 AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0 SET @Order = RTRIM(LTRIM(@Order)) SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','') WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0 BEGIN SET @Order = REPLACE(@Order,', ',',') SET @Order = REPLACE(@Order,' ,',',') END IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' OR ISNULL(@PrimaryKey,'') = '' OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 BEGIN PRINT('ERR_00参数错误') RETURN END IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC') BEGIN PRINT('ERR_02排序错误') RETURN END DECLARE @new_where1 VARCHAR(1000) DECLARE @new_where2 VARCHAR(1000) DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000) DECLARE @new_order3 VARCHAR(1000) DECLARE @Sql VARCHAR(8000) DECLARE @SqlCount NVARCHAR(4000) IF ISNULL(@where,'') = '' BEGIN SET @new_where1 = ' ' SET @new_where2 = ' WHERE ' END ELSE BEGIN SET @new_where1 = ' WHERE ' + @where SET @new_where2 = ' WHERE ' + @where + ' AND ' END IF CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0 BEGIN SET @new_order1 = ' ORDER BY ' + @Order SET @new_order2 = @Order + ',' SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},') SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,') SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1) IF @FieldList <> '*' BEGIN SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') SET @FieldList = ',' + @FieldList WHILE CHARINDEX(',',@new_order3)>0 BEGIN IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0 BEGIN SET @FieldList = @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3)) END SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3)) END SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END END SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1 IF @RecorderCount = 0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', @TotalCount OUTPUT,@TotalPageCount OUTPUT END ELSE BEGIN SELECT @TotalCount = @RecorderCount END IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) END IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) BEGIN IF @PageIndex = 1 --返回第一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 END IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 END-- END ELSE BEGIN --多列排序，必须包含主键，且放置最后，否则不处理 IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 BEGIN PRINT('ERR_02') RETURN END IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' + @new_order2 + ' ) AS TMP ' + @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@TotalCount-(@PageSize-1)*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 + ' ) AS TMP ' + @new_order1 END END EXEC(@Sql) --print @sql --select @sql GO第一篇:sql多字段排序
分组、 实验五 SQL 分组、 排序及多表连接语句 一、实验目的 1. 2. 3. 熟练掌握 SQL 分组语句； 熟练掌握 SQL 排序语句； 熟练掌握多表连接语句； 二、实验内容 给定一个练习数据库和相应的练习题，要求上机完成，并验证结果 实验基础知识提要 从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。虽然 SELECT 语句 的完整语法较复杂，但是其主要的子句可归纳如下SELECT select_list FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] From 子句中的连接类型 指定从其中检索行的表，当存在多个表时用“,”分隔。表之间可以使用连接，连接的类型 如下INNER 指定返回所有相匹配的行对。废弃两个表中不匹配的行。如果未指定联接类型，则 这是默认设置。LEFT [OUTER] 指定除所有由内联接返回的行外，所有来自左表的不符合指定条件的行也包含在结 果集内。来自左表的输出列设置为 NULL。RIGHT [OUTER] 指定除所有由内联接返回的行外，所有来自右表的不符合指定条件的行也包含在结 果集内。来自右表的输出列设置为 NULL。FULL [OUTER] 如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包含该行，并且 将与另一个表对应的输出列设置为 NULL。除此之外，结果集中还包含通常由内联 接返回的所有行。Group By 子句 指定用来放置输出行的组，并且如果 SELECT 子句 <select list> 中包含聚合函数， 则计算每组的汇总值。指定 GROUP BY 时， 选择列表中任一非聚合表达式内的所有列都应 包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。Having 子句 指定组或聚合的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一样。Order By 子句 指定结果集的排序。除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、 派生表和子查询中无效。ASC 指定按递增顺序，从最低值到最高值对指定列中的值进行排序。DESC 指定按递减顺序，从最高值到最低值对指定列中的值进行排序。空值被视为最低的可能值。 三、实验步骤 构建以下的数据表作为实验数据内容 3.1 Suppliers(供货厂商) 代码 SupplierID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax HomePage 描述 供货厂商编号 厂名 联系人名 联系人职位 地址 城市名 地区 邮政编码 国家 电话 传真 主页 数据类型 INT VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR 长度 4 40 30 30 60 15 15 10 15 24 24 16 约束条件 主码 3.2 Region（地区） 代码 RegionID 描述 地区编号 数据类型 INT 长度 4 约束条件 主码 RegionDescription 地区描述 VARCHAR 50 3.3 Products（产品） 代码 ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued 3.4 Orders（定单） 代码 OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion PostalCode ShipCountry 代码 OrderID ProductID UnitPrice Quantity Discount 代码 EmployeeID LastName FirstName Title 描述 定单编号 顾客编号 职员编号 定货日期 交货日期 载运日期 经由数 运费 船名 地址 城市 地区 邮政编码 国籍 描述 定单编号 产品编号 单价 数量 折扣 描述 职工编号 姓 名 头衔 数据类型 INT VARCHAR INT DATETIME DATETIME DATETIME INT FLOAT VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR 数据类型 INT INT FLOAT INT FLOAT 数据类型 INT VARCHAR VARCHAR VARCHAR 长度 4 5 4 8 8 8 4 8 40 60 15 15 10 15 长度 4 4 8 2 4 长度 4 20 10 30 约束条件 主码 约束条件 主码 主码 约束条件 主码 描述 产品编号 品名 供货厂商编号 所属种类号 单位数量 单价 库存 定货数 修订量 是否进行 数据类型 INT VARCHAR INT INT VARCHAR FLOAT INT INT INT BIT 长度 4 40 4 4 20 8 2 2 2 1 约束条件 主码 3.5 OrderDetails（定单详细信息） 3.6 Employees（职工） TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country HomePhone Extension Photo Notes ReportsTo Photopath 代码 CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax 性别 生日 受聘日期 地址 城市 地区 邮政编码 国籍 住宅电话 分机号 照片 备注 直接上级号 职工照片路径 描述 顾客编号 公司名 联系人名 联系人头衔 地址 城市 地区 邮政编码 国籍 电话 传真 VARCHAR DATETIME DATETIME VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR IMAGE VARCHAR INT VARCHAR 数据类型 VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR 25 8 8 60 15 15 10 15 24 4 16 16 4 255 长度 5 40 30 30 60 15 15 10 15 24 24 约束条件 主码 3.7 Customers（顾客） 3.8 OldSuppliers（供应厂商备份表） 结构与 Suppliers 表相同 运行 SQL SERVER 服务管理器，确认数据库服务器开始运行。运行企业管理器，以图示方式点击“附加数据库” ，恢复 db 目录下的数据库文件 打开查询分析器，选择刚才恢复的数据库 exampleDB，输入 SQL 指令，获得运行结果。任务任务：完成以下 SQL 查询语句 查询顾客表(Customer)中没有设定区域的顾客编号和公司名 select CustomerID,CompanyName from customers where Region is NULL 统计职工表(Employees)中头衔的数量 select distinct count(Title) from Employees 查找订单表(Orders)中顾客编号为’VICTE’和’WELLI’的的订单号和运费， 并按照运 费的降序排列 select OrderID,Freight from orders where CustomerID in ('VICTE','WELLI') order by Freight DESC 查找产品表(Products)中的平均库存总价(库存总价=单价×库存数) select avg(UnitPrice*UnitsInStock) from products 查找提供产品的各个供应商编号及其供应的产品数量 select SupplierID,UnitsOnOrder from products 查找提供产品的各个供应商名称及其供应的产品数量 select CompanyName,UnitsOnOrder from products,suppliers where products.SupplierID=suppliers.SupplierID 在订单详细信息表(OrderDetails)中查找每个订单号对应的产品种类超过 4 种的订 单号和产品种类数，并按照产品种类数升序排列 select OrderID,count(ProductID) from OrderDetails group by OrderID having count(ProductID)>=4 order by count(ProductID) 查询客户的公司名和它所下订单的订单编号 select CompanyName,OrderID from customers,orders where customers.CustomerID=orders.CustomerID 找出所有的职员姓和名以及他的直接上级的姓和名 select f1.LastName CLastName,f1.FirstName CFirstName,f2.LastName RLastName,f2.FirstName RFirstName from Employees f1 LEFT JOIN Employees f2 ON (f1.ReportsTo=f2.EmployeeID) 使用外部连接，查找所有的职员的基本信息以及其直接上级的姓、名 select f1.*,f2.LastName RLastName作文,f2.FirstName RFirstName from Employees f1 LEFT JOIN Employees f2 ON (f1.ReportsTo=f2.EmployeeID) 实验结果与分析（上交实验报告） 四. 实验结果与分析（上交实验报告） 分析思考问题分析思考问题1. 分析 Where 筛选和 Having 筛选的区别； having 和 where 都是用来筛选用的 having 是筛选组 而 where 是筛选记录 他们 有各自的区别 1》当分组筛选的时候 用 having 2》其它情况用 where 用 having 就一定要和 group by 连用， 用 group by 不一有 having （它只是一个筛选条件用的） where 作用于表而 Having 作用于组。 2.给出（可写遇到的问题及解决方法，或者对技术的理解等）多表查询中的左外连接，右外 连接和全连接示例 SQL 语句，并分析其使用场景； a 表 id name b 表 id job parent_id 1 张 3 1 23 1 2 李四 2 34 2 3 王武 3 34 4 a.id 同 parent_id 存在关系 左连接 select a.*,b.* from a left join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 3 王武 null 右连接 select a.*,b.* from a right join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 完全连接 select a.*,b.* from a full join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 3 王武 null 用法：外联接。外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定： LEFT JOIN 或 LEFT OUTER JOIN。左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列 所匹配的行。如果左表的某行在右表中没有匹配行， 则在相关联的结果集行中右表的所有选 择列表列均为空值。 RIGHT JOIN 或 RIGHT OUTER JOIN。右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没 有匹配行，则将为左表返回空值。 3. 给出不少于 5 个聚合函数，并写出示例 SQL 语句，说明含义。其中至少有两个 SQL 语句要包含 Having 子句 Avg 函数：select sno,cno From SC x Where Grade >=(select AVG(Grade) From SC y Where y.sno=x.sno); Count 函数：SELECT type FROM titles GROUP BY type HAVING COUNT(type) > 1 Min、Max 函数select max(sal),job from emp group by job Sum 函数：select a,sum(a) from t1,ta group by a having sum(a)>100 五．讨论、心得 基本上没有问题。