表xx的结构为:
id | pid | name
1 0 a
2 1 d
3 1 e
4 2 g想得到的结果为:
id name pname
1 a null
2 d a
3 e a
4 g d 我是这样写的:select t1.id,t1.name,t2.name from xx t1,xx t2 where t1.pid=t2.id;
但查询结果为空有劳大家帮忙写出个这个SQL语句
id | pid | name
1 0 a
2 1 d
3 1 e
4 2 g想得到的结果为:
id name pname
1 a null
2 d a
3 e a
4 g d 我是这样写的:select t1.id,t1.name,t2.name from xx t1,xx t2 where t1.pid=t2.id;
但查询结果为空有劳大家帮忙写出个这个SQL语句
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[pid] [int],[name] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','0','a' UNION ALL
SELECT '2','1','d' UNION ALL
SELECT '3','1','e' UNION ALL
SELECT '4','2','g'--SELECT * FROM [tb]-->SQL查询如下:
SELECT id, name, pname = (
SELECT name
FROM tb
WHERE id = t.pid
)
FROM tb t
/*
id name pname
----------- ---------- ----------
1 a NULL
2 d a
3 e a
4 g d(4 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[pid] [int],[name] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','0','a' UNION ALL
SELECT '2','1','d' UNION ALL
SELECT '3','1','e' UNION ALL
SELECT '4','2','g'--SELECT * FROM [tb]-->SQL查询如下:
SELECT a.id,a.name,b.name pname
FROM tb a
LEFT JOIN tb b
ON a.pid=b.id
/*
id name pname
----------- ---------- ----------
1 a NULL
2 d a
3 e a
4 g d(4 行受影响)
*/
这位大哥,请问能不能告诉我select t1.id,t1.name,t2.name from xx t1,xx t2 where t1.pid=t2.id;这查询错误在哪个地方,这语句是什么情况下的查询语句。
2.JOIN关键词和“=”最大的区别是什么,不是都可以用来连接表吗?
--改为左连接
select t1.id,t1.name,t2.name from xx t1 left join xx t2 on t1.pid=t2.id;
IF OBJECT_ID('xx') IS NOT NULL
DROP TABLE xx
GO
CREATE TABLE xx ([id] numeric(8),[pid] numeric(8),[name] [nvarchar](10))
INSERT INTO xx
SELECT '1','0','a' UNION ALL
SELECT '2','1','d' UNION ALL
SELECT '3','1','e' UNION ALL
SELECT '4','2','g'
go
select t1.id,t1.name,t2.name from xx t1,xx t2 where t1.pid=t2.id;
--结果不为空
/*
id name name
--------------------------------------- ---------- ----------
2 d a
3 e a
4 g d
*/
FROM tb a
LEFT JOIN tb b ON a.pid=b.id
左连接