好像写过的SELECT PRV,NAME,CODE,PIC,MEMO
FROM (
SELECT PRV,NAME,CAST(NULL AS VARCHAR(30)) AS CODE, CAST(NULL AS INT) AS PIC,(NULL AS VARCHAR(50)) AS MEMO,CODE AS TYPE1,1 AS TYPE2
FROM AA
UNION ALL
SELECT NULL,NULL,CODE,PIC,'0',CODE AS TYPE1,2 AS TYPE2
FROM AA
UNION ALL
SELECT NULL,NULL,NULL,PIC,CONVERT(VARCHAR(10),DATE,111),CODE AS TYPE1,3 AS TYPE2
FROM BB
) AS T
ORDER BY TYPE1,TYPE2
没碰过,Erp里面好像也没要求这个的...
http://blog.csdn.net/zjcxc/archive/2004/08/29/88234.aspx
ID int,
CODE varchar(20),
PRV varchar(10),
NAME Nvarchar(50),
PIC int
)
insert @aa select
38, 'IN06090009', 'G002', N'天人饮料有限公司', 800
union all select
39, 'IN06090010', 'G001', N'珠海可口可乐' , 60
union all select
40, 'IN06090011', 'G003', N'天虹制衣厂' , 100
union all select
41, 'IN06090012', 'G002', N'天人饮料有限公司' , 400declare @BB table (
CODE varchar(20),
DATE datetime,
PIC int
)
insert @bb select
'IN06090009', '2006/09/13', 800
union all select
'IN06090012', '2006/09/16', 350
union all select
'IN06090012', '2006/09/17', 50SELECT PRV,NAME,CODE,PIC,MEMO
FROM (
SELECT PRV,NAME,CAST(NULL AS VARCHAR(30)) AS CODE, CAST(NULL AS INT) AS PIC,cast(NULL AS VARCHAR(50)) AS MEMO,CODE AS TYPE1,1 AS TYPE2
FROM @AA
UNION ALL
SELECT NULL,NULL,CODE,PIC,'0',CODE AS TYPE1,2 AS TYPE2
FROM @AA
UNION ALL
SELECT NULL,NULL,NULL,PIC,CONVERT(VARCHAR(10),DATE,111),CODE AS TYPE1,3 AS TYPE2
FROM @BB
) AS T
ORDER BY TYPE1,TYPE2--结果
PRV NAME CODE PIC MEMO
---------- -------------------------------------------------- ------------------------------ ----------- --------------------------------------------------
G002 天人饮料有限公司 NULL NULL NULL
NULL NULL IN06090009 800 0
NULL NULL NULL 800 2006/09/13
G001 珠海可口可乐 NULL NULL NULL
NULL NULL IN06090010 60 0
G003 天虹制衣厂 NULL NULL NULL
NULL NULL IN06090011 100 0
G002 天人饮料有限公司 NULL NULL NULL
NULL NULL IN06090012 400 0
NULL NULL NULL 350 2006/09/16
NULL NULL NULL 50 2006/09/17(所影响的行数为 11 行)
ID int,
CODE varchar(20),
PRV varchar(10),
NAME Nvarchar(50),
PIC int
)
insert @aa select
38, 'IN06090009', 'G002', N'天人饮料有限公司', 800
union all select
39, 'IN06090010', 'G001', N'珠海可口可乐' , 60
union all select
40, 'IN06090011', 'G003', N'天虹制衣厂' , 100
union all select
41, 'IN06090012', 'G002', N'天人饮料有限公司' , 400declare @BB table (
CODE varchar(20),
DATE datetime,
PIC int
)
insert @bb select
'IN06090009', '2006/09/13', 800
union all select
'IN06090012', '2006/09/16', 350
union all select
'IN06090012', '2006/09/17', 50SELECT PRV,NAME,CODE,PIC,MEMO
FROM (
SELECT PRV,NAME,CAST(NULL AS VARCHAR(30)) AS CODE, CAST(NULL AS INT) AS PIC,cast(NULL AS VARCHAR(50)) AS MEMO,PRV as type0 ,min(CODE) AS TYPE1,1 AS TYPE2
FROM @AA
group by PRV,NAME
UNION ALL
SELECT NULL,NULL,CODE,PIC,'0',PRV as type0 ,CODE AS TYPE1,2 AS TYPE2
FROM @AA
UNION ALL
SELECT NULL,NULL,NULL,b.PIC,CONVERT(VARCHAR(10),b.DATE,111),a.PRV as type0 ,b.CODE AS TYPE1,3 AS TYPE2
FROM @BB b,@aa a
where a.code=b.code
) AS T
ORDER BY type0,TYPE1,TYPE2--结果
PRV NAME CODE PIC MEMO
---------- -------------------------------------------------- ------------------------------ ----------- --------------------------------------------------
G001 珠海可口可乐 NULL NULL NULL
NULL NULL IN06090010 60 0
G002 天人饮料有限公司 NULL NULL NULL
NULL NULL IN06090009 800 0
NULL NULL NULL 800 2006/09/13
NULL NULL IN06090012 400 0
NULL NULL NULL 350 2006/09/16
NULL NULL NULL 50 2006/09/17
G003 天虹制衣厂 NULL NULL NULL
NULL NULL IN06090011 100 0(所影响的行数为 10 行)