SELECT DISTINCT 、UNION、ORDER BY、GROUP BY、HAVING

一、SQL 查询结果去除得复行

select DISTINCT a.员工编号 
					     , CONVERT(date , b.日期) as 日期  
					     , b.工资表编号
					from gd_js a inner join gd_jm b
							on a.计件单主表序号 = b.计件单主表序号 
							inner join inserted i
							on a.计件单子表序号  = i.计件单子表序号

二、排序

--以字母顺序显示公司名称：
SELECT Company, OrderNumber FROM Orders ORDER BY Company 
-- 降序
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
-- 第二列排序
SELECT Company, OrderNumber FROM Orders ORDER BY 2 -- 对OrderNumber排序

三、分组 GROUP BY

select   s_fielstype.cvfptype + '小计' , sum(iid)
from dbo.s_fielstype
group by s_fielstype.cvfptype

四、HAVING 分组后筛选

having 作用类似于where，差别是where过滤行，having过滤组
使用顺序：where、group by、having、order by、limit

下面的示例按产品 ID 对 SalesOrderDetail 进行了分组，并且只包含那些订单合计大于 $1,000,000 且其平均订单数量小于 3 的产品组。

USE AdventureWorks;
GO

SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
    AND AVG(OrderQty) < 3 ;

GO

五、两条SELECT 合并
SELECT UNION

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

					select DISTINCT i.员工编号 
					     , CONVERT(date , i.日期) as 日期  
					     , i.工资表编号
					from  inserted i
							
					UNION
					
					select DISTINCT d.员工编号 
					     , CONVERT(date , d.日期) as 日期  
					     , d.工资表编号
					from  deleteed d