Select * from TEST A Where Not Exists(Select 1 from TEST Where GROUP_DESC=A.GROUP_DESC And DFCT_MEMO<A.DFCT_MEMO Having Count(*)>4)
To xiaomeixiang:请问大哥, a 是什么意思啊??恩不明白啊,能否解释一下阿???
如果不同的GROUP_DESC,有相同的DFCT_MEMO,那么一楼的语句就有问题。
--建立测试环境 Create table TEST(GROUP_DESC Varchar(10),DFCT_MEMO Varchar(10)) --插入数据 Insert TEST Values('aaaa', 'ddsf') Insert TEST Values('aaaa', 'dsg') Insert TEST Values('aaaa', 'ghh') Insert TEST Values('aaaa', 'dgh') Insert TEST Values('dddd', 'rtt') Insert TEST Values('dddd', 'rttr') Insert TEST Values('dddd', 'ku') Insert TEST Values('dddd', 'rtr') Insert TEST Values('dddd', 'rtuj') Insert TEST Values('bb', 'yty') Insert TEST Values('eeee', 'ok') Insert TEST Values('eeee', 'dfgh') Insert TEST Values('eeee', 'rty') Insert TEST Values('eeee', 'hgh') Insert TEST Values('eeee', 'ytuy') Insert TEST Values('eeee', 'iu') --测试 Select * from TEST A Where Not Exists(Select 1 from TEST Where GROUP_DESC=A.GROUP_DESC And DFCT_MEMO<A.DFCT_MEMO Having Count(*)>4) --删除测试环境 Drop Table TEST --结果 /* GROUP_DESC DFCT_MEMO aaaa ddsf aaaa dsg aaaa ghh aaaa dgh dddd rtt dddd rttr dddd ku dddd rtr dddd rtuj bb yty eeee ok eeee dfgh eeee rty eeee hgh eeee iu */
To paoluo(一天到晚游泳的鱼): 如果我向数据库里再插入以下语句,结果好像就不对了, Insert TEST Values('dddd', 'rtuj') Insert TEST Values('bb', 'yty') Insert TEST Values('eeee', 'ok') Insert TEST Values('eeee', 'dfgh') Insert TEST Values('eeee', 'rty') Insert TEST Values('eeee', 'hgh') Insert TEST Values('eeee', 'ytuy')大哥,帮忙改改呢???
哦,呵呵,能否解释一下你的SQL语句啊??select * from test a where DFCT_MEMO in (select top 5 DFCT_MEMO from test where GROUP_DESC=a.GROUP_DESC)这样写什么意思啊??
查詢每個GROUP_DESC的前5條記錄
DFCT_MEMO,为什么要用这个字段来查询啊?? 还有;比如我纪录是这样的 GROUP_DESC DFCT_MEMO ---------- --------- aaaa ddsf aaaa dsg aaaa ghh aaaa qqqq select top 5 DFCT_MEMO from test where GROUP_DESC=a.GROUP_DESC中GROUP_DESC=a.GROUP_DESC 他会怎样进行查询呢?? 如果可能的话,恩绝对愿意啊~ :)HOHO~~!
GROUP_DESC DFCT_MEMO 如果GROUP_DESC字段相同的纪录数目小于5条,则显示所有纪录 如果GROUP_DESC字段相同的纪录数目大于等于5条,则显示前5条纪录 select * from test where GROUP_DESC in ( select max(GROUP_DESC ) as GROUP_DESC from test group by GROUP_DESC ) having count(*)>=5
1: 如果GROUP_DESC字段相同的纪录数目小于5条,则显示所有纪录 2: 如果GROUP_DESC字段相同的纪录数目大于等于5条,则显示前5条纪录 1: select * from test where GROUP_DESC in ( select max(GROUP_DESC ) as GROUP_DESC from test group by GROUP_DESC ) having count(*)<5 2: select top 5 * from test where GROUP_DESC in ( select max(GROUP_DESC ) as GROUP_DESC from test group by GROUP_DESC ) having count(*)>=5
Not Exists(Select 1 from TEST Where GROUP_DESC=A.GROUP_DESC And DFCT_MEMO<A.DFCT_MEMO Having Count(*)>4)
Create table TEST(GROUP_DESC Varchar(10),DFCT_MEMO Varchar(10))
--插入数据
Insert TEST Values('aaaa', 'ddsf')
Insert TEST Values('aaaa', 'dsg')
Insert TEST Values('aaaa', 'ghh')
Insert TEST Values('aaaa', 'dgh')
Insert TEST Values('dddd', 'rtt')
Insert TEST Values('dddd', 'rttr')
Insert TEST Values('dddd', 'ku')
Insert TEST Values('dddd', 'rtr')
Insert TEST Values('dddd', 'rtuj')
Insert TEST Values('bb', 'yty')
Insert TEST Values('eeee', 'ok')
Insert TEST Values('eeee', 'dfgh')
Insert TEST Values('eeee', 'rty')
Insert TEST Values('eeee', 'hgh')
Insert TEST Values('eeee', 'ytuy')
Insert TEST Values('eeee', 'iu')
--测试
Select * from TEST A Where Not Exists(Select 1 from TEST Where GROUP_DESC=A.GROUP_DESC And DFCT_MEMO<A.DFCT_MEMO Having Count(*)>4)
--删除测试环境
Drop Table TEST
--结果
/*
GROUP_DESC DFCT_MEMO
aaaa ddsf
aaaa dsg
aaaa ghh
aaaa dgh
dddd rtt
dddd rttr
dddd ku
dddd rtr
dddd rtuj
bb yty
eeee ok
eeee dfgh
eeee rty
eeee hgh
eeee iu
*/
如果我向数据库里再插入以下语句,结果好像就不对了,
Insert TEST Values('dddd', 'rtuj')
Insert TEST Values('bb', 'yty')
Insert TEST Values('eeee', 'ok')
Insert TEST Values('eeee', 'dfgh')
Insert TEST Values('eeee', 'rty')
Insert TEST Values('eeee', 'hgh')
Insert TEST Values('eeee', 'ytuy')大哥,帮忙改改呢???
DFCT_MEMO in
(select top 5 DFCT_MEMO from test where GROUP_DESC=a.GROUP_DESC)这样写什么意思啊??
还有;比如我纪录是这样的
GROUP_DESC DFCT_MEMO
---------- ---------
aaaa ddsf
aaaa dsg
aaaa ghh
aaaa qqqq
select top 5 DFCT_MEMO from test where GROUP_DESC=a.GROUP_DESC中GROUP_DESC=a.GROUP_DESC
他会怎样进行查询呢??
如果可能的话,恩绝对愿意啊~ :)HOHO~~!
如果GROUP_DESC字段相同的纪录数目小于5条,则显示所有纪录
如果GROUP_DESC字段相同的纪录数目大于等于5条,则显示前5条纪录
select *
from test
where GROUP_DESC in
(
select max(GROUP_DESC ) as GROUP_DESC
from test
group by GROUP_DESC
)
having count(*)>=5
如果GROUP_DESC字段相同的纪录数目小于5条,则显示所有纪录
2:
如果GROUP_DESC字段相同的纪录数目大于等于5条,则显示前5条纪录
1:
select *
from test
where GROUP_DESC in
(
select max(GROUP_DESC ) as GROUP_DESC
from test
group by GROUP_DESC
)
having count(*)<5
2:
select top 5 *
from test
where GROUP_DESC in
(
select max(GROUP_DESC ) as GROUP_DESC
from test
group by GROUP_DESC
)
having count(*)>=5