UPDATE _test SET [value]='100' WHERE id = (SELECT TOP 1 id FROM _test WHERE [name]='aa' ORDER BY id DESC) AND [name]='aa'
2021年4月13日 星期二
2017年2月20日 星期一
【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)
2016年5月10日 星期二
【MSSQL】DECLARE 宣告
DECLARE @k nvarchar(255)
SET @k = 'ABC'
SELECT * FROM table WHERE name LIKE '%'+@k+'%'
SET @k = 'ABC'
SELECT * FROM table WHERE name LIKE '%'+@k+'%'
2014年9月17日 星期三
【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;
2014年8月15日 星期五
【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;
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 DESCWITH 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);
2014年8月8日 星期五
【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秒
2014年5月29日 星期四
【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 簡單多了
抓取第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 簡單多了
2014年5月28日 星期三
【MYSQL、MSSQL】複製資料表、複製一筆資料
1.複製一個資料表
MySQL 建議使用下面的方式,好處是 Schema 會一樣
只複製結構 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
MySQL 建議使用下面的方式,好處是 Schema 會一樣
- CREATE TABLE new_table LIKE old_table;
- INSERT new_table SELECT * 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
2013年12月18日 星期三
【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
C:\Windows\System32 底下。
4.重新啟動Apache即可。
// mssql.secure_connection = Off 改為on
http://hi.baidu.com/mvp_xuan/item/795d6d52bd4acb3094eb05f8
首先我是用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
2013年12月13日 星期五
【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
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
訂閱:
文章 (Atom)