declare @t1 table
(
id int identity(1,1),
mainType varchar(10)
)
insert @t1 select 'main_a'
union all select 'main_b'declare @t2 table
(
mainType varchar(10),
s1 int,
s2 int
)insert @t2 select 'main_a',1,1
union all select 'main_a',2,2
union all select 'main_b',1,1
select
*
from(
select
mainType,
'' as s1,
'' as s2
from @t1
union all
select
mainType,
convert(varchar(10),s1) as s1,
convert(varchar(10),s2) as s2
from @t2
)a
order by mainType,s1
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (id INT,mainType VARCHAR(6))
INSERT INTO #tb1
SELECT 1,'main_a' UNION ALL
SELECT 2,'main_b'
--> liangCK小梁 于2008-10-14
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (mainType VARCHAR(6),s1 INT,s2 INT)
INSERT INTO #tb2
SELECT 'main_a',1,1 UNION ALL
SELECT 'main_a',2,2 UNION ALL
SELECT 'main_b',1,1--SQL查询如下:SELECT mainType,s1,s2
FROM
(
SELECT mainType,'' AS s1,'' AS s2,
mainType AS o1,0 AS o2
FROM #tb1
UNION ALL
SELECT a.mainType,RTRIM(b.s1) AS s1,
RTRIM(b.s2) AS s2,
a.mainType AS o1,1 AS o2
FROM #tb1 AS a
JOIN #tb2 AS b
ON a.mainType=b.mainType
) AS t
ORDER BY o1,o2/*
mainType s1 s2
-------- ------------ ------------
main_a
main_a 1 1
main_a 2 2
main_b
main_b 1 1(5 行受影响)
*/
select maintype,s1,s2 from T2
union all
select maintype,'','' from T1
order by maintype,s1
declare @T1 table (id int,mainType varchar(6))
insert into @T1
select 1,'main_a' union all
select 2,'main_b'
--> 测试数据: @T2
declare @T2 table (mainType varchar(6),s1 int,s2 int)
insert into @T2
select 'main_a',1,1 union all
select 'main_a',2,2 union all
select 'main_b',1,1select maintype,null,null from @T1
union all
select maintype,s1,s2 from @T2
order by maintype
drop table t1
go
create table t1(id int,maintype varchar(50))
insert into t1 select 1,'main_a'
insert into t1 select 2,'main_b'if object_id('t2') is not null
drop table t2
go
create table t2(maintype varchar(50),s1 int,s2 int)
insert into t2 select 'main_a',1,1
insert into t2 select 'main_a',2,2
insert into t2 select 'main_b',1,1select * from (
select maintype,'' as s1,'' as s2 from t1
union all
select maintype,ltrim(s1),ltrim(s2) from t2)t
order by maintype,case when s1='' then 0 else 1 endmaintype s1 s2
main_a
main_a 1 1
main_a 2 2
main_b
main_b 1 1
select maintype,cast(s1 as varchar) as s1, cast(s1 as varchar) as s2 from T2
union all
select maintype,'','' from T1
order by maintype,s1maintype s1 s2
---------- ------------------------------ ------------------------------
main_a
main_a 1 1
main_a 2 2
main_b
main_b 1 1(所影响的行数为 5 行)
不好意思拉~~