MS SQL 使用Rowid 更新資料方法

--Form: http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

Jan 4, 2006

Row_Number() function in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

As we all know, SQL Server 2005 has new features when compared to SQL Server 2000. One of the features that we are going to discuss in this article is the Row_Number() function. SQL Server Database administrators and developers have been longing for this function for a long time--now the wait is over.
Traditionally developers and Database administrators used temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers.
Let us assume that we have the following database [EMPLOYEE TEST] and the following table [EMPLOYEE] in the database. You can use the below query to create the database, table and all the corresponding rows.

USE [MASTER]
GO
IF  EXISTS 
  (SELECT NAME FROM SYS.DATABASES WHERE NAME = N'EMPLOYEE TEST')
DROP DATABASE [EMPLOYEE TEST]
GO
CREATE DATABASE [EMPLOYEE TEST]
GO
USE [EMPLOYEE TEST]
GO
IF  EXISTS 
  (SELECT * FROM SYS.OBJECTS 
  WHERE OBJECT_ID = 
    OBJECT_ID(N'[DBO].[EMPLOYEE]') AND TYPE IN (N'U'))
DROP TABLE [DBO].[EMPLOYEE]
GO
CREATE TABLE EMPLOYEE (EMPID INT, FNAME VARCHAR(50),
LNAME VARCHAR(50))
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (2011111, 'ADAM', 'ACKERMAN')
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE  (EMPID, FNAME, LNAME) 
VALUES (1021710, 'MARIAH', 'MANDEZ')
GO

Let us browse the table Employee by using the following SQL Query.

SELECT EMPID, FNAME, LNAME FROM EMPLOYEE

The results of the above query look like illustration 1.0.

2021110	MICHAEL	POLAND
2021110	MICHAEL	POLAND
2021115	JIM	KENNEDY
2121000	JAMES	SMITH
2011111	ADAM	ACKERMAN
3015670	MARTHA	LEDERER
1021710	MARIAH	MANDEZ

Illustration 1.0

Traditionally in SQL Server 2000, in order to create row numbers based on the rows available in a table, we used to use the following query.

SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME 
INTO EMPLOYEE2 FROM EMPLOYEE ORDER BY EMPID

This query created a new table using the identity function in order to generate RowId.
Let us query the table by using the following query.

SELECT RowID, EMPID, FNAME, LNAME FROM EMPLOYEE2

The results of the above query would look like illustration 1.1.

1	1021710	MARIAH	MANDEZ
2	2011111	ADAM	ACKERMAN
3	2021110	MICHAEL	POLAND
4	2021110	MICHAEL	POLAND
5	2021115	JIM	KENNEDY
6	2121000	JAMES	SMITH
7	3015670	MARTHA	LEDERER

Illustration 1.1

In this illustration it is clear that the table has a duplicate row with EMPID = 2021110.
To delete the duplicate row with EMPID = 2021110, we have to delete the row in employee2 table and I cannot delete the duplicate row directly from the Employee table.
SQL Server 2005 provides a new function, Row_Number(), for generating row numbers. In order to delete the duplicate row from the original table we can use the features, Common Table Expression and Row_Number() together.
Let us generate the ROWID using the Row_Number() function based on EMPID.

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

The results of the above query would look like illustration 1.2.

1	1021710	MARIAH	MANDEZ
2	2011111	ADAM	ACKERMAN
3	2021110	MICHAEL	POLAND
4	2021110	MICHAEL	POLAND
5	2021115	JIM	KENNEDY
6	2121000	JAMES	SMITH
7	3015670	MARTHA	LEDERER

Illustration 1.2

In this result set, we can identify the duplicate row for the EMPID 2021110.
Let us display the duplicate row using the Common Table expression and Row_Number() function by using the following query.

WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4

The results of the above query would look like illustration 1.3.

4

2021110

MICHAEL

POLAND

Illustration 1.3

This duplicate row can be deleted using the Common Table expression and Row_Number() function by using the following query.

WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
DELETE FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4

Let us query the table using the following query.

SELECT * FROM EMPLOYEE

The results of the above query would look like illustration 1.4.

2021110	MICHAEL	POLAND
2021115	JIM	KENNEDY
2121000	JAMES	SMITH
2011111	ADAM	ACKERMAN
3015670	MARTHA	LEDERER
1021710	MARIAH	MANDEZ

Illustration 1.4

In this illustration, we can see that the duplicate row has been deleted.

Conclusion

In this article, we have discussed the new features of Row_Number() function and Common Table Expression and used both the features together to delete duplicate rows.
» See All Articles by Columnist MAK

what is difference between wmsys.wm_concat and ListAgg

This thread is to discussion what is difference between wmsys.wm_concat and ListAgg for 11G r2. 

*************************************************************************
difference1 

wmsys.wm_concat allows distinct option.
ListAgg does not allows it.

create table diffT(sortKey,Val) as
select 1,'aa' from dual union all
select 2,'bb' from dual union all
select 3,'aa' from dual union all
select 4,'dd' from dual;
 
col concatV for a20
 
select wmsys.wm_concat(distinct Val) as concatV from diffT;
 
concatV 
--------
aa,bb,dd

*************************************************************************
difference2 

ListAgg allows to decide string concat order.
wmsys.wm_concat does not allows it.

select ListAgg(Val,',')
       within group(order by sortKey desc) as concatV
from diffT;
 
CONCATV
------------
dd,aa,bb,aa 

*************************************************************************
difference3 

ListAgg allows to decide delimiter.
wmsys.wm_concat does not allows it.

select ListAgg(Val,'***')
       within group(order by sortKey desc) as concatV
from diffT;
 
CONCATV
-----------------
dd***aa***bb***aa

*************************************************************************
difference4 

wmsys.wm_concat allows to be used OLAP function with order by
ListAgg does not allows it.
ListAgg allows only OLAP function without order by.

select sortKey,wmsys.wm_concat(Val)
               over(order by sortKey) as concatV
  from diffT;
 
SORTKEY  CONCATV
-------  -----------
      1  aa
      2  aa,bb
      3  aa,bb,aa
      4  aa,bb,aa,dd

*************************************************************************
difference5 

wmsys.wm_concat allows to be used KEEP
ListAgg does not allows it.

select wmsys.wm_concat(Val) 
       Keep(Dense_Rank First order by Val) as concatV 
  from diffT;
 
CONCATV
-------
aa,aa

wmsys.wm_concat 將資料列轉行

SELECT   code, wmsys.wm_concat (col1) combine
          FROM table
      GROUP BY  code

Ms Sql 重複資料查詢與刪除

--檢查重複排序
WITH Get_Last_cmd
AS
(
  SELECT
    *,GroupID = ROW_NUMBER() OVER (PARTITION BY dbowner,custid,facisno ORDER BY cmopendate desc)
  FROM
    dbo.table
)
Select * FROM Get_Last_cmd order by facisno,cmopendate desc;

 --刪除重復
 WITH Get_Last_cmd
AS
(
  SELECT
    GroupID = ROW_NUMBER() OVER (PARTITION BY dbowner,custid,facisno ORDER BY cmopendate desc)
  FROM
    dbo.table
)
delete * FROM Get_Last_cmd WHERE GroupID > 1;

2010/12 聖誕水晶球

聖誕水晶球

2010-07-25 08.56.20

巧遇 Reach 電動版，也聽了主人解說功能特性，長知識

oracle 日期計算

1	日期運算
2
3	1. 更改日期顯示的format
4	ex.
5	ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
6	階段作業已被更改
7
8	select sysdate from dual;
9
10	SYSDATE
11	----------
12	2007/09/20
13
14	--只對目前session有效，一個 connect 視為一個 session
15
16	2. 日期 + 數值
17	ex.
18	select sysdate + 10 from dual;
19
20	SYSDATE+10
21	----------
22	01-OCT-07
23
24	3. 日期 - 數值
25	ex.
26	select sysdate - 10 from dual;
27
28	SYSDATE-10
29	----------
30	11-SEP-07
31
32	4. 日期相減得到日期差
33	ex.
34	select sysdate - to_date('20070901','yyyymmdd') aa from dual;
35
36	AA
37	-------------
38	20.4508218
39
40	--◎ 包含時間，所以有小數
41	--◎ 可做日期欄位的計算
42
43	select trunc(sysdate - to_date('20070901','yyyymmdd')) aa from dual;
44
45	AA
46	----------
47	20
48	--使用trunc取整數，得到日期
49
50	5. 日期相減得到小時差
51	ex.
52	select trunc((sysdate - to_date('20070901','yyyymmdd'))*24) aa from dual;
53
54	AA
55	----------
56	490
57
58	6. 日期相減得到分鐘差
59	ex.
60	select trunc((sysdate - to_date('20070901','yyyymmdd'))*24*60) aa from dual;
61
62	AA
63	---------
64	29459
65
66	7. 日期相減得到秒數差
67	ex.
68	select trunc((sysdate - to_date('20070901','yyyymmdd'))*24*60*60) aa from dual;
69
70	AA
71	----------
72	1767606
73
74	8. 日期 + n小時
75	ex.
76	select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') aa from dual;
77
78	AA
79	--------------------
80	2007/09/21 11:03:47  --系統時間
81
82	select to_char(sysdate+2/24,'YYYY/MM/DD HH24:MI:SS') aa from dual;
83
84	AA
85	--------------------
86	2007/09/21 13:03:47  --加2小時(理論值)
87
88	9. 日期 + n分鐘
89	ex.
90	select to_char(sysdate+10/1440,'YYYY/MM/DD HH24:MI:SS') aa from dual;
91
92	AA
93	--------------------
94	2007/09/21 11:13:47  --加10分鐘(理論值)
95
96	10. 日期+ n秒鐘
97	ex.
98	select to_char(sysdate+10/86400,'YYYY/MM/DD HH24:MI:SS') aa from dual;
99
100	AA
101	--------------------
102	2007/09/21 11:13:57  --加10秒鐘(理論值)