select * from deliver_all a,(select min(a.content_id) as content_id, b.number from content_relation a inner join deliver_all b on a.content_id=b.id group by b.number) b where a.id=b. content_id order by a.instert_date
select a.* from deliver_all a,(select min(a.content_id) as content_id, b.number from content_relation a inner join deliver_all b on a.content_id=b.id group by b.number) b where a.id=b. content_id order by a.instert_date
多谢小李铅笔刀,回复的好快啊。还有那个catalog_uuid=198是不是应该写在这里 select a.* from deliver_all a,(select min(a.content_id) as content_id, b.number from content_relation a inner join deliver_all b on a.content_id=b.id and catalog_uuid=198 group by b.number) b where a.id=b. content_id order by a.instert_date
Create Table Content_Relation( [id] int null, content_id int null, catalog_uuid int null, [date] datetime null ) ----------------------------------------------- Create Table Deliver_all( [id] int null, number int null, ms_content varchar(2) null, instert_date datetime null ) ------------------------------------------------ Insert into Content_Relation values(1,100,198,'2004/01/01') Insert into Content_Relation values(2,101,198,'2004/02/02') Insert into Content_Relation values(3,102,198,'2004/03/03') Insert into Content_Relation values(4,103,200,'2004/04/04') ---------------------------------------------------- insert into deliver_all values(100,1111,'ly','2004/01/01') insert into deliver_all values(101,2222,'lm','2004/02/02') insert into deliver_all values(102,2222,'lc','2004/03/03') insert into deliver_all values(103,2222,'lf','2004/04/04') --------------------------------------------------------------------------- -------------下面是查詢語句------------------------------------------ select a.[id],number,ms_content,instert_date from content_relation inner join ( select * from Deliver_all where [id] in( select min([id]) from deliver_all group by number having count(*)>1 union select min([id]) from deliver_all group by number having count(*)=1 ) ) a on (a.[id]=content_relation.content_id) where catalog_uuid=198 ---------------------------------------結束------------------------------ drop table content_relation drop table deliver_all--------------------------把這上面的 拷貝到查詢器中直接執行就可看到結果------------------
select distinct a.[id],number,ms_content,instert_date from Content_Relation inner join ( select * from Deliver_all where [id] in( select min([id]) from Deliver_all group by number having count(*)>1 union select min([id]) from Deliver_all group by number having count(*)=1 ) ) a on (a.[id]=Content_Relation.content_id) where catalog_uuid=198 樓上加distinct 就ok了
select a.number from deliver_all a,(select min(a.content_id) as content_id, b.number from content_relation a inner join deliver_all b on a.content_id=b.id group by b.number) b where a.id=b. content_id and cast(a.number as int)<2005 --这里加条件 order by a.instert_date
select b.* from content_relation a,deliver_all bwhere a.content_id=b.id and a.catalog_uuid=198 order by b.insert_date
楼主帮测试下:select a.* from deliver_all as a,content_relation as b where b.catalog_uuid='198' and a.id=b.content_id and cast(a.number as bigint)<2005 and not exists (select * from deliver_all where number=a.number and id<a.id)
from deliver_all a,(select min(a.content_id) as content_id,
b.number
from content_relation a inner join deliver_all b
on a.content_id=b.id
group by b.number) b
where a.id=b. content_id
order by a.instert_date
from deliver_all a,(select min(a.content_id) as content_id,
b.number
from content_relation a inner join deliver_all b
on a.content_id=b.id
group by b.number) b
where a.id=b. content_id
order by a.instert_date
select a.*
from deliver_all a,(select min(a.content_id) as content_id,
b.number
from content_relation a inner join deliver_all b
on a.content_id=b.id and catalog_uuid=198
group by b.number) b
where a.id=b. content_id
order by a.instert_date
[id] int null,
content_id int null,
catalog_uuid int null,
[date] datetime null
)
-----------------------------------------------
Create Table Deliver_all(
[id] int null,
number int null,
ms_content varchar(2) null,
instert_date datetime null
)
------------------------------------------------
Insert into Content_Relation values(1,100,198,'2004/01/01')
Insert into Content_Relation values(2,101,198,'2004/02/02')
Insert into Content_Relation values(3,102,198,'2004/03/03')
Insert into Content_Relation values(4,103,200,'2004/04/04')
----------------------------------------------------
insert into deliver_all values(100,1111,'ly','2004/01/01')
insert into deliver_all values(101,2222,'lm','2004/02/02')
insert into deliver_all values(102,2222,'lc','2004/03/03')
insert into deliver_all values(103,2222,'lf','2004/04/04')
---------------------------------------------------------------------------
-------------下面是查詢語句------------------------------------------
select a.[id],number,ms_content,instert_date from content_relation inner join (
select * from Deliver_all where [id] in(
select min([id]) from deliver_all group by number having count(*)>1
union select min([id]) from deliver_all group by number having count(*)=1
)
) a on (a.[id]=content_relation.content_id)
where catalog_uuid=198
---------------------------------------結束------------------------------
drop table content_relation
drop table deliver_all--------------------------把這上面的 拷貝到查詢器中直接執行就可看到結果------------------
select * from Deliver_all where [id] in(
select min([id]) from Deliver_all group by number having count(*)>1
union select min([id]) from Deliver_all group by number having count(*)=1
)
) a on (a.[id]=Content_Relation.content_id)
where catalog_uuid=198
樓上加distinct 就ok了
101 02002 lm 2004-02-02
102 02002 lc 2004-03-03
103 02006 lf 2004-04-04
. . . .
. . . .
还有如果其他条件一样,表diliver_all中的 number字段中的数据如上所示。
我如果想取得number中,除了0以后并小于2005的所有记录,
查处的结果应该为2001
2002又该怎么写呢?
from deliver_all a,(select min(a.content_id) as content_id,
b.number
from content_relation a inner join deliver_all b
on a.content_id=b.id
group by b.number) b
where a.id=b. content_id
and cast(a.number as int)<2005 --这里加条件
order by a.instert_date
b.insert_date
number=a.number and id<a.id)