SQL语法修行

大志哥123

已于 2023-03-17 10:14:24 修改

阅读量423

点赞数 1

分类专栏：数据库&SQL 文章标签： sql 数据库 mysql

于 2018-10-11 08:37:02 首次发布

本文链接：https://blog.csdn.net/liuxinzhi1982/article/details/83006296

版权

数据库&SQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

1.case when then else end

案例

SELECT b.F_NAME fRolename,c.F_NAME fDeptname,c.F_DEPTID fDeptid, 
CASE b.F_LEVEL 
WHEN '1' THEN '网点' 
WHEN '2' THEN '支行'
WHEN '3' THEN '市行' 
WHEN '4' THEN '省行' 
ELSE '' END fLevelname, 
a.F_TOKEN,
a.F_ID, a.F_NAME, a.F_PASSWD, a.F_PART, a.F_LEVEL, a.F_CANLOGIN, a.F_DEPT, a.F_RECVER, a.F_TEL, a.F_MOBILE, 
a.F_BP, a.F_EMAIL, a.F_WEBRIGHT, a.F_BLTO, a.F_BLFLAG, a.F_PAD1, a.F_PAD2 
FROM t_users a,t_userrole b,t_branch c 
WHERE a.F_PART = b.F_ROLEID AND a.F_DEPT = c.F_ID AND a.F_ID = 'root'

说明：b.F_LEVEL的值为1，View中fLevelname列值，填入'网点'，以此类推，ELSE填空

2.CONVERT

CONVERT(data_type(length),data_to_be_converted,style)

语句及查询结果：
SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06
SELECT CONVERT(varchar(100), GETDATE(), 2): 06.05.16
SELECT CONVERT(varchar(100), GETDATE(), 3): 16/05/06
SELECT CONVERT(varchar(100), GETDATE(), 4): 16.05.06
SELECT CONVERT(varchar(100), GETDATE(), 5): 16-05-06
SELECT CONVERT(varchar(100), GETDATE(), 6): 16 05 06
SELECT CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
SELECT CONVERT(varchar(100), GETDATE(), 8): 10:57:46
SELECT CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
SELECT CONVERT(varchar(100), GETDATE(), 10): 05-16-06
SELECT CONVERT(varchar(100), GETDATE(), 11): 06/05/16
SELECT CONVERT(varchar(100), GETDATE(), 12): 060516
SELECT CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
SELECT CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
SELECT CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
SELECT CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
SELECT CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
SELECT CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
SELECT CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
SELECT CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
SELECT CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
SELECT CONVERT(varchar(100), GETDATE(), 108): 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
SELECT CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
SELECT CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
SELECT CONVERT(varchar(100), GETDATE(), 112): 20060516
SELECT CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
SELECT CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
SELECT CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49:000

3.CONCAT函数用于将多个字符串连接起来，形成一个单一的字符串。


CONCAT(
SUBSTR(A.F_SPC,1,4),
'-',
SUBSTR(A.F_SPC,5,2),
'-',
SUBSTR(A.F_SPC,7,2),
' ',
SUBSTR(A.F_SPC,9,2),
':',
SUBSTR(A.F_SPC,11,2),
':',
SUBSTR(A.F_SPC,13,2)
)

4.LEFT JOIN 关键字

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

例子表:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

5.MyBatis动态SQL中的trim标签的使用方法

prefix="("，以"("开头，

suffix=")"，以")"结尾，

suffixOverrides="," ，去掉最后一个","

prefixoverride="AND|OR" ,去掉第一个and或者是or

  <insert id="insertSelective" parameterType="map">
    insert into t_mtrans
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="fDate != null">
        F_DATE,
      </if>
      <if test="fTime != null">
        F_TIME,
      </if>
      <if test="fDevid != null">
        F_DEVID,
      </if>
      <if test="fDept != null">
        F_DEPT,
      </if>
      <if test="fTrcd != null">
        F_TRCD,
      </if>
      <if test="fAcno != null">
        F_ACNO,
      </if>
      <if test="fAcno2 != null">
        F_ACNO2,
      </if>
      <if test="fCdno != null">
        F_CDNO,
      </if>
      <if test="fIdno != null">
        F_IDNO,
      </if>
      <if test="fUsrno != null">
        F_USRNO,
      </if>
      <if test="fTram != null">
        F_TRAM,
      </if>
      <if test="fFee != null">
        F_FEE,
      </if>
      <if test="fDevls != null">
        F_DEVLS,
      </if>
      <if test="fRcptno != null">
        F_RCPTNO,
      </if>
      <if test="fChkno != null">
        F_CHKNO,
      </if>
      <if test="fAcctno != null">
        F_ACCTNO,
      </if>
      <if test="fAcctoff != null">
        F_ACCTOFF,
      </if>
      <if test="fRetno != null">
        F_RETNO,
      </if>
      <if test="fStcd != null">
        F_STCD,
      </if>
      <if test="fFlag1 != null">
        F_FLAG1,
      </if>
      <if test="fFlag2 != null">
        F_FLAG2,
      </if>
      <if test="fFlag3 != null">
        F_FLAG3,
      </if>
      <if test="fFlag4 != null">
        F_FLAG4,
      </if>
      <if test="fPad1 != null">
        F_PAD1,
      </if>
      <if test="fMemo != null">
        F_MEMO,
      </if>
      <if test="fTime1 != null">
        F_TIME1,
      </if>
      <if test="fTime2 != null">
        F_TIME2,
      </if>
      <if test="fVideoid != null">
        F_VIDEOID,
      </if>
      <if test="fPermit != null">
        F_PERMIT,
      </if>
      <if test="fInfo != null">
        F_INFO,
      </if>
      <if test="fDate1 != null">
        f_date1,
      </if>
      <if test="fDate2 != null">
        F_DATE2,
      </if>
      <if test="fPrdtype != null">
        F_PRDTYPE,
      </if>
      <if test="fPeriod != null">
        F_PERIOD,
      </if>
      <if test="fOperator != null">
        F_OPERATOR,
      </if>
      <if test="fFile1 != null">
        f_file1,
      </if>
      <if test="fFile2 != null">
        f_file2,
      </if>
      <if test="fFile3 != null">
        f_file3,
      </if>
      <if test="fFlag != null">
        f_flag,
      </if>
      <if test="fPzflag != null">
        F_PZFLAG,
      </if>
      <if test="fUsrname != null">
        F_USRNAME,
      </if>
      <if test="fPhone != null">
        F_PHONE,
      </if>
      <if test="fCdtype != null">
        F_CDTYPE,
      </if>
      <if test="fFmtype != null">
        F_FMTYPE,
      </if>
      <if test="fTerm != null">
        F_TERM,
      </if>
      <if test="fPrint != null">
        F_PRINT,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="fDate != null">
        #{fDate,jdbcType=CHAR},
      </if>
      <if test="fTime != null">
        #{fTime,jdbcType=CHAR},
      </if>
      <if test="fDevid != null">
        #{fDevid,jdbcType=CHAR},
      </if>
      <if test="fDept != null">
        #{fDept,jdbcType=CHAR},
      </if>
      <if test="fTrcd != null">
        #{fTrcd,jdbcType=CHAR},
      </if>
      <if test="fAcno != null">
        #{fAcno,jdbcType=CHAR},
      </if>
      <if test="fAcno2 != null">
        #{fAcno2,jdbcType=CHAR},
      </if>
      <if test="fCdno != null">
        #{fCdno,jdbcType=CHAR},
      </if>
      <if test="fIdno != null">
        #{fIdno,jdbcType=CHAR},
      </if>
      <if test="fUsrno != null">
        #{fUsrno,jdbcType=CHAR},
      </if>
      <if test="fTram != null">
        #{fTram,jdbcType=DECIMAL},
      </if>
      <if test="fFee != null">
        #{fFee,jdbcType=DECIMAL},
      </if>
      <if test="fDevls != null">
        #{fDevls,jdbcType=CHAR},
      </if>
      <if test="fRcptno != null">
        #{fRcptno,jdbcType=CHAR},
      </if>
      <if test="fChkno != null">
        #{fChkno,jdbcType=CHAR},
      </if>
      <if test="fAcctno != null">
        #{fAcctno,jdbcType=CHAR},
      </if>
      <if test="fAcctoff != null">
        #{fAcctoff,jdbcType=CHAR},
      </if>
      <if test="fRetno != null">
        #{fRetno,jdbcType=CHAR},
      </if>
      <if test="fStcd != null">
        #{fStcd,jdbcType=CHAR},
      </if>
      <if test="fFlag1 != null">
        #{fFlag1,jdbcType=CHAR},
      </if>
      <if test="fFlag2 != null">
        #{fFlag2,jdbcType=CHAR},
      </if>
      <if test="fFlag3 != null">
        #{fFlag3,jdbcType=CHAR},
      </if>
      <if test="fFlag4 != null">
        #{fFlag4,jdbcType=CHAR},
      </if>
      <if test="fPad1 != null">
        #{fPad1,jdbcType=CHAR},
      </if>
      <if test="fMemo != null">
        #{fMemo,jdbcType=CHAR},
      </if>
      <if test="fTime1 != null">
        #{fTime1,jdbcType=VARCHAR},
      </if>
      <if test="fTime2 != null">
        #{fTime2,jdbcType=VARCHAR},
      </if>
      <if test="fVideoid != null">
        #{fVideoid,jdbcType=VARCHAR},
      </if>
      <if test="fPermit != null">
        #{fPermit,jdbcType=VARCHAR},
      </if>
      <if test="fInfo != null">
        #{fInfo,jdbcType=VARCHAR},
      </if>
      <if test="fDate1 != null">
        #{fDate1,jdbcType=VARCHAR},
      </if>
      <if test="fDate2 != null">
        #{fDate2,jdbcType=VARCHAR},
      </if>
      <if test="fPrdtype != null">
        #{fPrdtype,jdbcType=VARCHAR},
      </if>
      <if test="fPeriod != null">
        #{fPeriod,jdbcType=VARCHAR},
      </if>
      <if test="fOperator != null">
        #{fOperator,jdbcType=VARCHAR},
      </if>
      <if test="fFile1 != null">
        #{fFile1,jdbcType=VARCHAR},
      </if>
      <if test="fFile2 != null">
        #{fFile2,jdbcType=VARCHAR},
      </if>
      <if test="fFile3 != null">
        #{fFile3,jdbcType=VARCHAR},
      </if>
      <if test="fFlag != null">
        #{fFlag,jdbcType=VARCHAR},
      </if>
      <if test="fPzflag != null">
        #{fPzflag,jdbcType=VARCHAR},
      </if>
      <if test="fUsrname != null">
        #{fUsrname,jdbcType=VARCHAR},
      </if>
      <if test="fPhone != null">
        #{fPhone,jdbcType=VARCHAR},
      </if>
      <if test="fCdtype != null">
        #{fCdtype,jdbcType=VARCHAR},
      </if>
      <if test="fFmtype != null">
        #{fFmtype,jdbcType=VARCHAR},
      </if>
      <if test="fTerm != null">
        #{fTerm,jdbcType=VARCHAR},
      </if>
      <if test="fPrint != null">
        #{fPrint,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>

6.MySQL多表查询遇到的问题和解决方法

6.1内层查询代码

 select
		f_merchantid, f_thirdid, f_transdate, count(*) as
		f_transcount,
		sum(f_ptamt) as f_transamount, 0 as f_refundcount, 0
		as f_refundamount,
		0 as f_3transcount, 0 as f_3transamount, 0 as
		f_3refundcount, 0 as
		f_3refundamount, 0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from t_relation_trans
    as a
		where
		f_ptflag='1' and f_transtype=1 and f_thirdid =
		'07' and f_transdate = '20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		count(*) as f_refundcount, sum(f_ptamt) as
		f_refundamount,
		0 as f_3transcount, 0 as f_3transamount, 0 as
		f_3refundcount, 0 as
		f_3refundamount, 0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a2
		where
		f_ptflag='1' and f_transtype=-1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
		union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		count(*) as
		f_3transcount,sum(f_thirdamt) as f_3transamount,0 as
		f_3refundcount, 0
		as
		f_3refundamount, 0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a3
		where
		f_thirdflag='1' and f_transtype=1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		0 as
		f_3transcount, 0 as f_3transamount,
		count(*) as f_3refundcount,
		sum(f_thirdamt) as f_3refundamount,
		0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a4
		where
		f_thirdflag='1' and f_transtype=-1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		0 as
		f_3transcount, 0 as f_3transamount,
		0 as f_3refundcount, 0 as
		f_3refundamount,
		count(*) as f_histranscount, sum(f_hisamt) as
		f_histransamount,
		0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a5
		where
		f_hisflag='1' and f_transtype=1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
	  union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		0 as
		f_3transcount, 0 as f_3transamount,
		0 as f_3refundcount, 0 as
		f_3refundamount,
		0 as f_histranscount, 0 as f_histransamount,
		count(*) as f_hisrefundcount, sum(f_hisamt) as f_hisrefundamount
		from
		t_relation_trans
    as a6
		where
		f_hisflag='1' and f_transtype=-1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate

预期效果

6.2加上外层查询代码

select
		f_merchantid, f_thirdid, f_transdate,
		sum(f_transcount) as f_transcount,
		sum(f_transamount) as f_transamount, sum(f_refundcount) as f_refundcount,
		sum(f_refundamount) as f_refundamount,
		sum(f_3transcount) as f_3transcount, sum(f_3transamount) as f_3transamount,
		sum(f_3refundcount) as f_3refundcount,
		sum(f_3refundamount) as f_3refundamount, sum(f_histranscount) as f_histranscount,
		sum(f_histransamount) as f_histransamount,
		sum(f_hisrefundcount) as f_hisrefundcount, sum(f_hisrefundamount) as f_hisrefundamount
		from(		
    select
		f_merchantid, f_thirdid, f_transdate, count(*) as
		f_transcount,
		sum(f_ptamt) as f_transamount, 0 as f_refundcount, 0
		as f_refundamount,
		0 as f_3transcount, 0 as f_3transamount, 0 as
		f_3refundcount, 0 as
		f_3refundamount, 0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from t_relation_trans
    as a1
		where
		f_ptflag='1' and f_transtype=1 and f_thirdid =
		'07' and f_transdate = '20190612' group by f_merchantid, f_thirdid, f_transdate
	  union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		count(*) as f_refundcount, sum(f_ptamt) as
		f_refundamount,
		0 as f_3transcount, 0 as f_3transamount, 0 as
		f_3refundcount, 0 as
		f_3refundamount, 0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a2
		where
		f_ptflag='1' and f_transtype=-1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		count(*) as
		f_3transcount,sum(f_thirdamt) as f_3transamount,0 as
		f_3refundcount, 0
		as
		f_3refundamount, 0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a3
		where
		f_thirdflag='1' and f_transtype=1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		0 as
		f_3transcount, 0 as f_3transamount,
		count(*) as f_3refundcount,
		sum(f_thirdamt) as f_3refundamount,
		0 as f_histranscount, 0 as
		f_histransamount, 0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a4
		where
		f_thirdflag='1' and f_transtype=-1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		0 as
		f_3transcount, 0 as f_3transamount,
		0 as f_3refundcount, 0 as
		f_3refundamount,
		count(*) as f_histranscount, sum(f_hisamt) as
		f_histransamount,
		0 as
		f_hisrefundcount, 0 as f_hisrefundamount
		from
		t_relation_trans
    as a5
		where
		f_hisflag='1' and f_transtype=1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate
    union
		select
		f_merchantid, f_thirdid, f_transdate, 0 as f_transcount, 0
		as f_transamount,
		0 as f_refundcount, 0 as f_refundamount,
		0 as
		f_3transcount, 0 as f_3transamount,
		0 as f_3refundcount, 0 as
		f_3refundamount,
		0 as f_histranscount, 0 as f_histransamount,
		count(*) as f_hisrefundcount, sum(f_hisamt) as f_hisrefundamount
		from
		t_relation_trans
    as a6
		where
		f_hisflag='1' and f_transtype=-1 and f_thirdid =
		'07' and f_transdate =
		'20190612' group by f_merchantid, f_thirdid, f_transdate) 
    as b group by b.f_merchantid,b.f_thirdid,b.f_transdate

预期结果：

遇到的问题：

问题1：Every derived table must have its own alias

问题2：需要group by的应用，非聚集字段都要group by

解决方法：

as a1、as a2、as a3、as a4、as a5、as a6、as b

还有group by相关的内容

另外as可以省略，比如：

sum(f_transcount) as f_transcount 改成 sum(f_transcount) f_transcount

as b group by b.f_merchantid,b.f_thirdid,b.f_transdate改成b group by b.f_merchantid,b.f_thirdid,b.f_transdate

7.distinct多个列，查询还想返回其他没有distinct的列怎么办，使用group_concat

原始表:

原来的SQL，不能带上f_merchantid的值

重新写的SQL和效果

大志哥123

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
SQL语法修行

1.case when then else end案例SELECT b.F_NAME fRolename,c.F_NAME fDeptname,c.F_DEPTID fDeptid, CASE b.F_LEVEL WHEN '1' THEN '网点' WHEN '2' THEN '支行'WHEN '3' THEN '市行' WHEN '4' THEN '省行' ELSE ''...
复制链接

扫一扫

专栏目录