存在一个表
Artistname BORN DIED
--------------------------------
Jones, Henry 1965
Matisse, Henri 1869 1954
McCubbin, Fred 1840 1880
Michael, Jackson 1940
Nolan, Adelaide 1950
Nolan, Sydney 1917 1992
Nolan, Victoria 1955
Pollock, Jackson 1912 1956想要查找出 哪两个人有可能曾经遇到过
比如McCubbin, Fred 死于1880,而Matisse, Henri1 1869年出生且活到1954年 所以他们遇到过困扰在这个地方有几天了 还是没相处解决的办法 望有高手帮忙下呢。
Artistname BORN DIED
--------------------------------
Jones, Henry 1965
Matisse, Henri 1869 1954
McCubbin, Fred 1840 1880
Michael, Jackson 1940
Nolan, Adelaide 1950
Nolan, Sydney 1917 1992
Nolan, Victoria 1955
Pollock, Jackson 1912 1956想要查找出 哪两个人有可能曾经遇到过
比如McCubbin, Fred 死于1880,而Matisse, Henri1 1869年出生且活到1954年 所以他们遇到过困扰在这个地方有几天了 还是没相处解决的办法 望有高手帮忙下呢。
这样行不?
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (Artistname VARCHAR(20),BORN INT,DIED INT)
INSERT INTO @T
SELECT 'Jones, Henry',null,1965 UNION ALL
SELECT 'Matisse, Henri',1869,1954 UNION ALL
SELECT 'McCubbin, Fred',1840,1880 UNION ALL
SELECT 'Michael,Jackson',1940,null UNION ALL
SELECT 'Nolandelaide',1950,null UNION ALL
SELECT 'Nolan, Sydney',1917,1992 UNION ALL
SELECT 'Nolan, Victoria',1955,null UNION ALL
SELECT 'Pollock, Jackson',1912,1956--SQL查询如下:SELECT *
FROM @T AS A
WHERE EXISTS(SELECT * FROM @T
WHERE Artistname<>A.Artistname
AND BORN<A.DIED AND DIED<A.DIED
)
UNION
SELECT *
FROM @T AS A
WHERE EXISTS(SELECT * FROM @T
WHERE Artistname<>A.Artistname
AND BORN>A.BORN AND BORN<A.DIED
)/*
Artistname BORN DIED
-------------------- ----------- -----------
Jones, Henry NULL 1965
Matisse, Henri 1869 1954
McCubbin, Fred 1840 1880
Nolan, Sydney 1917 1992
Pollock, Jackson 1912 1956(5 行受影响)
*/
declare @tab table (
Artistname varchar(32), BORN int, DIED int )
insert into @tab values('Jones, Henry ', null , 1965 )
insert into @tab values('Matisse, Henri ', 1869 , 1954 )
insert into @tab values('McCubbin, Fred ', 1840 , 1880 )
insert into @tab values('Michael, Jackson ', 1940 , null )
insert into @tab values('Nolan, Adelaide ',1950 , null )
insert into @tab values('Nolan, Sydney ',1917 , 1992 )
insert into @tab values('Nolan, Victoria ',1955 , null )
insert into @tab values('Pollock, Jackson ', 1912 , 1956 )select * from @Tab A where exists(select 1 from @Tab where isnull(A.DIED,0) > isnull(BORN ,0))
AND Artistname ='Jones, Henry'/*
Artistname BORN DIED
-------------------------------- ----------- -----------
Jones, Henry NULL 1965*/
EXISTS和NOT EXISTS
如果一个子查询返回任何的行,则EXISTS subquery为FALSE。例如:SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);过去,EXISTS子查询以SELECT *为开始,但是可以以SELECT 5或SELECT column1或其它的为开始。MySQL在这类子查询中忽略了SELECT清单,因此没有区别。对于前面的例子,如果t2包含任何行,即使是只含有NULL值的行,EXISTS条件也为TRUE。这实际上是一个不可能的例子,因为基本上所有的[NOT] EXISTS子查询均包含关联。以下是一些更现实的例子:· 哪些种类的商店出现在一个或多个城市里?· SELECT DISTINCT store_type FROM stores· WHERE EXISTS (SELECT * FROM cities_stores· WHERE cities_stores.store_type = stores.store_type);· 哪些种类的商店没有出现在任何城市里?· SELECT DISTINCT store_type FROM stores· WHERE NOT EXISTS (SELECT * FROM cities_stores· WHERE cities_stores.store_type = stores.store_type);· 哪些种类的商店出现在所有城市里?· SELECT DISTINCT store_type FROM stores s1· WHERE NOT EXISTS (· SELECT * FROM cities WHERE NOT EXISTS (· SELECT * FROM cities_stores· WHERE cities_stores.city = cities.city· AND cities_stores.store_type = stores.store_type));最后一个例子是一个双嵌套NOT EXISTS查询。也就是,该查询包含一个NOT EXISTS子句,该子句又包含在一个NOT EXISTS子句中。该查询正式地回答了这个问题,“是否有某个城市拥有没有列在Stores中的商店?”。可以比较容易的说,一个带嵌套的NOT EXISTS可以回答这样的问题,“是否对于所有的y,x都为TRUE?”
Michael, Jackson Nolan, Adelaide 说明他们遇到过
Michael, Jackson Nolan, Victoria 他们也遇到过
Nolan, Victoria Pollock, Jackson 这个也是每个人对每个人是相对应的。。要和其他的所有人比较过 然后列出各种情况 接着排除重复的
SELECT * INTO #DDD FROM CCC
SELECT CCC.Artistname,#DDD.Artistname FROM CCC LEFT JOIN #DDD
ON ( (CCC.BORN>=#DDD.BORN AND CCC.BORN<=#DDD.DIED) OR (CCC.DIED>=#DDD.BORN AND CCC.DIED<=#DDD.DIED) )
AND (CCC.Artistname != #DDD.Artistname)试试这个应该没错
CCC就是你的表名
如果有哪里不对,说声,我在看看