由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
JobHunting版 - How to design the sql for this problem? (转载)
相关主题
问个sql问题有面试Amazon 语言用C#的吗?
一道sqlIs anyone familar with OPT extension interview?
初级SQL问题版上牛人能不能帮忙看道面试题?
leetcode Nth Highest Salary 谁做出来了?关于数学表达式解析和求值
请教个面试时遇到的sql 题一道面筋题目~
请教SQL面试题G家已跪,发个面经
新鲜C3 energy面经别把CS神化了
non-pp adv CSC中了问一道G家系统设计题
相关话题的讨论汇总
话题: a1话题: a2话题: date话题: value话题: select
进入JobHunting版参与讨论
1 (共1页)
c***n
发帖数: 921
1
【 以下文字转载自 Database 讨论区 】
发信人: citan (yoyo), 信区: Database
标 题: How to design the sql for this problem?
发信站: BBS 未名空间站 (Thu Dec 10 16:22:40 2009, 美东)
a table fctTbl contains following fields: A1, A2, date, value; the primary
key is . The example "date" value is 20090520 (May 20 2009),
20090601 ....
fctTble has 1 millon rows in it.
The question is, how to get the result set with coloumn A1,A2,value, where
the maximum date rows are returned.
Following code is my solution, and it takes f
H*M
发帖数: 1268
2
?
why cant you
select A1, A2, value, max(date)
from table
group by A1, A2, value
directly?
Did i miss something?

【在 c***n 的大作中提到】
: 【 以下文字转载自 Database 讨论区 】
: 发信人: citan (yoyo), 信区: Database
: 标 题: How to design the sql for this problem?
: 发信站: BBS 未名空间站 (Thu Dec 10 16:22:40 2009, 美东)
: a table fctTbl contains following fields: A1, A2, date, value; the primary
: key is . The example "date" value is 20090520 (May 20 2009),
: 20090601 ....
: fctTble has 1 millon rows in it.
: The question is, how to get the result set with coloumn A1,A2,value, where
: the maximum date rows are returned.

c***n
发帖数: 921
3
If I run the logic you provided, it may return following result:
A1, A2, value, date
c***n
发帖数: 921
4
这样语法会出错。 if you put value in the select clause, you have to put it
in the group by too.
H*M
发帖数: 1268
5
不太清楚你的意思.你想在库中到max(date)的那一行,return那一行?
select *
from table1
where table1.date =
( select max(date) from table2)

【在 c***n 的大作中提到】
: 这样语法会出错。 if you put value in the select clause, you have to put it
: in the group by too.

H*M
发帖数: 1268
6
语法错,
select list必须 全部出现在grouby-list中,除了aggregate functions.
c********g
发帖数: 449
7
my bad:)
how about this:
select t1.a1, t1.a2, t1.value
from table as t1
where date = (select max(date) from table as t2 where t1.a1=t2.a1 and t1.a2=
t2.a2);
maybe run slow:(
A*****i
发帖数: 465
8
Why not use procedure?
Create proc retrieveMaxDate @maxday out
as
set @maxday = (select max(date) from table);
declare @maxday datetime;
exec retrieveMaxDate maxday out
select A1, A2, value, date from table where date = @maxday
s**m
发帖数: 1564
9
try this and let me know if it works well.
select a1, a2, value, row_number() over(partition by a1, a2 order by date
desc) as 'rownumber'
from table
where 1= rownumber
c***n
发帖数: 921
10
a11,a21,v1, 20090221
a11,a21,v2, 20090320
a21,a21,v3, 20090621
a21,a21,v4, 20090820
不同的A1,A2 combination may have different max(date).
In above case, I am expecting to return
a11,a21,v2, 20090320
a21,a21,v4, 20090820

【在 H*M 的大作中提到】
: 不太清楚你的意思.你想在库中到max(date)的那一行,return那一行?
: select *
: from table1
: where table1.date =
: ( select max(date) from table2)

相关主题
请教SQL面试题有面试Amazon 语言用C#的吗?
新鲜C3 energy面经Is anyone familar with OPT extension interview?
non-pp adv CSC中了版上牛人能不能帮忙看道面试题?
进入JobHunting版参与讨论
i****l
发帖数: 135
11
现在没机器验证,但感觉这个应该是对的

【在 s**m 的大作中提到】
: try this and let me know if it works well.
: select a1, a2, value, row_number() over(partition by a1, a2 order by date
: desc) as 'rownumber'
: from table
: where 1= rownumber

q**y
发帖数: 135
12
就用windowing function就行了。
x****r
发帖数: 17
13
也可以试试索引,估计会快不少,当然, 是否适合, 取决于具体应用了
c***n
发帖数: 921
14
This is the final code that runs. Thanks for the idea! As the dabase is db2
Syste i, the SQL grammer may be a little bit different from other databases
.
select a1,a2,value from (
select a1, a2, value, row_number() over(partition by a1, a2 order by date
desc
) as rnber
from table ) end
where rnber=1 ;

【在 s**m 的大作中提到】
: try this and let me know if it works well.
: select a1, a2, value, row_number() over(partition by a1, a2 order by date
: desc) as 'rownumber'
: from table
: where 1= rownumber

c***n
发帖数: 921
15
the problem has been solved. Thank you guys.

【在 c***n 的大作中提到】
: This is the final code that runs. Thanks for the idea! As the dabase is db2
: Syste i, the SQL grammer may be a little bit different from other databases
: .
: select a1,a2,value from (
: select a1, a2, value, row_number() over(partition by a1, a2 order by date
: desc
: ) as rnber
: from table ) end
: where rnber=1 ;

1 (共1页)
进入JobHunting版参与讨论
相关主题
问一道G家系统设计题请教个面试时遇到的sql 题
问一道多线程面试题请教SQL面试题
amazon 面经新鲜C3 energy面经
脸家 System design 新题, 求指点non-pp adv CSC中了
问个sql问题有面试Amazon 语言用C#的吗?
一道sqlIs anyone familar with OPT extension interview?
初级SQL问题版上牛人能不能帮忙看道面试题?
leetcode Nth Highest Salary 谁做出来了?关于数学表达式解析和求值
相关话题的讨论汇总
话题: a1话题: a2话题: date话题: value话题: select