526互联

MS SQL Server SQL刷题记录

发布时间 2023-03-31 16:30:15作者: yhm138

MS SQL Server SQL方言
和mysql sql略有不同

目录

保留4位小数

SELECT CAST(ROUND(SUM(LAT_N), 4) AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;


SELECT CAST(ROUND(LAT_N, 4) AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N > 38.7780 
ORDER BY LAT_N ASC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

SELECT CAST(ROUND(LONG_W, 4) AS DECIMAL(10, 4))
FROM STATION
WHERE LAT_N < 137.2345
ORDER BY LAT_N DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

求曼哈顿距离和欧几里得距离

with t1 as(
select 
    min(LAT_N) as a,
    min(LONG_W) as b,
    max(LAT_N) as  c,
    max(LONG_W) as d
from station)


select  CAST(ROUND(abs(a-c)+abs(b-d), 4) AS DECIMAL(10, 4))
from t1

with t1 as(
select 
    min(LAT_N) as a,
    min(LONG_W) as b,
    max(LAT_N) as  c,
    max(LONG_W) as d
from station)


select  CAST(ROUND(sqrt(abs(a-c)*abs(a-c)+abs(b-d)*abs(b-d)), 4) AS DECIMAL(10, 4))
from t1

求中位数

SELECT  CAST(ROUND(PERCENTILE_CONT(0.5) , 4) AS DECIMAL(10, 4))
WITHIN GROUP (ORDER BY LAT_N) AS median_value
FROM station;

SELECT CAST(ROUND(AVG(x), 4) AS DECIMAL(10, 4))
                   AS Median
FROM (
    SELECT TOP 50 PERCENT LAT_N AS x
    FROM STATION
    ORDER BY LAT_N
) AS y

WITH NumberedRows AS (
  SELECT LAT_N, ROW_NUMBER() OVER (ORDER BY LAT_N) AS RowNum
  FROM STATION
)
SELECT CAST(ROUND(AVG(LAT_N), 4) AS DECIMAL(10, 4)) AS Median
FROM (
  SELECT LAT_N, RowNum, COUNT(*) OVER () AS TotalRows
  FROM NumberedRows
) Subquery
WHERE CASE 
  WHEN TotalRows % 2 = 0 THEN 
    CASE 
      WHEN RowNum = TotalRows / 2 THEN 1
      WHEN RowNum = TotalRows / 2 + 1 THEN 1
      ELSE 0
    END
  ELSE 
    CASE 
      WHEN RowNum = (TotalRows + 1) / 2 THEN 1
      ELSE 0
    END
  END = 1


with up as
    (
        select 
            lat_n,
            row_number() over(order by lat_n desc) as nr_desc,
            row_number() over(order by lat_n) as nr_asc
        from station
    )
select 
    CAST(ROUND(lat_n, 4) AS DECIMAL(10, 4))  as median
from up
where nr_desc = nr_asc

表联结

with t1 as(
    select s.name,g.grade,s.marks
    from Students  s
    left join Grades g
    on s.marks>=g.min_mark and s.marks<=g.max_mark
)

select 
(case when grade<=7 then null else name end) as name,
grade,
marks
from t1
order by grade desc,name asc

表联结，我真心建议给你的列命名的时候不要瞎命名

一次提交的提交者和一道试题的创建者你不要都用hacker_id字段啊
一次提交的分数和一道试题的满分分数你不要都用score字段啊

-- Can you name the columns in the table properly???

with t1 as(
select h.hacker_id, h.name,
s.submission_id,s.challenge_id,s.score,
c.difficulty_level,c.hacker_id as creator_id,
d.score as full_score
from hackers h
left join submissions s
on h.hacker_id=s.hacker_id
left join challenges c
on s.challenge_id=c.challenge_id
left join difficulty d
on d.difficulty_level=c.difficulty_level
)
,
t2 as(
select  hacker_id,name,challenge_id,submission_id
from t1
where score=full_score)
,
t3 as (
select hacker_id,name,challenge_id,count(*) as full_score_cnt
from t2
group by hacker_id,name,challenge_id)

select hacker_id,name
from t3
group by hacker_id,name
having count(distinct challenge_id)>=2
order by count(distinct challenge_id) desc, hacker_id asc