18 08 17 SQL自我研讀(一)

-
JOIN

a. 有INNER、OUTER、CROSS三大種類
b. 預設連結運算子為INNER
c. CROSS為笛卡爾乘積，另CROSS不用加ON條件(因為互相相乘)

ORDER BY

a. WHERE 子句無法使用別名，只有ORDER BY可以，因ORDER BY是SELECT子句之後處理
b. TOP 之後接WITH TIES會將有平手的資料列列出來
c. TOP 之後接PERCENT，因要計算整數會無條件進位
d. OFFSET模板 (OFFSET為必要；FETCH則為非必要)，只支援2012後的版本
OFFSET X Rows
FETCH NEXT X Rows ONLY ;
e. 排序值如果有NULL，會影響順序 (SQL將NULL視為最小值)


留言

張貼留言

這個網誌中的熱門文章

TSQL Lab 09 習題

-
LAB 09 Exercise 1 --------------------------------------------------------------------- -- Task 2 --  -- Write a SELECT statement that will return groups of customers that made a purchase. -- The SELECT clause should include the custid column from the Sales.Orders table  -- and the contactname column from the Sales.Customers table.  -- Group by both columns and filter only the orders from the sales employee  -- whose empid equals five. --------------------------------------------------------------------- 寫法1 select c.custid,c.contactname from Sales.Customers c inner join Sales.Orders o on c.custid = o.custid where o.empid = 5 group by c.custid,c.contactname 寫法2 select distinct(o.custid), c.contactname,o.empid from Sales.Orders o inner join Sales.Customers c on o.custid = c.custid where o.empid = 5 order by o.custid asc --------------------------------------------------------------------- -- Task 3 --  -- Copy the T-SQL statement ...
閱讀完整內容

TSQL Lab 06 習題

-
LAB 06 Exercise 1 --------------------------------------------------------------------- -- Task 1 -- -- -- Write a SELECT statement to return columns that contain: --  The current date and time. Use the alias currentdatetime. --  Just the current date. Use the alias currentdate. --  Just the current time. Use the alias currenttime. --  Just the current year. Use the alias currentyear. --  Just the current month number. Use the alias currentmonth. --  Just the current day of month number. Use the alias currentday. --  Just the current week number in the year. Use the alias currentweeknumber. --  The name of the current month based on the currentdatetime column. Use the alias currentmonthname. -- -- Execute the written statement and compare the results that you got with the desired results shown in the file 52 - Lab Exercise 1 - Task 1 Result.txt. Your results will be different because of the current date and time value. -- -- Ca...
閱讀完整內容

TSQL Lab 08 習題

-
LAB 08 Exercise 1 --------------------------------------------------------------------- -- Task 1 --  -- Write a SELECT statement against the Production.Products table to retrieve a calculated column named productdesc. The calculated column should be based on the columns productname and unitprice and look like this: --  The unit price for the Product HHYDP is 18.00 $. -- -- Execute the written statement and compare the results that you got with the desired results shown in the file 52 - Lab Exercise 1 - Task 1 Result.txt.  -- -- Did you use the CAST or the CONVERT function? Which one do you think is more appropriate to use? --------------------------------------------------------------------- select productname,'The unit price for the Product ' + SUBSTRING(productname,9,LEN(productname)) + ' is ' + convert(varchar(10),unitprice) + ' $.' as productdesc from Production.Products YES,因為有金額欄位接成字串需要做轉換 --------------------------------------------...
閱讀完整內容