b********r
发帖数: 59 | 1 不好意思，题目描述的有点晕
有如下的table A，希望得到每种Title中Age最大的人的Name
Name Age Title
xxx 35 Engineer
yyy 25 Engineer
zzz 40 Manager
我是数据库新手，能想到的办法比较笨:
select Name from A inner join
(select DISTINCT Title, MAX(Age) AS Age from A GROUP BY Title) AS B
on A.Age=B.Age and A.Title=B.Title
问题是
(1)如果有两个Name不同，但Age，Title相同的人
INNER JOIN 能否同时得到这两个Name？
(2)如果我还有where clause来限制这个表（还有其他field）
应该如何优化?放进中间的Select里面还是放在最后？
(3)是否有更好的办法完成这个query？
谢谢！ |
B*****g
发帖数: 34098 | 2 1. yes. But why not test yourself to confirm your sql. btw, I think distinct
is unnecessary.
2. Create temp table use for both?
3. I will do this in oracle
SELECT NAME
FROM (SELECT NAME,
RANK () OVER (PARTITION BY title ORDER BY age DESC) AS
agetitlerank
FROM table)
WHERE agetitlerank = 1
【在 b********r 的大作中提到】
: 不好意思，题目描述的有点晕
: 有如下的table A，希望得到每种Title中Age最大的人的Name
: Name Age Title
: xxx 35 Engineer
: yyy 25 Engineer
: zzz 40 Manager
: 我是数据库新手，能想到的办法比较笨:
: select Name from A inner join
: (select DISTINCT Title, MAX(Age) AS Age from A GROUP BY Title) AS B
: on A.Age=B.Age and A.Title=B.Title
|
b********r
发帖数: 59 | 3 多谢回答！昨晚在家联不上数据库，只好凭印象写了
今天在研究一下T-Sql是否有类似你3中的写法。
distinct
【在 B*****g 的大作中提到】
: 1. yes. But why not test yourself to confirm your sql. btw, I think distinct
: is unnecessary.
: 2. Create temp table use for both?
: 3. I will do this in oracle
: SELECT NAME
: FROM (SELECT NAME,
: RANK () OVER (PARTITION BY title ORDER BY age DESC) AS
: agetitlerank
: FROM table)
: WHERE agetitlerank = 1
|
b********r
发帖数: 59 | 4 今天试了一下你的建议3，在T-SQL里面完全不用修改:D
查询时间由用join的20几秒降到3秒以内，太感谢啦！
distinct
【在 B*****g 的大作中提到】
: 1. yes. But why not test yourself to confirm your sql. btw, I think distinct
: is unnecessary.
: 2. Create temp table use for both?
: 3. I will do this in oracle
: SELECT NAME
: FROM (SELECT NAME,
: RANK () OVER (PARTITION BY title ORDER BY age DESC) AS
: agetitlerank
: FROM table)
: WHERE agetitlerank = 1
|
j*****n
发帖数: 1781 | 5 PARTITION BY ?
never try it with T-SQL, it might be new in 2k5...
en, I am somewhat out of date...
your DISTINCT is the performance killer, definitely no necessary in your
case.
【在 b********r 的大作中提到】
: 今天试了一下你的建议3，在T-SQL里面完全不用修改:D
: 查询时间由用join的20几秒降到3秒以内，太感谢啦！
:
: distinct
|
j*****n
发帖数: 1781 | 6 can you try this? just want to see how it's performance will be.
this is the old method if we can not use PARTITION BY as Beijing said.
should not as good performance as Beijing's. here are two killers: Order BY
and DISTINCT...
SELECT
(SELECT TOP 1 name FROM A WHERE A.title = B.title ORDER BY age) AS Name
FROM
(SELECT DISTINCT title FROM A) AS B
【在 b********r 的大作中提到】
: 不好意思，题目描述的有点晕
: 有如下的table A，希望得到每种Title中Age最大的人的Name
: Name Age Title
: xxx 35 Engineer
: yyy 25 Engineer
: zzz 40 Manager
: 我是数据库新手，能想到的办法比较笨:
: select Name from A inner join
: (select DISTINCT Title, MAX(Age) AS Age from A GROUP BY Title) AS B
: on A.Age=B.Age and A.Title=B.Title
|
B*****g
发帖数: 34098 | 7 不知道这个是搞成了standard sql了，还是M$抄的。
【在 b********r 的大作中提到】
: 今天试了一下你的建议3，在T-SQL里面完全不用修改:D
: 查询时间由用join的20几秒降到3秒以内，太感谢啦！
:
: distinct
|
B*****g
发帖数: 34098 | 8 晕。还new in 2005呢，2008都快出来了。哈哈
【在 j*****n 的大作中提到】
: PARTITION BY ?
: never try it with T-SQL, it might be new in 2k5...
: en, I am somewhat out of date...
: your DISTINCT is the performance killer, definitely no necessary in your
: case.
|
j*****n
发帖数: 1781 | 9 come on, we are in upgrade plan to 2005...will done by end this year...
usually no big firms except M$ like to upgrade to the newest tech before the
market tested it as reliable...
e.g. one of our app is using 2k5... we just had 2k5 memory leak problem
causes serious production issue, fortunately it is still under pre-running,
otherwise we will lose lots of clients... it is billion $$ of assets in
which the system holds...
【在 B*****g 的大作中提到】
: 晕。还new in 2005呢，2008都快出来了。哈哈
|
B*****g
发帖数: 34098 | 10 嘿嘿，俺们还是oracle9i
the
,
【在 j*****n 的大作中提到】
: come on, we are in upgrade plan to 2005...will done by end this year...
: usually no big firms except M$ like to upgrade to the newest tech before the
: market tested it as reliable...
: e.g. one of our app is using 2k5... we just had 2k5 memory leak problem
: causes serious production issue, fortunately it is still under pre-running,
: otherwise we will lose lots of clients... it is billion $$ of assets in
: which the system holds...
|
|
|
j*****n
发帖数: 1781 | 11 yeap, just checked Books Online, it is new in 2k5 :)
DB2 has similar function too.
maybe it is copied from Oracle, just like the table partitioning, hehe
【在 B*****g 的大作中提到】
: 不知道这个是搞成了standard sql了，还是M$抄的。
|
j*****n
发帖数: 1781 | 12 悟空你又淘气了 ^L^
【在 B*****g 的大作中提到】
: 嘿嘿，俺们还是oracle9i
:
: the
: ,
|
b********r
发帖数: 59 | 13 收到，不过周末连不上数据库了，
周一试过之后再来报告。
BY
【在 j*****n 的大作中提到】
: can you try this? just want to see how it's performance will be.
: this is the old method if we can not use PARTITION BY as Beijing said.
: should not as good performance as Beijing's. here are two killers: Order BY
: and DISTINCT...
: SELECT
: (SELECT TOP 1 name FROM A WHERE A.title = B.title ORDER BY age) AS Name
: FROM
: (SELECT DISTINCT title FROM A) AS B
|
c**t
发帖数: 2744 | 14 公司越大，这种更新越慢
【在 B*****g 的大作中提到】
: 晕。还new in 2005呢，2008都快出来了。哈哈
|
j*****n
发帖数: 1781 | 15 最近碰到了一个类似的问题，组里面就俺做出来了，两种方法，一个2000下得TOP 1,
另一个就是Beijing 牛人给的了，在2005下面果然用得很好。
用execution plan quote了一下，59:41, old vs. new
呵呵，小小地得意一下....
distinct
【在 B*****g 的大作中提到】
: 1. yes. But why not test yourself to confirm your sql. btw, I think distinct
: is unnecessary.
: 2. Create temp table use for both?
: 3. I will do this in oracle
: SELECT NAME
: FROM (SELECT NAME,
: RANK () OVER (PARTITION BY title ORDER BY age DESC) AS
: agetitlerank
: FROM table)
: WHERE agetitlerank = 1
|
B*****g
发帖数: 34098 | 16 DBA也开始写sql了？
【在 j*****n 的大作中提到】
: 最近碰到了一个类似的问题，组里面就俺做出来了，两种方法，一个2000下得TOP 1,
: 另一个就是Beijing 牛人给的了，在2005下面果然用得很好。
: 用execution plan quote了一下，59:41, old vs. new
: 呵呵，小小地得意一下....
:
: distinct
|
j*****n
发帖数: 1781 | 17 俺是 Application DBA, 当然得写。
最近正在研究 MS SQL Server 2005 System Catalog Views(SCVs) and Dynamic
Management Views (DMVs), 嗯嗯，有那么点挑战性。
赶明儿能整出个 automated and dynamic table partitioning, 到时候俺也小牛一把
，呵呵。
【在 B*****g 的大作中提到】
: DBA也开始写sql了？
|
W*******r
发帖数: 15 | 18 I have no indea of those advanced technologies you guyes use. But I think it
's quite easy to solve this prob.
SELECT Max_Age = max(Age), Title
INTO #max_ages
FROM #employee
GROUP BY Title
GO
SELECT a.*
FROM #employee a,
#max_ages b
WHERE a.Age = b.Max_Age
AND b.Title = b.Title
GO |
b********r
发帖数: 59 | 19 Yes it works. But from what I understand, the temp table
resides in temp db, so when the table is huge, a lot of
I/O will be incurred (not the case the in the given example).
Also there could be potential lock issue.
BTW, does anybody know if there is a practical guideline about
when to use local temp table, global temp table or table
variable? There are some articles discussing the diff among
them but how about in the real world?
it
【在 W*******r 的大作中提到】
: I have no indea of those advanced technologies you guyes use. But I think it
: 's quite easy to solve this prob.
: SELECT Max_Age = max(Age), Title
: INTO #max_ages
: FROM #employee
: GROUP BY Title
: GO
: SELECT a.*
: FROM #employee a,
: #max_ages b
|
W*******r
发帖数: 15 | 20 tempdb..temptalble resides in tempdb. #temptable resides in the same db. |
|
|
Z*****l
发帖数: 14069 | 21 I would always use row_number() instead of rank() to avoid potential tie
problem.
distinct
【在 B*****g 的大作中提到】
: 1. yes. But why not test yourself to confirm your sql. btw, I think distinct
: is unnecessary.
: 2. Create temp table use for both?
: 3. I will do this in oracle
: SELECT NAME
: FROM (SELECT NAME,
: RANK () OVER (PARTITION BY title ORDER BY age DESC) AS
: agetitlerank
: FROM table)
: WHERE agetitlerank = 1
|
B*****g
发帖数: 34098 | 22 read the original question again, I think tie is needed. I seldom use row_number() in work, because for tie we are always asked to take the highest PK one
【在 Z*****l 的大作中提到】
: I would always use row_number() instead of rank() to avoid potential tie
: problem.
:
: distinct
|
Z*****l
发帖数: 14069 | 23 u r right
number() in work, because for tie we are always asked to take the highest PK
one
【在 B*****g 的大作中提到】
: read the original question again, I think tie is needed. I seldom use row_number() in work, because for tie we are always asked to take the highest PK one
|
B*****g
发帖数: 34098 | 24 number(), rank(), dense_rank... 的performance应该区别不大吧，从来没比过，下
次比一下
PK
【在 Z*****l 的大作中提到】
: u r right
:
: number() in work, because for tie we are always asked to take the highest PK
: one
|
j*****n
发帖数: 1781 | 25 sure lot of times temp table can make things easier...
however, when one step can solve the problem, why should you go for two?
also, temp table has to go back and forth to disk storage more than once.
another point is that when this system is highly transactional, your batch
includes two transactions, and then might have risk that another transaction
(s) can between those two.
it
【在 W*******r 的大作中提到】
: I have no indea of those advanced technologies you guyes use. But I think it
: 's quite easy to solve this prob.
: SELECT Max_Age = max(Age), Title
: INTO #max_ages
: FROM #employee
: GROUP BY Title
: GO
: SELECT a.*
: FROM #employee a,
: #max_ages b
|
j*****n
发帖数: 1781 | 26 in my point of view, both temp table and table variable actually all need
access to physical disk, so performance wide is not a big difference.
however, for temp table you can do more (indexes?, keys, restrictions, etc)
than table variable. commonly suggested is that use temp table store larger
amount of data and use table variable for less.
global temp table is not a suggested method for resolving your problem, at
least I never use it. I use stage table at most time instead.
【在 b********r 的大作中提到】
: Yes it works. But from what I understand, the temp table
: resides in temp db, so when the table is huge, a lot of
: I/O will be incurred (not the case the in the given example).
: Also there could be potential lock issue.
: BTW, does anybody know if there is a practical guideline about
: when to use local temp table, global temp table or table
: variable? There are some articles discussing the diff among
: them but how about in the real world?
:
: it
|
j*****n
发帖数: 1781 | 27 damn, this is another new function in 2k5....
sounds like an Oracle person can easily take over my job... //sigh
【在 Z*****l 的大作中提到】
: I would always use row_number() instead of rank() to avoid potential tie
: problem.
:
: distinct
|
B*****g
发帖数: 34098 | 28 they don't know top1，哈哈
【在 j*****n 的大作中提到】
: damn, this is another new function in 2k5....
: sounds like an Oracle person can easily take over my job... //sigh
|
x***e
发帖数: 2449 | 29 try this with self join.
group by may not necessary be the best idea:
SELECT cp.title
, cp.name
, cp.age
FROM tbl_test AS cp WITH (NOLOCK)
WHERE (
SELECT count(*)
FROM tbl_test AS cr WITH (NOLOCK)
WHERE cr.title = cp.title
AND cr.age > cp.age
) = 0
【在 b********r 的大作中提到】
: 不好意思，题目描述的有点晕
: 有如下的table A，希望得到每种Title中Age最大的人的Name
: Name Age Title
: xxx 35 Engineer
: yyy 25 Engineer
: zzz 40 Manager
: 我是数据库新手，能想到的办法比较笨:
: select Name from A inner join
: (select DISTINCT Title, MAX(Age) AS Age from A GROUP BY Title) AS B
: on A.Age=B.Age and A.Title=B.Title
|
