HackerRank笔记 - SQL Server

这篇博客记录了在HackerRank上的SQL Server挑战,涉及易到中等难度的问题。内容包括查询偶数ID的行,取整与四舍五入的方法,画三角形的不同方式,LEFT JOIN的使用,首字母处理,数学函数的运用等。通过实例解析了SQL Server中的基础操作和各种函数应用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


Easy

1. 返回ID为偶数的行 - (id % 2) = 0

select city from station where (id % 2) = 0

Weather Observation Station 3

2. 返回(向上/向下)取整/四舍五入结果

1) ROUND(num,len)
select COUNTRY.Continent,round(avg(CITY.Population),0)
from CITY 
left join COUNTRY
on CITY.CountryCode = COUNTRY.Code
where COUNTRY.Continent is not null
group by COUNTRY.Continent

Average Population of Each Continent

2) CAST(num AS DECIMAL(38,len))
select cast(sum(LAT_N) as decimal(38,2)),cast(sum(LONG_W) as decimal(38,2)) from STATION

Weather Observation Station 2

3) 向上取整CEILING()/ 向下取整FLOOR()
select cast(CEILING(avg(convert(float,salary)) - 
       avg(convert(float,replace(salary,'0','')))) as decimal(38,0)) 
       from EMPLOYEES

The Blunder

4) 整理四舍五入/(向上/向下)取整相关方法 ⚠️

(借助The Blunder的数据)

-- the type of SALARY is INT
select sum(salary),count(salary) from EMPLOYEES
-- >> 80935 20 
-- Then the avg salary should be 4046.75
-- And the avg(salary) returns an INT
select avg(salary) from EMPLOYEES
-- >> 4046
-- Which means it is ignoring the decimals not rounding
select avg(convert(float,salary)) from EMPLOYEES 
-- >> 4046.75 

-- Round up / Round down
select CEILING(avg(convert(float,salary))) from EMPLOYEES 
-- >> 4047.0
select FLOOR(avg(convert(float,salary))) from EMPLOYEES 
-- >> 4046.0

-- Round with some 0s remained
select round(avg(convert(float,salary))) from EMPLOYEES 
-- >> 4047.0
-- CONVERT() it
select convert(int,round(avg(convert(float,salary)),0)) from EMPLOYEES 
-- >> 4047

-- Round
select cast(avg(convert(float,salary))as decimal(38,0)) from EMPLOYEES 
-- >> 4047
-- But CAST(... AS INT) ignores the decimals
select cast(avg(convert(float,salary))as INT) from EMPLOYEES 
-- >> 4046

总结:
1 四舍五入:

  1. CONVERT(INT, ROUND(num, 0))
  2. CAST(num AS DECIMAL(38, len))

2 向上/向下取整:

  1. 向上取整CEILING()/ 向下取整FLOOR()
  2. CAST(num AS INT) (忽略小数 = 向下取整)

3. 画三角形

循环输出 WHILE(con) BEGIN ... END
declare @row int 
declare @begin int, @starline varchar(40), @star char(2)
set @row = 1;
set @star = '*';
while(@row<=20)
    begin
        set @begin = 20;
        set @starline = '';
        while(@begin >= @row)
            begin
                set @starline = @starline + @star;
                set @begin = @begin - 1;
            end
        print @starline;
        set @row = @row + 1;
    end
varchar vs char
  1. varchar(n) 可变长度字符串数据,n不指定时默认为1。
  2. char(n) 固定长度字符串数据,若不足位后面补空字符串,n不指定时默认为1。

Medium

1. 在两个数之间 LEFT JOIN [table] ON A.col BETWEENB.col1 ANDB.col2

select case when grade<8 then null else name end as name, grade, marks
from (select *
from Students S
left join Grades G
on S.marks between G.min_mark and G.max_mark) t
order by grade desc,name

The Report

2. 首字母LEFT(string,num) / 大小写UPPER() LOWER()

select name+'('+left(occupation,1)+')' from OCCUPATIONS order by name;

select 'There are a total of '+convert(varchar,a.num)+' '+lower(a.occupation)+'s.' 
from (select occupation, count(name) num from OCCUPATIONS group by occupation) a
order by num,occupation;

The PADS

3. PIVOT

select min(Doctor), min(Professor),min(Singer),  min(Actor)
from(
select 
ROW_NUMBER() OVER(PARTITION By Doctor,Actor,Singer,Professor order by name asc) AS Rownum, 
case when Doctor=1 then name else Null end as Doctor,
case when Actor=1 then name else Null end as Actor,
case when Singer=1 then name else Null end as Singer,
case when Professor=1 then name else Null end as Professor
from occupations
pivot
( count(occupation)
for occupation 
in(Doctor, Actor, Singer, Professor) ) as p
) temp
group by Rownum  ;

Occupations

PIVOT 语句格式 (摘自MS SQL Docs FROM - Using PIVOT and UNPIVOT):

SELECT <non-pivoted column>,                          --不进行透视的列
    [first pivoted column] AS <column name>,          --透视的列
    [second pivoted column] AS <column name>,         --透视的列
    ...                                               --...
    [last pivoted column] AS <column name>            --透视的列
FROM  
    (<SELECT query that produces the data>)           --数据
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)                 --聚合函数(聚合的列)
FOR   
[<column that contains the values that will become column headers>]   --将作为列名的那列数据
    IN ( [first pivoted column], [second pivoted column],             --透视的列
    ... [last pivoted column])  
) AS <alias for the pivot table>                                      --透视表别名
<optional ORDER BY clause>;                                           --(可选)排序从句

理解:from 之后的才是一个整体,把数据源pivot后再select

4. 数学函数

  1. 绝对值函数ABS(x)和返回圆周率的函数PI()
  2. 平方根函数SQRT(x)
  3. 获取随机函数的函数RAND()RAND(x)
  4. 四舍五入函数ROUND(x,y)
  5. 符号函数SIGN(x)
  6. 获取整数的函数CEILING(x)FLOOR(x)
  7. 幂运算函数POWER(x,y)SQUARE(x)、和EXP(x)
  8. 对数的运算LOG(x)LOG10(x)
  9. 角度与弧度相互转换的函数RANDIANS(x)DEGREES(x)
  10. 正弦函数SIN(x)和反正弦函数ASIN(x)
  11. 余弦函数COS(x)和反余弦函数ACOS(x)
  12. 正切函数TAN(x),反正切函数ATAN(x)和余切函数COT(x)

Sql Server函数全解(二) 数学函数

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值