求两个表查询的SQL语句

[luoqi]

表1:(t1)
字段: id,name
值:
1,a
2,b

表2:(t2)
字段:id,pid,count
值:
1,1,10
2,1,15
3,1,20
4,2,10
5,2,15

求:

1,1,a,10
2,1,a,15
3,1,a,20
4,2,b,10
5,2,b,15

[ruwawapp]

select 表1的字段值，表2的字段值from表1，表2 where……

[rmljoe]

select a.id,a.pid,b.name,a.count
from t2 a
left join t1 b
on a.pid = b.id

[ChinaJiaBing]

select b.*,a.name  
   from 表1 a join 表2 b on a.id=b.pid

[ruwawapp]

"select distinct s.学生编号,s.学生姓名,s.性别,s.出生年月,s.年龄,s.所在学院,s.所学专业,m.高数 from tb_stu s ,tb_mark m where s.学生编号=m.学生编号 and m.高数 >85"; 这是一个具体的例子

[shixixi1987]

if exists(select * from sysobjects where [name]='t1')
drop table t1
go
create table t1
(
id int,
[name] varchar(10)
)
go
insert into t1(id,[name])
(
select 1,'a' union
select 2,'b'
)
go
if exists(select * from sysobjects where [name]='t2')
drop table t2
go
create table t2
(
id int,
pid int,
[count] int
)
go
insert into t2(id,pid,[count])
(
select 1,1,10 union
select 2,1,15 union
select 3,1,20 union
select 4,2,10 union
select 5,2,15
)
select a.id,a.pid,b.name,a.[count] from t2 a left join t1 b on a.pid=b.id
go

[lingjin520]

CREATE TABLE t1(
	id decimal (4,0) PRIMARY KEY NOT NULL,
	name varchar(5)
)
INSERT t1 VALUES (1,'a')
INSERT t1 VALUES (2,'b')

CREATE TABLE t2(
	id decimal (4,0) PRIMARY KEY NOT NULL,
	pid decimal (4,0),
	[count] decimal(10),
	FOREIGN KEY (pid) REFERENCES t1(id)
)

INSERT t2 VALUES (1,1,10)
INSERT t2 VALUES (2,1,15)
INSERT t2 VALUES (3,1,20)
INSERT t2 VALUES (4,2,10)
INSERT t2 VALUES (5,2,15)

SELECT t2.id,t2.pid,t1.name,t2.[count] FROM t2 LEFT JOIN t1 on t1.id = t2.pid

[htl258_Tony]

--> 生成测试数据表: [t1]
IF OBJECT_ID('[t1]') IS NOT NULL
	DROP TABLE [t1]
GO
CREATE TABLE [t1] ([id] [int],[name] [nvarchar](10))
INSERT INTO [t1]
SELECT '1','a' UNION ALL
SELECT '2','b'

--> 生成测试数据表: [t2]
IF OBJECT_ID('[t2]') IS NOT NULL
	DROP TABLE [t2]
GO
CREATE TABLE [t2] ([id] [int],[pid] [int],[count] [int])
INSERT INTO [t2]
SELECT '1','1','10' UNION ALL
SELECT '2','1','15' UNION ALL
SELECT '3','1','20' UNION ALL
SELECT '4','2','10' UNION ALL
SELECT '5','2','15'

--SELECT * FROM [t1]
--SELECT * FROM [t2]

-->SQL查询如下:
--1.
SELECT id,pid,name=(SELECT name FROM t1 WHERE id=t2.pid),[count] FROM t2
/*
id          pid         name       count
----------- ----------- ---------- -----------
1           1           a          10
2           1           a          15
3           1           a          20
4           2           b          10
5           2           b          15

(5 行受影响)
*/
--2.
SELECT a.id,a.pid,b.name,a.[count] FROM t2 a,t1 b WHERE b.id=a.pid
/*
id          pid         name       count
----------- ----------- ---------- -----------
1           1           a          10
2           1           a          15
3           1           a          20
4           2           b          10
5           2           b          15

(5 行受影响)
*/

[nice_dream_li]

select t2.id ,pid,name,count from t1,t2
where t1.id=pid

[nice_dream_li]

select t2.id ,pid,name,count from t1,t2
where t1.id=pid