那位SQL达人能解这个？？

AmandaX

原帖由 happysee 于 2010-4-14 11:46 发表
我刚才测试了一下, 2楼和3楼得到的是一样的结果,执行计划也相同. 2 楼的语法简洁,3楼的更能清楚的表明意图.

一样的结果? no way
testing data 有问题了
accountid       recorddate                  price
1               1/1/10 11:11:11                1
2               1/1/10 11:11:11                2
2               1/2/10 11:11:11                3

试这个

jingsun

我试了一下，确实都能执行。

可能是习惯不一样吧，向来认为那样引用是错的，但好像MSSQL足够聪明。

TuLaLa

If it's MS SQL 2005 or above, Rank() is very handy

SELECT * FROM
(
        SELECT
                AccountId,
                RecordDate,
                Price,
                Rank() over(PARTITION BY AccountId, RecordDate ORDER BY RecordDate DESC) AS Rank
        FROM
                TABLE_NAME
)
WHERE
        Rank = 1

AmandaX

这个厉害了
学习一下

zn7726

一样的结果? no way
testing data 有问题了

二楼那个执行的没错呀

AmandaX

ms是我错了 lol

zn7726

zn7726的错误和pk没关系
是逻辑错误
再想想
哈哈

想得浑身是汗, 没想明白, 救救我吧

happysee

33楼提供了新思路. 里面有两个type error. 修改后的版本:

SELECT * FROM
(
        SELECT
                AccountId,
                RecordDate,
                Price,
                Rank() over(PARTITION BY AccountId ORDER BY RecordDate DESC) AS Rank
        FROM
                tableName
) t
WHERE
        Rank = 1

aperson

haven't written SQL for a while, I hope I got it right.
be careful of rank() function. if got two value with the same date, their rank is the same.

If it is SQL 2005 above

WITH STAGING AS
(
    SELECT acccountID, price, ROW_NUMBER()  OVER (PARTITION BY acccountID ORDER BY recordDate DESC) AS 'RowNumber'
)
SELECT acccountID, price FROM STAGING WHERE RowNumber = 1


If it is above Oracle 9, can't remember oracle syntax. valid it using google

SELECT unique acccountID, LAST_VALUE(price) OVER (PARTITION BY acccountID ORDER BY recordDate) AS latest_price

[ 本帖最后由 aperson 于 2010-4-14 12:13 编辑 ]

stevenbian

select distinct accountid,first_value(price) over (partition by accountid order by recordDate d
esc) from table_name;

happysee

2楼3楼和33楼的返回结果是一样的. 估计33楼的效率稍微低点.

北风

别听他们的，你没错

原帖由 zn7726 于 14/4/2010 12:02 发表

想得浑身是汗, 没想明白, 救救我吧

乱码

如果这个table有id（normal practice都应该有）,这是典型的group 求最大/最小的scenario.

2个最简单的solution，也有其他的，但这两个在tsql commnunity最为提倡:

1. top 1 （这个不太常用，说一下）

select * from TABLE_NAME t1
where t1.id in
(select top 1 id from TABLE_NAME t2
where t1.account_ID=t2.account_id
order by account_id,recordDate desc)

2. 用cte,这个最常见，都滥街了，就不说了。

北风

2楼3楼一样的逻辑，效率也是一样的

zn7726

如果是oracle就用rowid吧, 对于LZ这种没PK的情况, 至少保证只返回最后插入(oracle认为)的那个

select * from table t
where t.rowid = (select max(tt.rowid) from table tt where t.accountID = tt.accountID)

这个效率应该很高, 因为oracle最高效的就是通过rowid操作记录.

ps, LZ给出的wenxuecity那个达人的方案, 也不能解决无PK的情况, 但是人家至少考虑到了这种可能, 够严谨的.

典

原帖由 zn7726 于 2010-4-14 10:57 发表
澳贼你没事吧? 是不是当初database的作业你是抄别人的?

select * from TABLE_NAME t
where t.recordDate = (select max(tt.recordDate) from TABLE_NAME tt where t.accountID = tt.accountID)

如果RecordDate有相同的(同一个Customer）， 这个查询是错误的

典

原帖由 TuLaLa 于 2010-4-14 11:56 发表
If it's MS SQL 2005 or above, Rank() is very handy

SELECT * FROM
(
        SELECT
                AccountId,
                RecordDate,
                Price,
                Rank() over(PARTITION BY AccountId, RecordDate ORDER BY RecordDate DESC) AS Ra ...

如果RecordDate有相同的(同一个Customer）， 这个查询是错误的

典

原帖由 乱码 于 2010-4-14 12:13 发表
如果这个table有id（normal practice都应该有）,这是典型的group 求最大/最小的scenario.
2个最简单的solution，也有其他的，但这两个在tsql commnunity最为提倡:

1. top 1 （这个不太常用，说一下）
select * from TABLE_NAME t1
where t1.id in
(select top 1 id from TABLE_NAME t2
where t1.account_ID=t2.account_id
order by account_id,recordDate desc)

执行效率会非常差，因为要反复地调用同一个表

典

原帖由 aperson 于 2010-4-14 12:08 发表
haven't written SQL for a while, I hope I got it right.
be careful of rank() function. if got two value with the same date, their rank is the same.
WITH STAGING AS
(
    SELECT acccountID, price, ROW_NUMBER()  OVER (PARTITION BY acccountID ORDER BY recordDate DESC) AS 'RowNumber'
)
SELECT acccountID, price FROM STAGING WHERE RowNumber = 1

我觉得这个最好

TuLaLa

原帖由 典 于 2010-4-14 13:07 发表


如果RecordDate有相同的(同一个Customer）， 这个查询是错误的

I think LZ asked for the latest price based on RecordDate. If records have the same recordDate, that means both should be reported. Otherwise, the query is not accurate and complete.

典

原帖由 TuLaLa 于 2010-4-14 13:13 发表


I think LZ asked for the latest price based on RecordDate. If records have the same recordDate, that means both should be reported. Otherwise, the query is not accurate and complete.

有道理，

乱码

原帖由 TuLaLa 于 2010-4-14 13:13 发表


I think LZ asked for the latest price based on RecordDate. If records have the same recordDate, that means both should be reported. Otherwise, the query is not accurate and complete.

一般这个table都有row identity的，lz没有交代清楚，在实际工作中，这种是很罕见的design flaw.我们有必要提醒他一下。

乱码

原帖由 典 于 2010-4-14 13:10 发表


执行效率会非常差，因为要反复地调用同一个表

没说他是最优的，比cte会差，但是从逻辑上来说是最简单的solution之一。

典

LZ 快来说明
有没有rowid?
有没有同一天多条Record(同一Customer)? 如果有，要不要全部显示?
要不要考虑效率？ 如果要， 多少条记录在表里？有Index吗？

TuLaLa

原帖由 典 于 2010-4-14 13:30 发表
LZ 快来说明
有没有rowid?
有没有同一天多条Record(同一Customer)? 如果有，要不要全部显示?
要不要考虑效率？ 如果要， 多少条记录在表里？有Index吗？

Hehe, let's give LZ a break. You guys have offered a lot of ideas here and I think it good enough for him to solve his issue.

北风

不是说了吗？accountID和recordDate是PK
一天多条record也没关系啊，那是datetime
效率肯定不用考虑太多了，2，3楼的效率就不错

zn7726

LZ 快来说明
有没有rowid?

唉, oracle里都有rowid, oracle自己的东西.

LZ是没事闲的, 大家别太当真了

zn7726

不是说了吗？accountID和recordDate是PK
一天多条record也没关系啊，那是datetime

我当初就是这么想的, 后来私下和LZ沟通, 被告知这表没PK 所以说我的答案不对.

degra

原帖由 happysee 于 2010-4-14 11:28 发表

不行,select里面的必须是group by里面有的,或者是 aggregation

mysql 里是可以的，记得oracle 也可以，不过没用过 mssql

degra

原帖由 gandu 于 2010-4-14 14:12 发表


mysql 里是可以的，记得oracle 也可以，不过没用过 mssql

刚试了一下, price果然是出错:

mysql> desc records;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| accountID | varchar(10) | YES  |     | NULL    |       |
| dateline  | int(11)     | YES  |     | NULL    |       |
| price     | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> select * from records;
+-----------+----------+-------+
| accountID | dateline | price |
+-----------+----------+-------+
| 1         |  2010410 |   555 |
| 1         |  2010411 |   666 |
| 2         |  2010412 |   777 |
| 2         |  2010413 |   888 |
+-----------+----------+-------+
4 rows in set (0.00 sec)

mysql> select accountID, max(dateline),price from records group by accountID;
+-----------+---------------+-------+
| accountID | max(dateline) | price |
+-----------+---------------+-------+
| 1         |       2010411 |   555 |
| 2         |       2010413 |   777 |
+-----------+---------------+-------+
2 rows in set (0.00 sec)

[ 本帖最后由 gandu 于 2010-4-14 14:23 编辑 ]