drop table ta go create table ta(id int identity primary key, [group] int, [type] int) godrop table tb go create table tb(id int identity primary key, [type] int, name nvarchar(100), [all] bit) gosample data for TA id group type 1 1 1 2 1 2 3 2 1 4 2 3 5 2 4sample data for TB id type name all 1 1 a NULL 2 2 b NULL 3 3 c NULL 4 4 d NULL 5 5 e NULL 6 55 for all 1 7 56 for all 1我想要如下结果id group type id type name all 1 1 1 1 1 a NULL 2 1 2 2 2 b NULL 3 2 1 1 1 a NULL 4 2 3 3 3 c NULL 5 2 4 4 4 d NULL -------- NULL 1 55 55 55 for all 1 NULL 1 56 56 56 for all 1 NULL 2 55 55 55 for all 1 NULL 2 56 56 56 for all 1注意:每个group都要有55和56这2个子类别。
--不懂楼主的具体意思!乱写的!create table ta(id int,[group] int,[type] int) insert into ta select 1,1,1 union all select 2,1,2 union all select 3,2,1 union all select 4,2,3 union all select 5,2,4create table tb(id int,[type] int,[name] varchar(20),[all] int) insert into tb select 1,1,'a',NULL union all select 2,2,'b',NULL union all select 3,3,'c',NULL union all select 4,4,'d',NULL union all select 5,5,'e',NULL union all select 6,55,'for all',1 union all select 7,56,'for all',1 goselect * from( select a.id,a.[group],a.[type],b.id bid,b.[type] btype,b.[name],b.[all] from ta a left join tb b on a.[type] = b.[type] union all select a.id,a.[group],b.[type],b.[type],b.[type],b.[name],b.[all] from (select distinct null as id,[group] from ta) a cross join (select * from tb where [name] = 'for all')b )tdrop table ta,tb /* id group type bid btype name all ----------- ----------- ----------- ----------- ----------- -------------------- ----------- 1 1 1 1 1 a NULL 2 1 2 2 2 b NULL 3 2 1 1 1 a NULL 4 2 3 3 3 c NULL 5 2 4 4 4 d NULL NULL 1 55 55 55 for all 1 NULL 1 56 56 56 for all 1 NULL 2 55 55 55 for all 1 NULL 2 56 56 56 for all 1(9 行受影响)
select * from a LEFT JOIN b on a.type=b.id --WHERE Condition....楼主出题应给出测试数据和相关信息,
where a.type=b.id
最好给数据 和你需要的结果
select *
from a inner join b on a.[type] = b.id
where b.flag = ...
select * from a INNER JOIN b
on a.type=b.id但是要每个a.group包括一些a.type没有引用的b.id,输入结果中a.id可以为null。如:a.id|a.group|a.type
1 g1 1
2 g1 2
3 g2 1
4 g2 2
5 g2 3
null g1 9
null g1 9
null g2 9
null g2 9
null g3 9
null g3 9
on a.type=b.id
go
create table ta(id int identity primary key, [group] int, [type] int)
godrop table tb
go
create table tb(id int identity primary key, [type] int, name nvarchar(100), [all] bit)
gosample data for TA
id group type
1 1 1
2 1 2
3 2 1
4 2 3
5 2 4sample data for TB
id type name all
1 1 a NULL
2 2 b NULL
3 3 c NULL
4 4 d NULL
5 5 e NULL
6 55 for all 1
7 56 for all 1我想要如下结果id group type id type name all
1 1 1 1 1 a NULL
2 1 2 2 2 b NULL
3 2 1 1 1 a NULL
4 2 3 3 3 c NULL
5 2 4 4 4 d NULL
--------
NULL 1 55 55 55 for all 1
NULL 1 56 56 56 for all 1
NULL 2 55 55 55 for all 1
NULL 2 56 56 56 for all 1注意:每个group都要有55和56这2个子类别。
--不懂楼主的具体意思!乱写的!create table ta(id int,[group] int,[type] int)
insert into ta
select 1,1,1 union all
select 2,1,2 union all
select 3,2,1 union all
select 4,2,3 union all
select 5,2,4create table tb(id int,[type] int,[name] varchar(20),[all] int)
insert into tb
select 1,1,'a',NULL union all
select 2,2,'b',NULL union all
select 3,3,'c',NULL union all
select 4,4,'d',NULL union all
select 5,5,'e',NULL union all
select 6,55,'for all',1 union all
select 7,56,'for all',1
goselect *
from(
select a.id,a.[group],a.[type],b.id bid,b.[type] btype,b.[name],b.[all]
from ta a left join tb b on a.[type] = b.[type]
union all
select a.id,a.[group],b.[type],b.[type],b.[type],b.[name],b.[all]
from (select distinct null as id,[group] from ta) a cross join
(select * from tb where [name] = 'for all')b
)tdrop table ta,tb
/*
id group type bid btype name all
----------- ----------- ----------- ----------- ----------- -------------------- -----------
1 1 1 1 1 a NULL
2 1 2 2 2 b NULL
3 2 1 1 1 a NULL
4 2 3 3 3 c NULL
5 2 4 4 4 d NULL
NULL 1 55 55 55 for all 1
NULL 1 56 56 56 for all 1
NULL 2 55 55 55 for all 1
NULL 2 56 56 56 for all 1(9 行受影响)
select * from a LEFT JOIN b
on a.type=b.id
--WHERE Condition....楼主出题应给出测试数据和相关信息,