18 10 01 自我研讀(四)

-
COUNT 會忽略空值
LEFT JOIN = LEFT (OUTER) JOIN

GROUP BY
ROLLUP 運算子可用來產生包含小計與總數的報告。ROLLUP 運算子將產生一個類似於 CUBE 運算子產生之結果集的結果集。如需詳細資訊，請參閱＜使用 CUBE 來摘要資料＞。

以下是 CUBE 與 ROLLUP 之間的特定差異：

CUBE 將產生一個結果集，顯示出選定資料行之所有值組合的彙總。
ROLLUP 將產生一個結果集，顯示出選取的資料行中值階層的彙總。

INTERSECT

except(差集)
select * from A
except
select * from B

(任何欄位值不同,皆會被列出,列出A存在但B不存在的資料)

===================
DateTime 跟 DateTimeOffset都是一個struct

DateTimeOffset跟DateTime很相似，都是用來表示日期跟時間

但是DateTimeOffset多提供了GMT time的offset，用來明確表示特定時間

當根據時區表示明確的時間點時，使用DateTimeOffset
ex: now, transaction times, file change times, logging event times

不需要表示特訂時區時，使用DateTime

不需要特訂時間時，使用DateTime。
例如:生日，並且把時間訂為 00:00:00

不要在DateTimeOffset中使用DateTimeKind

只有時間沒有日期時，使用TimeSpan


for system_time as of  '2017-01-01'  從TEMP TABLE取得一段時間 117

留言

張貼留言

這個網誌中的熱門文章

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,因為有金額欄位接成字串需要做轉換 --------------------------------------------...
閱讀完整內容