SQL/ORACLE相关问题记录

1.SQL 自定义将星期一作为本周的第一天

代码如下（示例）：

select  getdate() as today,
		case when datepart(weekday,getdate()) in (2,3,4,5,6,7) and 
				  DATEPART(WEEKday,datename(year,getdate())+'-01-01') = 1  then datepart(week,getdate()) +1
		          
			 when datepart(weekday,getdate()) =1  and  
				  DATEPART(WEEKday,datename(year,getdate())+'-01-01') = 1  then datepart(week,getdate()) 
		     
			 when datepart(weekday,getdate()) in (2,3,4,5,6,7) then datepart(week,getdate()) 
			 else datepart(week,getdate())  -1 
		end  as week 
/*
today	week
2017-03-15 10:55:53.507
*/

2.ORACLE 报错：ORA-01476: divisor is equal to zero

分析：出现这个问题是因为sql语句中使用除法，且除数为0的情况下导致的
代码如下（示例）：

应该修改为：

(CASE
    WHEN sum(P.数量) = 0 THEN 0
    ELSE (sum(P.不含税采购额)/sum(P.数量)) 
    END) 单价

3.ORACLE 报错：ORA-12704: character set mismatch

分析：在做两个select语句的union all的过程中，爆出了这个问题，这两个语句单独执行都能顺利执行，经查找发现是两部分查询结果字段对应类型不一致导致的

解决方案： 使用to_char函数做字符转换，将nvarchar2转为varchar2
代码如下（示例）：

select 
to_char(d.FNAME_L2) as 物料名称
from T_BD_MATERIAL

参考链接：https://blog.youkuaiyun.com/Alen_Liu_SZ/article/details/90542935

4.SQL 设置大小写敏感语句

--设置表内某字段大小写敏感

alter table 表名

alter column 列名 varchar(44) collate chinese_prc_cs_as

5.SQL 查询将计算当前日期所在周的起始日期和结束日期

--SQL查询将计算当前日期所在周的起始日期和结束日期：

DECLARE @Today DATE = GETDATE()

SELECT  DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0) AS StartOfWeek,
        DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0)) AS EndOfWeek;

在SQL中，可以使用WEEK和YEAR函数获取当前日期所在的周和年份。然后，可以使用DATEADD函数和日期格式化函数来计算周的第一天和最后一天。

首先，通过DATEDIFF函数获取当前日期和1900-01-01日期之间的周数差。因为1900-01-01是一个星期一，所以0表示星期一，1表示星期二，以此类推。因此，DATEDIFF(WEEK, 0, @Today)将返回当前日期所在的周数。

然后，将周数作为参数传递到DATEADD函数中，以获取当前周的第一天。DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0)将返回当前周的第一天，也就是该周的星期一。

最后，使用DATEADD函数和DATEFORMAT函数来计算当前周的最后一天。DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0))将返回当前周的最后一天，也就是该周的星期日。

6.SQL 将日期类型转换为字符串,截取字符串中间部分，拼接字符串

concat(substring(CONVERT(varchar(100), GETDATE(), 5),4,2)
concat()  --拼接函数
substring()  --截取函数

7.SQL 计算日期之间的时间间隔天数

--在SQL Server中，可以使用DATEDIFF函数来计算两个日期之间的月份差。该函数的语法如下：
DATEDIFF(datepart, startdate, enddate)

--其中，datepart参数指定要计算的日期部分，可以是year、quarter、month、day、hour、minute、second等。startdate和enddate参数分别是要计算的日期范围的起始日期和结束日期。
DECLARE @DATE1 DATE
DECLARE @DATE2 DATE
SET @DATE1 = '2020-01-01'
SET @DATE2 = '2021-01-01'

SELECT DATEDIFF(MONTH, @DATE1, @DATE2) AS MonthDifference

执行上述代码后，将返回两个日期之间的月份差。在这个例子中，结果为12，表示从2020年1月到2021年1月之间有12个月的差距。1

以下是一个示例，演示如何计算两个日期之间的月份差：

-- 时间间隔天数
SELECT DATEDIFF(day,'2023-05-29','2023-05-30') AS days;
结果为：1

8.SQL 计算当前时间加上间隔天数得到的日期

--当前时间加上间隔数
select CONVERT(VARCHAR(10),dateadd(day,间隔天数,当前时间),120)
select CONVERT(VARCHAR(10),dateadd(day,3,'2023-04-29'),120) 
结果为：2023-05-02

9.SQL 为查询到的结果添加序列号

--添加序列号
ROW_NUMBER() OVER (ORDER BY 列名) AS ID

--例子,获取这周的日期区间，为查找的数据添加序号
SELECT 
ROW_NUMBER() OVER (ORDER BY DAY_SHORT_DESC) AS ID,
WEEK,
DAY_SHORT_DESC
FROM dim_calendar
WHERE WEEK='23' AND YEAR_ID='2023'

10.SQL 创建时间维度表

--日期区间内获得相应日期
WITH A AS (
	SELECT CONVERT
		( NVARCHAR ( 10 ), DateAdd( DAY, number, '2014-01-01' ), 120 ) AS DAY_SHORT_DESC 
	FROM
		master..spt_values 
	WHERE
		type = 'p' 
		AND number <= DateDiff( DAY, '2014-01-01', '2018-12-31' ) 
	) 
SELECT
	ROW_NUMBER() OVER (ORDER BY DAY_SHORT_DESC) AS ID
	,concat(LEFT(DAY_SHORT_DESC,4),substring(DAY_SHORT_DESC,6,2),right(DAY_SHORT_DESC,2)) as DAY_ID
	,DAY_SHORT_DESC
	,concat(LEFT(DAY_SHORT_DESC,4),'年',substring(DAY_SHORT_DESC,6,2),'月',right(DAY_SHORT_DESC,2),'日') as DAY_LONG_DESC
	,datename(weekday,DAY_SHORT_DESC) as WEEK_DESC
	,concat(LEFT(DAY_SHORT_DESC,4),substring(DAY_SHORT_DESC,6,2)) as MONTH_ID
	,LEFT(DAY_SHORT_DESC,7) AS YEARMON
	,concat(LEFT(DAY_SHORT_DESC,4),'年第',substring(DAY_SHORT_DESC,6,2),'月') as MONTH_LONG_DESC
-- 季度
	,case when substring(DAY_SHORT_DESC,6,2) in ('01','02','03') then concat(LEFT(DAY_SHORT_DESC,4),1) 
		    when substring(DAY_SHORT_DESC,6,2) in ('04','05','06') then concat(LEFT(DAY_SHORT_DESC,4),2) 
				when substring(DAY_SHORT_DESC,6,2) in ('07','08','09') then concat(LEFT(DAY_SHORT_DESC,4),3) 
				else concat(LEFT(DAY_SHORT_DESC,4),4) 
	end QUARTER_ID
	,case when substring(DAY_SHORT_DESC,6,2) in ('01','02','03') then concat(LEFT(DAY_SHORT_DESC,4),'年第',1,'季度') 
		    when substring(DAY_SHORT_DESC,6,2) in ('04','05','06') then concat(LEFT(DAY_SHORT_DESC,4),'年第',2,'季度') 
				when substring(DAY_SHORT_DESC,6,2) in ('07','08','09') then concat(LEFT(DAY_SHORT_DESC,4),'年第',3,'季度') 
				else concat(LEFT(DAY_SHORT_DESC,4),'年第',4,'季度') 
	end QUARTER_LONG_DESC
		,LEFT(DAY_SHORT_DESC,4) AS YEAR_ID
	,concat(LEFT(DAY_SHORT_DESC,4),'年') AS YEAR_LONG_DESC
	--星期	
,		case when datepart(weekday,DAY_SHORT_DESC) in (2,3,4,5,6,7) and 
				  DATEPART(WEEKday,datename(year,DAY_SHORT_DESC)+'-01-01') = 1  then datepart(week,DAY_SHORT_DESC) +1
		          
			 when datepart(weekday,DAY_SHORT_DESC) =1  and  
				  DATEPART(WEEKday,datename(year,DAY_SHORT_DESC)+'-01-01') = 1  then datepart(week,DAY_SHORT_DESC) 
		     
			 when datepart(weekday,DAY_SHORT_DESC) in (2,3,4,5,6,7) then datepart(week,DAY_SHORT_DESC) 
			 else datepart(week,DAY_SHORT_DESC)  -1 
		end  as WEEK
--半年度
 	,case when substring(DAY_SHORT_DESC,6,2) in ('01','02','03','04','05','06') then concat(LEFT(DAY_SHORT_DESC,4),6) 
 				else concat(LEFT(DAY_SHORT_DESC,4),7) 
 	end HALF_QUARTER_ID
	,case when substring(DAY_SHORT_DESC,6,2) in ('01','02','03','04','05','06') then concat(LEFT(DAY_SHORT_DESC,4),'年上半年') 
 				else concat(LEFT(DAY_SHORT_DESC,4),'年下半年') 
 	end HALF_QUARTER
FROM
	A

10.SQL 获取第一条记录

1.对于MySQL和SQLite，可以使用LIMIT关键字来限制结果集的大小，并使用ORDER BY子句指定排序方式。

SELECT * FROM table_name ORDER BY column_name LIMIT 1;

2.对于Oracle，可以使用ROWNUM关键字来限制结果集的大小，并使用ORDER BY子句指定排序方式。

SELECT * FROM (SELECT * FROM table_name ORDER BY column_name) WHERE ROWNUM = 1;

3.对于SQL Server，可以使用TOP关键字来限制结果集的大小，并使用ORDER BY子句指定排序方式。

SELECT TOP 1 * FROM table_name ORDER BY column_name;

11.SQL SERVER 将表A一列的数据更新到表B中

/*其中，column_name是要更新的列名，key_column是连接两个表的关键列名。*/
UPDATE B
SET B.column_name = A.column_name
FROM A
WHERE A.key_column = B.key_column

12.SQL SERVER 统计数据出现的次数

/*其中，column_name是要更新的列名，key_column是连接两个表的关键列名。*/
SELECT 字段[1],字段[2], COUNT(*) as count
FROM 表名
GROUP BY 字段[1],字段[2]
HAVING COUNT(*) > 1;

13.SQL Server查询数据时遇到含有空格的字段导致不匹配的问题

/*SQL Server查询数据时遇到含有空格的字段导致不匹配的问题*/
SELECT *
FROM your_table
WHERE LTRIM(RTRIM(your_column)) = LTRIM(RTRIM('需要匹配的值'));

这里的your_table是你的数据表名，your_column是可能含有空格的字段名。LTRIM()函数去除左边空格，RTRIM()函数去除右边空格，确保两边的空格被去除后进行比较。

14.SQL Server查询数据时字段既包含中文又包含数字，只保留中文

将做了变成右列格式，只保留中文

/*SQL Server查询数据时字段既包含中文又包含数字，只保留中文*/
SELECT 
       field_name,
       (SELECT SUBSTRING(field_name, number, 1)
        FROM master.dbo.spt_values
        WHERE type = 'P' AND number BETWEEN 1 AND LEN(field_name)
          AND UNICODE(SUBSTRING(field_name, number, 1)) BETWEEN 19968 AND 40959
        ORDER BY number
        FOR XML PATH('')) AS chinese_text
FROM your_table;

15.SQL server 进行除法运算的时候不进行四舍五入，保留原有小数位

/*在 SQL Server 中，当你进行除法运算并期望结果保持原始小数位而不限于四舍五入的情况下，你需要确保运算的结果被正确地存储为浮点类型（如 decimal 或 float）。这是因为整数除法 (int / int) 通常会得到整数结果，即使分母不为零，商也是作为整数显示。

例如，如果你想避免将 decimal 类型的结果向下取整，你可以这样操作：*/
SELECT CAST(your_integer_column AS decimal(18, X)) / your_divisor_column
FROM your_table;
/*这里的 X 是你希望保留的小数位数。例如，如果你的原数据可能有两位小数，你可以设 X = 2。这样，如果分母导致结果小于 1，小数部分也会被保留*/

15.Spark SQL 获取数组里面的值

SELECT regexp_replace(field_iM2Af__c, '[\\[\\]]', '') AS ExtractedValues,
  field_iM2Af__c
FROM $[数据过滤];

SELECT 
  regexp_replace(regexp_replace(field_iM2Af__c, '[\\[\\]]', ''), '"', '') AS ExtractedValues,
  field_iM2Af__c
FROM $[数据过滤];
/*第一个 regexp_replace(field_iM2Af__c, '[\\[\\]]', '') 用于去除方括号。
第二个 regexp_replace(..., '"', '') 用于去除双引号。*/