用MS SQL，这样的查询怎样写？(50分)

F

fwcy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #1

我有一组数据(如下)：
A 2001-01-09
A 2002-01-08
A 2002-12-31
B 2001-09-10
B 2001-10-11
B 2002-12-31
C 2001-11-11
C 2002-12-31
.
.
.
我想查出 A，B，C等中日期最近的前一个日期的（第二大的日期）记录（如下） :
A 2002-01-08
B 2001-10-11
C 2001-11-11
.
.
.
这样的查询怎样写呢？

 

B

bes96261

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #2

一點思路:首先有Group by的形式檢索出最大日期的值設別名為tableA,
然後再從表中提取不在表tableA中的最大日期記錄,這樣就是第二大的值了

 

B

bes96261

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #3

表名:table 字段名:column1 Datecolumn
select column1,Max(Datecolumn) as Datecolumn from table as a
where Datacolumn <> (select Max(Datecolumn) from table where column1=a.column1)
group by column1

 

Q

qianwt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #4

select a.LbField,Max(DateField) MaxDateField from Table a,
(select LbField,Max(DateField) MaxDateField from Table group by LbField) b
where a.LBField=b.LBField and a.DateField<b.MaxDateField group by LbField

 

F

fwcy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #5

不行，通不过！

 

Q

qianwt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #6

你把字段名和表名贴出来

 

R

raymondcen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #7

mysql不支持子查询的。不过可以通过做view来解决。

 

G

gongli

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #8

假设字段名为id,mydate,表名为table
SELECT id, MAX(mydate)
FROM TABLE
WHERE (mydate NOT IN
(SELECT MAX(mydate)
FROM table
GROUP BY id))
GROUP BY id
以上只适用在第二大不等于任何一组最大的情况

 

N

newfat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #9

借用一下楼上的设定
假设字段名为mydate,表名为table
SELECT MAX(mydate)
FROM TABLE
WHERE (mydate<
(SELECT MAX(mydate)
FROM table
GROUP BY mydate))
GROUP BY mydate

 

J

jsxjd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #10

选A的情况
select top 1 * from
(select top 2 * from table where type='A' broup by date/desc) a
group by a.date

用union 合成

 

L

lccc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #11

SELECT * FROM YourTable WHERE DateField=(
SELECT MAX(DateField) FROM YourTable WHERE DateField--这个子句求次大
<(SELECT MAX(DateField) FROM YourTable)--这个子句求最大
)

 

A

Awen.ZW.Yang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #12

借用楼上的部分意见：

假设字段名为id,mydate,表名为table
SELECT id, MAX(mydate) FROM TABLE --第二大不等于任何一组最大的情况
WHERE mydate <(SELECT MAX(mydate) FROM table GROUP BY id)
GROUP BY id
UNION
SELECT id, MAX(mydate) FROM TABLE --第二大等于任何一组最大的情况
WHERE id NOT IN(SELECT id FROM TABLE
WHERE mydate <(SELECT MAX(mydate) FROM table GROUP BY id)
)
GROUP BY id

 

F

fwcy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #13

to:gongli
可能会有第二大等于任何一组最大的情况，怎么处理？
TO:其他朋友
你们的方法好像不行，只有gongli的语句可行，但又有以上问题！

 

薛

薛獅

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #14

SELECT LB,MAX(MYDate) From A A1
Where MYDate < (SELECT MAX(MYData) FROM A A2 Where A2.LB=A1.LB) Group by LB
试一下！[^]

 

L

lccc

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-11

• #15

如果你的表中的日期都相同，可以单独处理，否则
应该是可以的。

 

S

simonlai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-03-29

• #16

這個一定ok
select Distinct a.FieldName,
Case When (Select Max(dDate) From Table1 where FieldName=A.FieldName)=(Select Min(dDate) From Table1 where FieldName=A.FieldName) then
bdate
else
( Select Max(dDate) From Table1 where FieldName= A.FieldName and bDate<(Select Max(dDate) From Table1 where FieldName= A.FieldName))
end From Table1 A
Order by FieldName

 

J

jsxs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-05-03

• #17

select Distinct a.FieldName,
Case When (Select Max(dDate) From Table1 where FieldName=A.FieldName)=(Select Min(dDate) From Table1 where FieldName=A.FieldName) then
bdate
else
( Select Max(dDate) From Table1 where FieldName= A.FieldName and bDate<(Select Max(dDate) From Table1 where FieldName= A.FieldName))
end From Table1 A
Order by FieldName

 

F

fwcy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-05-03

• #18

接受答案了.