实际项目中一些实用SQL

1.case when

SELECT
	A .GUID AS PROD_GUID,
	A .GDNAME,
	A .GZCZNO,
	A .PRODUCER,
  (CASE A.GUID 
			WHEN '222E836FC33B477F8AB22CDE1565724A' THEN '已生成' 
			ELSE '新增' END)
	as status
FROM
	XT_PRODUCT_FBZ A,DOC_OTHER_LIC o
WHERE
	A .GUID IN ('222E836FC33B477F8AB22CDE1565724A','333E836FC33B477F8AB22CDE1565724A','444E836FC33B477F8AB22CDE1565724A')
GROUP BY
	A .GUID,
	A .GDNAME,
	A .GZCZNO,
	A .PRODUCER
ORDER BY
	A .GDNAME

2.查询嵌套表数据

SELECT
	i.*
FROM
	doc_other_lic D,
	TABLE (D .imgpath) I
WHERE
	d.guid='333DA5A53C444942A613430982868354'

3.在嵌套表中插入数据

INSERT INTO THE (
	SELECT
		IMGPATH
	FROM
		DOC_OTHER_LIC
	WHERE
		guid = '333DA5A53C444942A613430982868354'
)
VALUES
	(
		'https://ss2.baidu.com/6ONYsjip0QIZ8tyhnq/it/u=2507907452,1782106577&fm=58',
		'',
		'2',
		'3',
		'4',
		'5'
	);

4.查询所有嵌套证照类型和每一种的数量

SELECT
	D .lictype AS TYPE,
	COUNT (D .lictype) AS COUNT
FROM
	doc_other_lic D,
	TABLE (D .imgpath) I
WHERE
	D .sup_guid = '1A0774CDEBDA425DBEDA3BBAAF58023E'
AND D .hosp_guid = '2333609FBB4442239A5A285462C39FB1'
and d.PROD_GUID is null
GROUP BY
	D .lictype;

5.读取某几行的数据（像oracle不提供此功能，一般分页或读取特定行数据时使用）

select * from(
SELECT scg.PUST_GUID,scg.PUST_NAME,rownum no
FROM DOC_SUP_CUST s,DOC_SUP_CUST_GOODS scg,DOC_CHANGE_OTHER_LIC co,DOC_CHANGE_DOC c
WHERE
	co.GUID = '8BC2651DEC474C7C8E0C16BB6574B690'
AND s.HOSP_GUID = c.HOSP_GUID
AND s.SUP_GUID = c.SUP_GUID
AND co.DOC_CHANGEGUID = c.GUID
AND s.GUID = scg.SUP_CUST_GUID
GROUP BY scg.PUST_GUID,scg.PUST_NAME,rownum)where no>1 and no<3;

6.简单存储过程及存储过程中的数组使用

declare 
   TYPE list_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
   testlist list_type;
begin

FOR i IN 1 .. 3 LOOP
select PUST_GUID INTO testlist(i) from(
SELECT scg.PUST_GUID,scg.PUST_NAME,rownum no
FROM DOC_SUP_CUST s,DOC_SUP_CUST_GOODS scg,DOC_CHANGE_OTHER_LIC co,DOC_CHANGE_DOC c
WHERE
	co.GUID = '3AD57E7B3D5C4569ACC588FA38895D92'
AND s.HOSP_GUID = c.HOSP_GUID
AND s.SUP_GUID = c.SUP_GUID
AND co.DOC_CHANGEGUID = c.GUID
AND s.GUID = scg.SUP_CUST_GUID
GROUP BY scg.PUST_GUID,scg.PUST_NAME,rownum)where no>i-1 and no<i+1 ;
END LOOP;
  FOR i IN 1 .. testlist.count LOOP
   Dbms_Output.put_line(testlist(i));
 END LOOP;
end;

7.修改嵌套表数据

UPDATE THE (
	SELECT
		IMGPATH
	FROM
		DOC_OTHER_LIC
)
SET imagepath = '';

8.删除嵌套表数据

DELETE
FROM
	THE (
		SELECT
			IMGPATH
		FROM
			DOC_OTHER_LIC
    WHERE
      guid='78ECAFBF1C144B7384EA6672A125CD81'
	);

9.嵌套表去重

DELETE
FROM
	THE (
		SELECT
			IMGPATH
		FROM
			doc_other_lic
		WHERE
			guid = '78ECAFBF1C144B7384EA6672A125CD80'
	)
WHERE
	ROWID NOT IN (
		SELECT
			MIN (i. ROWID)
		FROM
			doc_other_lic D,
			TABLE (D .imgpath) i
		WHERE
			D .guid = '78ECAFBF1C144B7384EA6672A125CD80'
		GROUP BY
			i.imagepath
	)

10.插入从另一张表查询出的数据

INSERT INTO DOC_CHANGE_OTHER_LIC c (
	c.guid,
	c.licobjtype,
	c.lictype,
	c.memo,
  c.BEGRQ
) 
SELECT
	sys_guid(),
	o.licobjtype,
	o.lictype,
	'CESHI22222222',
  to_date('2015-1-30 12:20','YYYY-MM-DD HH24:MI')
FROM
	DOC_OTHER_LIC o
WHERE
	o.SUP_GUID = '1A0774CDEBDA425DBEDA3BBAAF58023E'
AND o.HOSP_GUID = '2333609FBB4442239A5A285462C39FB1'
AND o.lictype = '合同书'

未完待续。。。