CREATE TABLE person
(
id INT,
pname varchar(10)
)
CREATE TABLE book
(
id INT,
bname varchar(10),
checks INT,
pid INT
)
INSERT INTO person (id,pname)VALUES(1,'张三')
INSERT INTO person (id,pname)VALUES(2,'李四')
INSERT INTO person (id,pname)VALUES(3,'王二')
INSERT INTO person (id,pname)VALUES(4,'麻子')
INSERT INTO book (id,bname,checks,pid)VALUES(1,'C#',1,1)
INSERT INTO book (id,bname,checks,pid)VALUES(2,'java',2,2)
INSERT INTO book (id,bname,checks,pid)VALUES(3,'C++',1,3)
SELECT * FROM person
SELECT * FROM book
--查checks<>2 的psnme
--要求person表和book表都要用到
--结果
--张三 王二 麻子
SELECT pname FROM person
SELECT pid FROM book WHERE checks<>2
------解决方案--------------------
- SQL code
--建立连接函数Create function f_hb(@name varchar(20)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + pname from (select person.pname from book inner join person on person.id = book.pid where checks <>2) T set @str = right(@str , len(@str) - 1) return(@str) End go --获取select top 1 dbo.f_hb(pname)from (select person.pname from book inner join person on person.id = book.pidwhere checks <>2)TCreate function f_hb(@name varchar(20)) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + pname from (select person.pname from book inner join person on person.id = book.pid where checks <>2) T set @str = right(@str , len(@str) - 1) return(@str) End go --张三,王二
------解决方案--------------------
select * from person where not id in (select id from book where checks = 2)
------解决方案--------------------
- SQL code
--测试数据：CREATE TABLE person(id INT,pname varchar(10))CREATE TABLE book(id INT,bname varchar(10),checks INT,pid INT)INSERT INTO person (id,pname)VALUES(1,'张三')INSERT INTO person (id,pname)VALUES(2,'李四')INSERT INTO person (id,pname)VALUES(3,'王二')INSERT INTO person (id,pname)VALUES(4,'麻子')INSERT INTO book (id,bname,checks,pid)VALUES(1,'C#',1,1)INSERT INTO book (id,bname,checks,pid)VALUES(2,'java',2,2)INSERT INTO book (id,bname,checks,pid)VALUES(3,'C++',1,3)--方法1：select a.pname from person a left join book b on a.id=b.pidwhere b.checks<>2 or b.checks is null--方法2：select a.pname from person a left join book b on a.id=b.pidwhere b.checks<>2union allselect a.pname from person a left join book b on a.id=b.pidwhere b.checks is null--结果：pname----------张三王二麻子(3 行受影响)