SQL语法

with语句查询树形结构

string cteSql = WITH org("Id","No","Name","ParentId","LeaderId","Leader","Comment","Sort","OrgTypeId","OrgType","CreateUser","CreateTime","UpdateUser","UpdateTime") AS 
               (select "Id","No","Name","ParentId","LeaderId","Leader","Comment","Sort","OrgTypeId","OrgType","CreateUser","CreateTime","UpdateUser","UpdateTime" FROM "ZTHROrgUnit" WHERE "Id"='1231id' 
              UNION ALL 
              SELECT z."Id",z."No",z."Name",z."ParentId",z."LeaderId",z."Leader",z."Comment",z."Sort",z."OrgTypeId",z."OrgType",z."CreateUser",z."CreateTime",z."UpdateUser",z."UpdateTime" FROM "ZTHROrgUnit" z INNER JOIN org o ON z."ParentId" = o."Id")  
              SELECT * FROM org ORDER BY "ParentId";


qry = db.ZTHROrgUnit.SqlQuery(cteSql).AsQueryable(); // .ToList();

oracle 救命语句（物理删除数据恢复）

SELECT * FROM “表名” AS OF TIMESTAMP SYSDATE  -3/1440     3分钟

Oracle排序

https://www.cnblogs.com/yeys/p/7647819.html

https://www.iteye.com/blog/wallimn-1956636

https://www.cnblogs.com/sooner/p/7727242.html

字段拼接

'>' || a."MaterielCatalog_1_Code" || '>' || a."MaterielCatalog_2_Code" || '>' || a."MaterielCatalog_3_Code" || '>' as "MaterielCatalogFullCode",
    '>' || a."MaterielCatalog_1_Name" || '>' || a."MaterielCatalog_2_Name" || '>' || a."MaterielCatalog_3_Name" || '>' as "MaterielCatalogFullName",

树形结构拼接

SELECT  A."Id",
         A."Code",
         A."Name",
         A."FatherId",
         A."FinancialCode",
         A."SortOrder",
         A."Comment",
         A."CreateUser",
         A."CreateTime",
         A."UpdateUser",
         A."UpdateTime",
         CASE CONNECT_BY_ISLEAF WHEN 1 THEN 'FALSE' ELSE 'TRUE' END "HasChild",
         SYS_CONNECT_BY_PATH(A."Code", '>') || '>' "FullCode",
         SYS_CONNECT_BY_PATH(A."Name", '>') || '>' AS "FullName",
         Level AS "CatalogLevel"
    FROM ZTZS_ERPBASE."MaterialCatalog" A
   START WITH A."FatherId" IS NULL
 CONNECT BY PRIOR A."Id" = A."FatherId"
   ORDER SIBLINGS BY A."SortOrder"

删除数据库

use  master
go
drop database ef

分组取最大N条记录方法

数据库:ORACLE 9I以上
 
--建表语句
create table t2 ( 
    id int primary key, 
    gid    char, 
    col1    int, 
    col2    int 
) ;
 
insert into t2 values (1,'A',31,6);
insert into t2 values (2,'B',25,83);
insert into t2 values (3,'C',76,21); 
insert into t2 values (4,'D',63,56);

--SQL
select *
  from (select id,
               gid,
               col1,
               col2,
               rank() over(partition by gid order by col2 desc) as r_col2
          from t2) tt2
 where tt2.r_col2 = 1--1置换成:N
 order by id
 
-- 当N=1时候,可以这样
 select t2.*
   from t2, (select gid, max(col2) as m_col2 from t2 group by gid) tt2
  where t2.gid = tt2.gid
    and t2.col2 = tt2.m_col2 order by id
    
    --你用这个绝对可以
    select * from (select * from table order by A,C desc) as r group by A

--其他
select a.* from t2 a  join (select gid,max(col2) col2 from t2 group by gid) b
on a.gid=b.gid and a.col2=b.col2;


select * from t2 where concat(gid,col2) in(select concat(gid,max(col2)) from t2 group by gid);

https://bbs.csdn.net/topics/330021260?list=4217982

https://bbs.csdn.net/topics/340185297?list=5509771

Oracle关于如何获取分组排序后的第一条数据

--方式1
SELECT t1.*,t2.nums FROM "PS_QualityMargin" t1,
(
    SELECT b."ProjectNo",b."SupplierNo", COUNT(*) nums, MAX("Date") "dt" FROM "PS_QualityMargin" b GROUP BY b."ProjectNo",b."SupplierNo"
) t2
WHERE t1."ProjectNo" = t2."ProjectNo" AND t1."Date" = t2."dt";



--方式2
SELECT * FROM 
(
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY "ProjectNo","SupplierNo" ORDER BY "Date" DESC) rn,COUNT(*) OVER(PARTITION BY "ProjectNo","SupplierNo") nums FROM "PS_QualityMargin" t
)
WHERE rn = 1;


---推荐使用方式2，row_number()是比rownum更强大的伪列。

Oracle where条件，当天时间段

https://blog.csdn.net/qq_34803742/article/details/76082447

Oracle 循环sql

https://blog.csdn.net/henni_719/article/details/79695559

https://blog.csdn.net/wk1134314305/article/details/60762449

--17. 招聘地：RecruitmentPlace;工作地：WorkPlace;险种缴纳地：InsurancePaymentPlace，编码初始化；
--a.遍历所有记录；
--b.嵌套便利，所有城市：根据城市名和城市名去掉最后一个字，匹配数据，两者之一匹配上，则获取相应的省，再以相同规则匹配相应的省，如也能匹配，则取改记录，并反填数据；
declare
    x number;
    y NUMBER;
begin
  x:=0;
  y:=2622;
  while x<y loop   --while关键字提供循环的条件。loop关键字开始循环。
    x:=x+1;
    --b.嵌套便利，所有城市：根据城市名和城市名去掉最后一个字，匹配数据，两者之一匹配上，则获取相应的省，再以相同规则匹配相应的省，如也能匹配，则取改记录，并反填数据；
     declare
     num number := 0;
     begin
       loop 
         exit when num = 10;
         num := num + 1;
         dbms_output.put_line(num);   
       end loop;
     end;
    end loop;
    dbms_output.put_line('end loop x='||x);
end;

DECLARE s_sql clob:='';
begin
FOR wf IN (SELECT * FROM "zthremptemp")
  loop
    s_sql:=wf."Name";
    dbms_output.put_line(s_sql);
  END loop;
end;

oracle判断是否包含字符串的方法

1.contains，contains用法如下：

select * from students where contains(address, ‘beijing’)

但是，使用contains谓词有个条件，那就是列要建立索引，也就是说如果上面语句中students表的address列没有建立索引，那么就会报错。

2.instr，instr用法如下：

select * from students where instr(address, ‘beijing’) > 0

3.like,like用法如下：

select * from students where address like ‘%beijing%’

【Oracle】锁表处理 SQL 错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

https://www.cnblogs.com/Zeros/p/6907675.html

SQL语法

Oracle Update语句 Oracle中的 UPDATE FROM 解决方法

Oracle关于如何获取分组排序后的第一条数据

Oracle where条件，当天时间段

oracle判断是否包含字符串的方法

【Oracle】锁表处理 SQL 错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效