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