随笔分类 -  T-SQL 基础

摘要：透视转换：use tempdb;if object_id('dbo.Orders', 'U') is not null drop table dbo.Orders;create table dbo.Orders( orderid int not null, orderdate date not null, empid int not null, custid varchar(5) not null, aty int not null, constraint pk_Orders primary key(... 阅读全文

摘要：{TODO:} 阅读全文

摘要：1.派生表(derived table)select YEAR(orderdate) as orderyear, COUNT(distinct custid) as numcustsfrom Sales.Ordersgroup by YEAR(orderdate);内联别名select orderyear, COUNT(distinct custid)from (select YEAR(orderdate)as orderyear, custid from Sales.Orders) as Dgroup by orderyear外部命名select orderyear, COUNT(disti 阅读全文

摘要：简单子查询select * from (select custid, companyname from Sales.Customers where country = N'USA') as USACusts关联子查询select custid, orderid, orderdate, empidfrom Sales.Orders as o1where orderid = (select max(o2.orderid) from Sales.Orders as o2 where o2.custid = o1.custid)select orderid, custid, val.. 阅读全文

摘要：{TODO:} 阅读全文

摘要：开窗函数overselect orderid, custid, val,SUM(val) over() as totalvalue,SUM(val) over(partition by custid) as custtotalvaluefrom Sales.OrderValuesselect orderid, custid, val,100. * val / SUM(val) over() as pctall,100. * val / SUM(val) over(partition by custid) as pctcustfrom Sales.OrderValuesover 子句也支持四种排 阅读全文

摘要：第一范式： 第一范式要求表中的行必须是唯一的，属性应该是原子的(atomic)。这个范式对于关系的定义来说是冗余的，换句话说，如果一个表真可以表示一个关系，那么它一定符合第一范式。行的唯一性是可以通过在表中定义一个唯一的主键而实现的。对于属性，只能使用随属性的数据类型定义一起定义的操作来对它们进行操... 阅读全文