IF OBJECT_ID('test1') IS NOT NULL DROP TABLE test1
go
create table test1 ( a INT,
b INT
)
SELECT * FROM test1
INSERT INTO test1
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 --自己写的代码,执行报错:
--服务器: 消息 156,级别 15,状态 1,行 5
--在关键字 'ON' 附近有语法错误。SELECT d.* FROM test1 d
LEFT JOIN
(SELECT c.* FROM test1 c
WHERE a%2=1)
ON d.a=c.a+1--想要得到的结果
a b a b
1 10 2 20
3 30 4 40--求助各位大侠~!
go
create table test1 ( a INT,
b INT
)
INSERT INTO test1
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40
SELECT a.*,b.* FROM test1 a,test1 b
where a.a=b.a-1 and a.a%2=1
a b a b
----------- ----------- ----------- -----------
1 10 2 20
3 30 4 40(2 行受影响)
SELECT t.*,d.* FROM test11 d
LEFT JOIN
(SELECT c.* FROM test11 c
WHERE a%2=1)t
ON d.a=t.a+1
where t.a is not null
FROM test1 d
LEFT JOIN test1 c ON d.a=c.a-1
WHERE d.a%2=1
go
create table test1 ( a INT,
b INT
)
INSERT INTO test1
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50SELECT *
FROM test1 d
LEFT JOIN test1 c ON d.a=c.a-1
WHERE d.a%2=1 /**
a b a b
----------- ----------- ----------- -----------
1 10 2 20
3 30 4 40
5 50 NULL NULL(3 行受影响)
**/
go
create table testL ( a INT,
b INT
)
--SELECT * FROM testL
INSERT INTO testL
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 SELECT * FROM testL--想要得到的结果
--a b a b
--1 10 2 20
--3 30 4 40--方法一
SELECT m.*,n.*
FROM testL m,testL n
WHERE m.a=n.a-1 AND m.a % 2=1--方法二
SELECT m.*,n.*
FROM testL m
LEFT JOIN testL n
ON m.a=n.a -1
WHERE m.a % 2=1a b a b
----------- ----------- ----------- -----------
1 10 2 20
3 30 4 40(2 row(s) affected)