34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[tb1](
[tb1order] [int] NULL,
[tb2name] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tb2](
[tb2name] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO tb1(tb1order,tb2name) VALUES (1,'a')
INSERT INTO tb1(tb1order,tb2name) VALUES (2,'a')
INSERT INTO tb1(tb1order,tb2name) VALUES (3,'a')
INSERT INTO tb1(tb1order,tb2name) VALUES (1,'b')
INSERT INTO tb1(tb1order,tb2name) VALUES (2,'b')
INSERT INTO tb1(tb1order,tb2name) VALUES (3,'b')
INSERT INTO tb2 (tb2name) VALUES('a')
INSERT INTO tb2 (tb2name) VALUES('b')
select b.tb1order,a.tb2name
from tb2 a
left join tb1 b on a.tb2name=b.tb2name
where b.tb1order in(select MIN(tb1order) from tb1)
tb1里的tb1order 如果不都是1,2,3的话就返回不了正确的结果。
比如说是
tb1:
2 a
3 a
7 a
4 b
5 b
8 b
那么返回的结果是
2 a
正确的应该是
2 a
4 b
求解???[/quote]
知道了 在里面加一句 group by tb2name
select b.tb1order,a.tb2name
from tb2 a
left join tb1 b on a.tb2name=b.tb2name
where b.tb1order in(select MIN(tb1order) from tb1 GROUP BY tb2name)
select b.tb1order,a.tb2name
from tb2 a
left join tb1 b on a.tb2name=b.tb2name
where b.tb1order in(select MIN(tb1order) from tb1)
tb1里的tb1order 如果不都是1,2,3的话就返回不了正确的结果。
比如说是
tb1:
2 a
3 a
7 a
4 b
5 b
8 b
那么返回的结果是
2 a
正确的应该是
2 a
4 b
求解???
select b.tb1order,a.tb2name
from tb2 a
left join tb1 b on a.tb2name=b.tb2name
where b.tb1order in(select MIN(tb1order) from tb1)
/*
1 a
1 b
*/