IT工程師數位筆記本

---TEST FOR ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
DROP TABLE TEST_Y
CREATE TABLE TEST_Y(
       ID VARCHAR2 (32) PRIMARY KEY ,
       NAME VARCHAR2 (20),
       AGE NUMBER(3 ),
       DETAILS VARCHAR2 (1000)
);
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',20 ,'面朝大海，春暖花開');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',30 ,'面朝大海，春暖花開');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '貝多芬',43 ,'致愛麗絲');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '普希金',34 ,'假如生活欺騙了你');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '楊過',23 ,'黯然銷魂掌');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '小龍女',32 ,'神雕俠侶');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '李清照',21 ,'尋尋覓覓、冷冷清清');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '周芷若',18 ,'峨眉');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '趙敏',18 ,'自由');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '張無忌',20 ,'倚天屠龍記');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '張無忌',30 ,'倚天屠龍記');

SELECT * FROM TEST_Y;


----1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
---查詢所有姓名，如果同名，則按年齡降序
SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

----通過上面的語句可知，ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分組，按AGE字段排序的。
----如果只需查詢出不重復的姓名即可，則可使用如下的語句
SELECT * FROM (SELECT NAME,AGE,DETAILS ,ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY AGE DESC)RN FROM TEST_Y )WHERE RN= 1;

----由查詢結果可知，姓名相同年齡小的數據被過濾掉了；可以使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)對部分子彈進行去重處理


----2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----跳躍排序
SELECT NAME ,AGE,DETAILS , RANK() OVER (PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

----由查詢結果可知，相同的并列，下一個則跳躍到并列所替的序列后：如有兩個并列1，那么下一個則直接排為3，跳過2；

----3.DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----連續排序，當有多個并列時，下一個仍然連續有序

----由查詢結果可知，當兩個并列為1時，下一個仍連續有序為2，不跳躍到3

 Lag和Lead函數可以在一次查詢中取出同一字段的前N行的數據和后N行的值。這種操作可以使用對相同表的表連接來實現，不過使用LAG和LEAD有更高的效率.
  Lag和Lead偏移量函數，其用途是：可以查出同一字段下一個值或上一個值，并作為新列存在表中.
-----4.LAG(exp_str,offset,defval) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回顯示的字段；offset是exp_str字段的偏移量，默認是1，如offset=1表示返回當前exp_str的下一個exp_str；defval當該函數無值可用的情況下返回該值。
(1) SELECT NAME ,AGE,DETAILS, LAG(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_

(2) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

(3) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

----5.LEAD(EXP_STR,OFFSET,DEFVAL) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回顯示的字段；offset是exp_str字段的偏移量，默認是1，如offset=1表示返回當前exp_str的上一個exp_str；
-----defval當該函數無值可用的情況下返回該值。
(1)SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM 

(2) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

(3) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

-----6.SUM(COL1) OVER([PARTITION BY COL2 ] [ORDER BY COL3])
(1) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PART

(2) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PAR

(3)SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( ORDE

(4) SELECT NAME ,AGE,DETAILS, SUM(AGE) OVER ()  FROM TEST_Y;

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序，求和當前行所在順序號的C列所有值
sum(c) over() sum2--無排序，求和 C列所有值

sum() over()

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
  FROM aa;

view sql

SELECT 
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
SC.CheckState,
SC.Notes,
SM.CMSocialMemberID,
SM.MemberID,
SM.MemberName,
SM.MemberIntegral,
SM.EnterTime,
SM.MemberState,
SM.MemberRank,
SM.IsRecommend
FROM (SELECT * FROM CMSocialMember WHERE MemberID=1 AND IsDelete<>1 AND IsAvailable=1) AS SM 
LEFT JOIN CMSocial AS S ON S.CMSocialID=SM.CMSocialID 
LEFT JOIN (
SELECT *
FROM ( 
    SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*  /* 根據 CMSocialID 分組，CreateTime倒序，生成分組內部序號 */
    FROM CMSocialCheck WHERE IsDelete<>1
    ) AS SCsub WHERE SCsub.group_index=1 /*取每個分組內部序號=1 的信息*/
) AS SC ON SC.CMSocialID=S.CMSocialID

select s.*  
from ( 
    select *, row_number() over (partition by [手機號] order by [店鋪]) as group_idx  
    from table_name
) s
where s.group_idx = 1

DECLARE @Status INT;
SET @Status=1;
SELECT  p.CFProjectID AS ID,pc.Status AS StatusID,* FROM dbo.CFProject p
LEFT JOIN (
    select s.*  
    from ( 
        select *, row_number() over (partition by [SourceProjectID] order by [CFProjectID]) as group_idx  
        from dbo.CFProjectToCrowdSpace WHERE [Status]=@Status OR @Status IS NULL
    ) s
    where s.group_idx = 1
) pc ON pc.SourceProjectID=p.CFProjectId
WHERE p.CreatorID=100273  AND p.Status=3  AND p.OrganizationID=180  ORDER  BY p.CreationTime DESC