【ORACLE】ORACLE常用语法

---左连接查询
select * from E_LOSSSUMMARY t1
LEFT JOIN SS_MFGLINE sm
ON sm.SS_MFGLINENAME=t1.LINECODE ;
> --复制查询的表数据到新表
CREATE TABLE E_LOSSSUMMARY_NEW AS select * from E_LOSSSUMMARY WHERE LINECODE='H2104';

--指定查询的数据插入到已存在的新表种
INSERT INTO E_LOSSSUMMARY_NEW (LINECODE) SELECT LINECODE FROM E_LOSSSUMMARY el ;

--whit as，将复杂的查询逻辑封装成一个临时的结果集，以便在后续的查询中重复使用。
WITH t1 AS  (select * from E_LOSSSUMMARY el )
SELECT LINECODE  FROM t1;


---聚合函数加条件
SELECT count (*) from E_LOSSSUMMARY el 
GROUP BY LINECODE 
HAVING LINECODE ='H21004';

---查询符合/不查询条件的解果
select * from E_LOSSSUMMARY el WHERE EXISTS (select * from E_LOSSSUMMARY el2  WHERE LINECODE='H2104');

--case when 即if  else语法
select 
CASE 
WHEN LINECODE ='H2104'
THEN '#1'
WHEN LINECODE ='H2204'
THEN '#2'
ELSE ''
END AS Line
from E_LOSSSUMMARY e;

后续继续学习补充