sql(复杂)

SELECT A,B,"COUNT"(*) FROM 表 GROUP BY A,B

SELECT "MIN"(id) ,A,B,"COUNT"(*) FROM 表 WHERE ** GROUP BY A,B

单个

SELECT person_id ,COUNT(*)FROM t22000_member_points_info GROUP BY person_id HAVING COUNT(*)>1
group by 之后不能用where 要用having

2.删除ID不同其他字段都相同的数据

delete tablename where ID not in(select min( ID) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

3.多个字段in查询

单表

select * from SRO_ORDER where (FIRST_BIZ_ID,SECOND_BIZ_ID) in ((500,505),(502,503));

select * from b where exists ( select * from a where a.aaa=b.aaa and a.bbb=b.bbb);

// 多表

select * from SUPPLIER_UNIT_PRICE where (FIRST_BIZ_ID,SECOND_BIZ_ID) in (select BUSINESS_CATEGORY_ID,BUSINESS_SUBCLASS_ID from BUSINESS_RELATIONSHIP WHERE id = 500);

4.分组查询前N个

通用写法

select * from SUPPLIER_UNIT_PRICE t1 WHERE (select COUNT(*) from SUPPLIER_UNIT_PRICE t2 where t2.SUPPLIER_ID = t1.SUPPLIER_ID AND t2.PRICE >= t1.PRICE ) < 3 ORDER BY SUPPLIER_ID DESC

mysql写法一

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)

oracle写法一 (采用rownum)

ROWNUM和ORDER BY的优先级比较是要分情况的，order by 索引，主键时，即可让oracle先按该字段排序，然后再用rownum标号；order by 普通列时，先rownum后order by

(SELECT * FROM (select * from SUPPLIER_UNIT_PRICE where SUPPLIER_ID = '512' order by PRICE DESC) WHERE rownum <=3)
union all
(SELECT * FROM (select * from SUPPLIER_UNIT_PRICE where SUPPLIER_ID = '661' order by PRICE DESC) WHERE rownum <=3)

<if test="list !=null and list.size >0">
<foreach item="item" index="index" collection="list" separator="union all" >
( SELECT * FROM (select * from SUPPLIER_UNIT_PRICE where SUPPLIER_ID = #{item.id} order by PRICE DESC) WHERE rownum <= 3 )
</foreach>
</if>

5.select distinct 后面跟left jion 分页可能会导致数据重复问题

必须有主表中唯一键 order by

6.left jion 统计统计右表与右表每条数据的关联数据

SELECT
    t.ID,
    to_char( t.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss' ),
    t.TITLE,
    t.ACCOUNT_ID,
    e.USER_NOTESNAME,
    t.INVITED_USERS,
    e.USER_DEPTPATH,
    t.VIEWS,
  (SELECT COUNT(*) FROM DOC_COLLECTOR dc WHERE dc.DOC_ID = t.ID) as DOC_COLLECTOR,
    (SELECT COUNT(*) FROM DOC_SHARE ds WHERE ds.DOC_ID = t.ID) as DOC_SHARE,
    (SELECT COUNT(*) FROM DOC_EFFECTIVE de WHERE de.DOC_ID = t.ID AND de.EFFECTIVE_FLAG=1) as DOC_EFFECTIVE1,
    (SELECT COUNT(*) FROM DOC_EFFECTIVE de WHERE de.DOC_ID = t.ID AND de.EFFECTIVE_FLAG=0) as DOC_EFFECTIVE0
FROM
    DOCUMENT t
    LEFT JOIN v_emp_asset @oahr e ON t.INVITED_USERS = e.user_shortname
WHERE
    t.CREATE_DATE > to_date( '2018-08-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss' )
ORDER BY
    t.CREATE_DATE DESC

7.查询两个时间间隔的小时数

ROUND(TO_NUMBER(b.SIGNIN_TIME - b.SUPPLIER_TIME) * 24,2)

8.按照拼音排序

举例如下：
表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。
//按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
//按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
//按照拼音排序，此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');

9.left join关联表可以添加条件

select * from SRO_ORDER so

left join ORDER_SERIAL_NUMBER osn ON so.ORDER_CODE = osn.ORDER_CODE and osn.DELETE_STATUS =0

10.delete和left join 结合使用

指定删除哪张表不然有错误
DELETE g
FROM
    t${tenantId}_contactlinkgroup g
        LEFT JOIN t${tenantId}_contact c ON c.id = g.contactid
WHERE
    g.groupid = #{groupId}
  AND c.identity = #{identity}

11.更新和jion结合使用

UPDATE t12818_member t1
INNER JOIN (SELECT * FROM (SELECT
   a.member_id,
   a.growth_log,
   m.growth,
   c.real_name
FROM
   (
       SELECT
           l.member_id,
           SUM(l.exchange_growth) AS growth_log
       FROM
           t12818_member_growth_log l
       GROUP BY
           l.member_id
   ) a
LEFT JOIN t12818_member m ON m.id = a.member_id
LEFT JOIN t12818_contact c ON m.id = c.member_id
WHERE
   a.growth_log != m.growth) a) t2 ON t2.member_id   = t1.id
SET t1.growth = t2.growth_log

12.求日期的间隔天数

SELECT DATEDIFF(maxDate,minDate) div num FROM (SELECT MAX(create_time)as maxDate, MIN(create_time) as minDate ,COUNT(*)as num FROM t12818_order WHERE contact_id = 63357) a

13.删除表的时候禁用外键索引

SET FOREIGN_KEY_CHECKS = 0;

14.sql字符串替换

UPDATE t12818_short_link_long SET long_url = REPLACE(long_url,'imageTextGroup','2222') WHERE id = 21
中替换函数 REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。