18 10 01 自我研讀(三)

-
=== IIF ( boolean_expression, true_value, false_value )

範例：
select productid,unitprice,
iif(unitprice>50 ,'high','low') as pricepoint
from Production.Products

=== PARSE、TRY_PARSE

-- 繁體中文，日期的順序是：ymd
 SELECT PARSE('01/02/03' AS  datetime2 USING 'zh-TW') N'日期'
GO

 -- 英文 us_english，日期的順序是：mdy
SELECT PARSE('01/02/03' AS  datetime2 USING 'en-US') N'日期'
GO

== CHOOSE(index,condition 1,condition 2...)

 select CHOOSE(3,'Japan','China','Taiwan')
-- result  'Taiwan' 
== CAST CONVERT 區別

CONVERT是專對SQL Server使用的，使日期與時間值，小數之間轉換具有更寬的靈活性。

CAST是兩種功能中更具ANSI標準的功能，即雖然更具便攜性（比如，使用CAST的函數能更容易的被其它資料庫軟體使用），但功能相對弱一些。不過，當小數轉化為數值，並保留原始運算式中的小數數值時，仍然需要使用CAST

== NULLIF COALESCE 區別

 NULLIF是專對SQL Server使用的，盡可能使用規範中所支援的標準函數COALESCE

留言

張貼留言

這個網誌中的熱門文章

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