- -- charindex 函數
- SELECT *
- ,CHARINDEX(season, '冬天春天夏天秋天') pos
- ,ROW_NUMBER() OVER(ORDER BY season) "ex ante"
- FROM
- (
- SELECT '春天' season
- UNION
- SELECT '夏天'
- UNION
- SELECT '秋天'
- UNION
- SELECT '冬天'
- )a
- ORDER BY
- CHARINDEX(a.season, '春天夏天秋天冬天')
- --NULLIF 函數 (EXP1, EXP2) 與EXP2比較,相同回傳NULL, 否則回傳EXP1
- SELECT 100*1/ NULLIF(VAL, 0) "RATIO"
- ,CASE WHEN VAL=0 THEN NULL
- ELSE CAST( 100.0*1/VAL AS INT) END "ratio2"
- FROM
- (
- SELECT 0 val
- UNION ALL
- SELECT 2 val
- )a
2014年5月14日 星期三
[SQL] NUFFIF + CHARINDEX
2014年5月13日 星期二
[SQL] order by case 排序
- --生成OrderTest
- DROP TABLE OrderTest
- WITH TEST(N, VAL)
- AS
- (
- SELECT 1 N
- ,CAST('A' AS VARCHAR) VAL
- UNION ALL
- SELECT N+1
- ,CAST(SUBSTRING('ABCDE', N+1, 1) AS VARCHAR)
- FROM TEST
- WHERE N<5
- )
- SELECT * INTO OrderTest
- FROM TEST
- --排序12354
- SELECT N
- FROM OrderTest
- ORDER BY --越小越前面
- --區域前 (123)(45)
- CASE WHEN n>3 THEN 1 ELSE 0 END
- --區預後 (123)(54)
- CASE WHEN n>3 THEN 0-N ELSE N END
- --CBADE
- SELECT val
- FROM OrderTest
- ORDER BY
- --分類(abc)(de)
- CASE WHEN n>3 THEN 1 ELSE 0 END
- --到轉(cba)(de)
- ,CASE WHEN n<4 THEN 0-N ELSE N END
- --cbade
- SELECT val
- FROM OrderTest
- ORDER BY
- --分兩類
- --case when val in ('D','E') THEN 1 ELSE 0 END
- --排序
- CASE WHEN val < 'D' THEN
- ROW_NUMBER() OVER(ORDER BY val DESC)
- ELSE ROW_NUMBER() OVER(ORDER BY val ASC) END
[SQL] CASE WHEN
- --case 範例 表達式只有一組
- SELECT
- CASE Cust_Name
- WHEN '張先生' THEN 1
- WHEN '曹先生' THEN 2
- ELSE 3
- END UseNo
- FROM Orders
- SELECT
- CASE SUBSTRING(emp_id, 2, 1)
- WHEN '1' THEN '男'
- WHEN '2' THEN '女'
- ELSE 'unknow'
- END Sex
- FROM employee
- --搜索式case子句 表達式有多組
- SELECT
- CASE
- WHEN cust_Name = '張先生' THEN 1
- WHEN cust_Name = '曹先生' THEN 2
- ELSE 3
- END USERNO
- , CASE
- WHEN CUST_NAME '%先生' THEN '男'
- ELSE '女'
- END SEX
- ,CASE
- WHEN QTY BETWEEN 0 AND 150 THEN 0.10
- WHEN QTY BETWEEN AND 300 THEN 0.15
- WHEN QTY > 300 THEN 0.2
- END DISCOUNT
- FROM ORDERS
訂閱:
文章 (Atom)