A表,id是主键
id title sendtime
1 111 2010-9-13 15:33:12
2 222 2010-9-14 15:33:12
3 333 2010-9-15 15:33:12
4 444 2010-9-16 15:33:12B表,eID是主键
eID emailID sendID receiveID status
1 1 1 1 1
2 1 1 2 2
3 1 1 3 3
4 2 2 1 1
5 3 2 2 2
6 3 2 2 2
7 4 2 2 2
8 4 2 2 2
9 4 1 2 2A表的id和B表的emailID关联,一对多的关系,现在要得到结果如下(sendID为1,且emailID不重复):id title sendtime eID emailID sendID receiveID status
1 111 2010-9-13 15:33:12 1 1 1 1 1
4 444 2010-9-16 15:33:12 9 4 1 2 2
id title sendtime
1 111 2010-9-13 15:33:12
2 222 2010-9-14 15:33:12
3 333 2010-9-15 15:33:12
4 444 2010-9-16 15:33:12B表,eID是主键
eID emailID sendID receiveID status
1 1 1 1 1
2 1 1 2 2
3 1 1 3 3
4 2 2 1 1
5 3 2 2 2
6 3 2 2 2
7 4 2 2 2
8 4 2 2 2
9 4 1 2 2A表的id和B表的emailID关联,一对多的关系,现在要得到结果如下(sendID为1,且emailID不重复):id title sendtime eID emailID sendID receiveID status
1 111 2010-9-13 15:33:12 1 1 1 1 1
4 444 2010-9-16 15:33:12 9 4 1 2 2
from a , b m
where a.id = m.emailID and m.sendID = 1 and m.eID = (select min(eID) from b where emailID = m.emailID and sendID = 1)
select a.* , m.eID ,m.emailID ,m.sendID ,m.receiveID ,m.status
from a , b m
where a.id = m.emailID and m.sendID = 1 and not exists (select 1 from b where emailID = m.emailID and sendID = 1 and eID < m.eID)
insert into a values(1 ,111 ,'2010-9-13 15:33:12')
insert into a values(2 ,222 ,'2010-9-14 15:33:12')
insert into a values(3 ,333 ,'2010-9-15 15:33:12')
insert into a values(4 ,444 ,'2010-9-16 15:33:12')
create table b(eID int, emailID int,sendID int,receiveID int,status int)
insert into b values(1 ,1 ,1 ,1 ,1)
insert into b values(2 ,1 ,1 ,2 ,2)
insert into b values(3 ,1 ,1 ,3 ,3)
insert into b values(4 ,2 ,2 ,1 ,1)
insert into b values(5 ,3 ,2 ,2 ,2)
insert into b values(6 ,3 ,2 ,2 ,2)
insert into b values(7 ,4 ,2 ,2 ,2)
insert into b values(8 ,4 ,2 ,2 ,2)
insert into b values(9 ,4 ,1 ,2 ,2)
goselect a.* , m.eID ,m.emailID ,m.sendID ,m.receiveID ,m.status
from a , b m
where a.id = m.emailID and m.sendID = 1 and m.eID = (select min(eID) from b where emailID = m.emailID and sendID = 1)
/*
id title sendtime eID emailID sendID receiveID status
----------- ---------- ------------------------------------------------------ ----------- ----------- ----------- ----------- -----------
1 111 2010-09-13 15:33:12.000 1 1 1 1 1
4 444 2010-09-16 15:33:12.000 9 4 1 2 2(所影响的行数为 2 行)
*/select a.* , m.eID ,m.emailID ,m.sendID ,m.receiveID ,m.status
from a , b m
where a.id = m.emailID and m.sendID = 1 and not exists (select 1 from b where emailID = m.emailID and sendID = 1 and eID < m.eID)
/*
id title sendtime eID emailID sendID receiveID status
----------- ---------- ------------------------------------------------------ ----------- ----------- ----------- ----------- -----------
1 111 2010-09-13 15:33:12.000 1 1 1 1 1
4 444 2010-09-16 15:33:12.000 9 4 1 2 2(所影响的行数为 2 行)
*/
drop table a , b
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(id int, title int, sendtime datetime)
insert into #a
select 1, 111, '2010-9-13 15:33:12' union all
select 2, 222, '2010-9-14 15:33:12' union all
select 3, 333, '2010-9-15 15:33:12' union all
select 4, 444, '2010-9-16 15:33:12'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(eID int, emailID int, sendID int, receiveID int, status int)
insert into #b
select 1, 1, 1, 1, 1 union all
select 2, 1, 1, 2, 2 union all
select 3, 1, 1, 3, 3 union all
select 4, 2, 2, 1, 1 union all
select 5, 3, 2, 2, 2 union all
select 6, 3, 2, 2, 2 union all
select 7, 4, 2, 2, 2 union all
select 8, 4, 2, 2, 2 union all
select 9, 4, 1, 2, 2select * from #a a join #b b on a.id = b.emailID and b.sendID=1
and not exists (select 1 from #b where emailID=b.emailID and sendID=1 and receiveID<b.receiveID)/*
id title sendtime eID emailID sendID receiveID status
----------- ----------- ----------------------- ----------- ----------- ----------- ----------- -----------
1 111 2010-09-13 15:33:12.000 1 1 1 1 1
4 444 2010-09-16 15:33:12.000 9 4 1 2 2
*/
and not exists (select 1 from emailReceive b where emailID=b.emailID and b.sendID=1 and b.receiveID<receiveID)我想外部不用别名可以吗,也就是下面的不能使用别名:
select * from email join emailReceive on id = emailID and sendID=1
@TableNames VARCHAR(200), --表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR(200), --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页,0表示第1页
@Filter VARCHAR(200) = '', --条件,可以为空,不用填 where
@Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by
@Order VARCHAR(200) = '' --排序,可以为空,为空默认按主键升序排列,不用填 order by因为我使用的这个存储过程不能使用别名,郁闷吧
select *
from (select *,
ROW_NUMBER()over(partition by y.emailid order by y.eid)as row
from dbo.a as x
cross join dbo.b as y
where x.id=y.emailID
and y.sendID=1)as p
where row=1;
ROW_NUMBER()这个函数是SQL 2005里的吧?
我的数据库是SQL 2000呢
insert into #A values(1 ,111 ,'2010-9-13 15:33:12')
insert into #A values(2 ,222 ,'2010-9-14 15:33:12')
insert into #A values(3 ,333 ,'2010-9-15 15:33:12')
insert into #A values(4 ,444 ,'2010-9-16 15:33:12') create table #b(eID int, emailID int,sendID int,receiveID int,status int)
insert into #b values(1 ,1 ,1 ,1 ,1)
insert into #b values(2 ,1 ,1 ,2 ,2)
insert into #b values(3 ,1 ,1 ,3 ,3)
insert into #b values(4 ,2 ,2 ,1 ,1)
insert into #b values(5 ,3 ,2 ,2 ,2)
insert into #b values(6 ,3 ,2 ,2 ,2)
insert into #b values(7 ,4 ,2 ,2 ,2)
insert into #b values(8 ,4 ,2 ,2 ,2)
insert into #b values(9 ,4 ,1 ,2 ,2) select * from #A
left join #B
on #A.id=#B.emailID
where #B.eid in (select Min(eid) from #B where sendid=1 group by emailid)
select a.id,a.title,a.sendtime,b.eid,b.emailid,b.sendid,b.receiveid,b.status
from a,b where
a.id=b.emailid
and b.sendid=1
and b.eid in (select min(eid) from b where sendid=1 group by emailid)