【MSSQL】名稱相同址更新最新一筆資料

UPDATE _test SET [value]='100' WHERE id  = (SELECT TOP 1 id FROM _test WHERE [name]='aa' ORDER BY id DESC) AND [name]='aa'

【MSSQL】為查詢加上排序序號 ROW_NUMBER(), RANK(), DENSE_RNAK()

在 MSSQL 中有個好用的函數，能為查詢加上序號，

其中又分為

1.ROW_NUMBER() ：能將指定欄位做排序，並加上排序序號

2.RANK()：能將指定欄位做排序，相同資料排序相同

3.DENSE_RNAK()：能將指定欄位做排序，相同資料排序相同，但序號會延續

4.ROW_NUMBER 加上 PARTITION 能將順序重新排序

5.MySQL 要呈現相同結果可以用IF加上變數去+1

if(@_type_id=type_id,@rank:=@rank+1,@rank:=1)

【MSSQL】DECLARE 宣告

DECLARE @k nvarchar(255)
SET @k = 'ABC'
SELECT *  FROM table WHERE name LIKE '%'+@k+'%'

【SQL】if else

基本

PHP

   if ($score >= 90)
     $str = 'A';
    elseif ($score >= 70 && $score < 90) 
     $str = 'B';
    elseif ($score >= 60 && $score < 70) 
     $str = 'C';  
    else{
     $str = 'D';  
    }
     
  

SQL

   CASE  
     WHEN score >= 90 THEN 'A'
      WHEN score >= 90 THEN 'B'
      WHEN score >= 90 THEN 'C'
      ELSE 'D'
    END AS str
  

欄位值

Name	Answer
張一	1
王二	2
李三	3

    SELECT Name, 
     case Answer
       when 1 then '喜歡'
       when 2 then '不喜歡'
       when 3 then '一般'
      END
    FROM table;
    
    OR
    
    SELECT Name, 
     case
       when Answer=1 then '喜歡'
       when Answer=2 then '不喜歡'
       when Answer=3 then '一般'
      END AS Answer
      FROM table;
  

【MSSQL】最近執行的SQL

查詢目前正在執行的SQL語法

SELECT TOP 200 qs.creation_time,last_execution_time-creation_time as time,text 
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY qs.creation_time DESC

SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
    ltrim(q.text),
    r.statement_start_offset/2+1,
    (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
   LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc

SELECT
A.SESSION_ID,DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,
A.START_TIME,A.COMMAND,A.CPU_TIME,
A.PERCENT_COMPLETE,A.ESTIMATED_COMPLETION_TIME,B.TEXT

FROM SYS.DM_EXEC_REQUESTS A
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B
WHERE SESSION_ID > 50

看哪個語法最花時間

SELECT TOP 10 total_worker_time, last_worker_time, 
max_worker_time, min_worker_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset 
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END 
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC   

SELECT TOP 50
 [Average CPU used] = total_worker_time / qs.execution_count,
 [Total CPU used] = total_worker_time,
 [Execution count] = qs.execution_count,
 [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;


WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 
  SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
       OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);
SELECT 
substring(text,qs.statement_start_offset/2
,(CASE 
WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), 
text)) * 2 
ELSE qs.statement_end_offset 
END - qs.statement_start_offset)/2) 
,qs.plan_generation_num as recompiles
,qs.execution_count as execution_count
,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
,qs.total_worker_time as cpu_time
,qs.total_logical_reads as reads
,qs.total_logical_writes as writes
,last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
LEFT JOIN sys.dm_exec_requests r 
ON qs.sql_handle = r.sql_handle
ORDER BY total_wait_time DESC

【SQL】優化搜尋

今天發現網站變慢，查了一下原因

1.量大需限定範圍:

資料大約50萬筆，抓最近5筆資料，多加了範圍限制快了10倍

$sql = "SELECT TOP(5) *
FROM page_log pl 
ORDER BY time DESC";

$s = microtime(time);
$result = $this->db->queryAll($sql);
$e = microtime(time);
echo $e - $s;

結果：0.605秒

$sql = "SELECT TOP(5) *
FROM page_log pl 
WHERE time > '20140807000000'
ORDER BY time DESC";

$s = microtime(time);
$result = $this->db->queryAll($sql);
$e = microtime(time);
echo $e - $s;

結果：0.07秒

【MySQL、MSSQL】 分頁寫法

MSSQL 分頁的寫法:
抓取第80000~80020的資料 

SELECT * FROM
(SELECT ROW_NUMBER() over (order by id ASC) sort,* FROM test) as c
WHERE sort BETWEEN 80000 and 80020

OR

WITH Table AS (
   SELECT ROW_NUMBER() over (order by id ASC) sort,* FROM test
) SELECT * FROM Table WHERE sort BETWEEN 80000 and 80020



MYSQL：

SELECT * FROM `test` LIMIT 80000,20


* MySQL 簡單多了

【MYSQL、MSSQL】複製資料表、複製一筆資料

1.複製一個資料表
MySQL 建議使用下面的方式，好處是 Schema 會一樣

1. CREATE TABLE new_table LIKE old_table;
2. INSERT new_table SELECT * FROM old_table

MSSQL 要使用 SELECT * INTO new_table FROM old_table
只複製結構 SELECT * INTO new_table FROM old_table WHERE 1=0

2.複製一筆資料
MySQL、MSSQL 都可使用

INSERT INTO test(
id,
        name,
test1,
test2
)
SELECT
3 AS id,
name,
'test' AS test1,
test2
FROM test
WHERE id =1

* 要複製原來的值就不用AS

3.運用
當資料量大，查詢時可將一個表拆成多個表
如:
Insert Into new_table （學號, 姓名,國文,英文,數學）
Select * From old_table Where 英文>90

【php】連線 sql server 2005

今天搞了好久終於連上mssql
首先我是用xampp1.7.1
載點 http://sourceforge.net/projects/xampp/files/

安裝後發現本身就有開啟php_mssql但連線總是失敗，出現:
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server:

後來找了很久更新一個檔案就成功ntwdblib.dll，
上網找下載後先停止apache將檔案複製到apache/bin中，再啟動就成功。

如果還是不行:
1. 開啟php.ini檔後，將extension=php_mssql.dll 前面的註解拿掉。
2. 將C:\AppServ\php5\ntwdblib.dll 檔案複製到
  C:\Windows\System32 底下。
3.將C:\AppServ\php5\ext\php_mssql.dll 檔案複製到
  C:\Windows\System32 底下。
4.重新啟動Apache即可。

// mssql.secure_connection = Off 改為on


http://hi.baidu.com/mvp_xuan/item/795d6d52bd4acb3094eb05f8

【MSSQL】Limit分頁

參考網址:http://www.dotblogs.com.tw/jimmyyu/archive/2009/11/09/11499.aspx
http://paco8822.pixnet.net/blog/post/52232214-ms-sql-%E5%88%86%E9%A0%81%E6%9F%A5%E8%A9%A2

user表的Structure:
id,name,tel,address

要抓30-40:
SQL2011、SQL Server CE4.0

SELECT * FROM user
OFFSET 30 ROWS
FETCH NEXT 10 ROWS ONLY;

之前的版本:

SELECT TOP 10 id,name FROM user
WHERE id not in
(SELECT TOP 30 id FROM user ODER BY id ASC)

ODER BY id ASC