MySQL和Oracle学习到的一些知识点

最新推荐文章于 2024-08-03 16:11:51 发布

热水养鲨鱼

最新推荐文章于 2024-08-03 16:11:51 发布

阅读量166

点赞数

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

本文链接：https://blog.csdn.net/weixin_42477252/article/details/118566180

版权

SQL 专栏收录该内容

2 篇文章

订阅专栏

1.列转行并且用逗号拼接

mysql

//mysql
SELECT
	SYS_ID,
	group_concat(TBL_ID SEPARATOR ',')
FROM
	md_db_table
GROUP BY
	SYS_ID

A	a,b,c,d,e,f

oracle

//oracle
SELECT
	SYS_ID,
	listagg (TBL_ID, ',') within GROUP (ORDER BY TBL_ID)
FROM
	md_db_table
GROUP BY
	SYS_ID;

2.近一周各天统计

mysql

                SELECT
            count(*) AS num,
            Date(
                DATE_SUB(NOW(), INTERVAL 1 DAY)
            ) as date_day
        FROM
            post
        WHERE
            Date(
                DATE_SUB(NOW(), INTERVAL 1 DAY)
            ) = DATE(create_time)
        UNION ALL
            SELECT
                count(*) AS num,
                Date(
                    DATE_SUB(NOW(), INTERVAL 2 DAY)
                ) as date_day
            FROM
                post
            WHERE
                Date(
                    DATE_SUB(NOW(), INTERVAL 2 DAY)
                ) = DATE(create_time)
        UNION ALL
            SELECT
                count(*) AS num,
                Date(
                    DATE_SUB(NOW(), INTERVAL 3 DAY)
                )as date_day
            FROM
                post
            WHERE
                Date(
                    DATE_SUB(NOW(), INTERVAL 3 DAY)
                ) = DATE(create_time)
        UNION ALL
            SELECT
                count(*) AS num,
                Date(
                    DATE_SUB(NOW(), INTERVAL 4 DAY)
                )as date_day
            FROM
                post
            WHERE
                Date(
                    DATE_SUB(NOW(), INTERVAL 4 DAY)
                ) = DATE(create_time)
        UNION ALL
            SELECT
                count(*) AS num,
                Date(
                    DATE_SUB(NOW(), INTERVAL 5 DAY)
                )as date_day
            FROM
                post
            WHERE
                Date(
                    DATE_SUB(NOW(), INTERVAL 5 DAY)
                ) = DATE(create_time)
        UNION ALL
            SELECT
                count(*) AS num,
                Date(
                    DATE_SUB(NOW(), INTERVAL 6 DAY)
                )as date_day
            FROM
                post
            WHERE
                Date(
                    DATE_SUB(NOW(), INTERVAL 6 DAY)
                ) = DATE(create_time)
        UNION ALL
            SELECT
                count(*) AS num,
                Date(
                    DATE_SUB(NOW(), INTERVAL 7 DAY)
                )as date_day
            FROM
                post
            WHERE
                Date(
                    DATE_SUB(NOW(), INTERVAL 7 DAY)
                ) = DATE(create_time)

oracle

SELECT
	last_day(
		trunc (
			add_months (sysdate ,- rownum + 1)
		)
	) AS days
FROM
	DUAL connect BY rownum <= 8

2021-07-31 00:00:00
2021-06-30 00:00:00
2021-05-31 00:00:00
2021-04-30 00:00:00
2021-03-31 00:00:00
2021-02-28 00:00:00
2021-01-31 00:00:00
2020-12-31 00:00:00

3.函数区别

函数区别
date_format(date,'%Y-%m-%d')		to_char()---TO_CHAR(x.days,'yyyy-MM')
str_to_date(date,'%Y-%m-%d')		to_date()
concat(A,B)		A \|\| B
nvl(u.email_address, 10)		IFNULL(u.email_address, 10)
decode(条件,值1,翻译值1 ,值2,翻译值2 ,...值n,翻译值n,缺省值)	IF 条件=值1 THEN　　　 RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) 　　　　...... ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF	SELECT IF(1>2,2,3);
now() / SYSDATE()		SYSDATE