两个表的数据关联查询得到另一个表

[tddxg]

表a
date mno time
120601 1 20
120602 1 30
表b
mno desp
1 lwl
2 sp
问题如下：先要求a和b联合查询得到新表C，c表字段如下：
date mno desp time
120601 1 lwl 20
120601 2 sp 0
120602 1 lwl 30
120602 2 sp 0
请高手赐教！

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
	DROP TABLE A
END
GO
CREATE TABLE A  
(
	date VARCHAR(10),
	mno INT,
	time INT
)
GO
INSERT INTO A
SELECT '120601', 1, 20 UNION
SELECT '120602', 1, 30
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'B')
BEGIN
	DROP TABLE B
END
GO
CREATE TABLE B  
(
	mno INT,
	desp VARCHAR(10)
)
GO
INSERT INTO B
SELECT 1, 'lwl' UNION
SELECT 2, 'sp'
GO

SELECT date, B.mno, desp, time INTO C
FROM A,B
ORDER BY date

SELECT * FROM C

[百年树人]

if object_id('[a]') is not null drop table [a]
go
create table [a]([date] int,[mno] int,[time] int)
insert [a]
select 120601,1,20 union all
select 120602,1,30
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([mno] int,[desp] varchar(3))
insert [b]
select 1,'lwl' union all
select 2,'sp'
go

select a.date,b.mno,b.desp,isnull(time,0) as time
from (select distinct date from a) a
join b on 1=1
left join a as c on c.mno=b.mno and a.date=c.date

/**
date        mno         desp time
----------- ----------- ---- -----------
120601      1           lwl  20
120601      2           sp   0
120602      1           lwl  30
120602      2           sp   0

(4 行受影响)
**/

[bancxc]

还是树哥专业

[开着拖拉机泡妞]

[Quote=引用 2 楼 的回复:]

SQL code
if object_id('[a]') is not null drop table [a]
go
create table [a]([date] int,[mno] int,[time] int)
insert [a]
select 120601,1,20 union all
select 120602,1,30
go
if object_id('[b]') is not ……
[/Quote]
膜拜一下树哥

[發糞塗牆]

貌似是一个行转列/列转行的问题，网上一大把