13-06-2015 SQL 笔记

1. 我经常把having和on搞混

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used withaggregatefunctions.

Example:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

注意到没有：having跟着aggregate function走的

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 10;

Keyword On

On代表join的条件

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

以下内容来自Beyond SQL Basics

2. Converting datatypes

Convert Int to String:

-- CONVERT( NEW DATA TYPE, COLUMN OR EXPRESSION)

Select fn + ' ' + sn + ' (' + convert(varchar(10), contactid) + ')' as Reference, * from tblcontacts

3. Create temporary table

--Create a Temporary Table

create table #sample(
contactid int,
firstName varchar(100)
lastName varchar(100)
displayName varchar(150),
dob smalldatetime
)

select * from #sample