SQL语句使用自连接 查询pubs数据库时出现差错,怎么回事?select distinct t1.type,t1.price
from titles as t1 inner join titles as t2
on t1.type =t2.type and t1.price <> t2.price
where t1.price<$15 and t2.price<$15
go比如我里面的有两项的类型不一样,它居然给我排掉了,怎么回事?
而and t1.price <> t2.price这句不用就可以出来?类型不同,价格相同居然会被排掉? 为啥?而且还只有前两行信息会如此,而后面的则不会!忘高手帮忙解析下 谢谢!
from titles as t1 inner join titles as t2
on t1.type =t2.type and t1.price <> t2.price
where t1.price<$15 and t2.price<$15
go比如我里面的有两项的类型不一样,它居然给我排掉了,怎么回事?
而and t1.price <> t2.price这句不用就可以出来?类型不同,价格相同居然会被排掉? 为啥?而且还只有前两行信息会如此,而后面的则不会!忘高手帮忙解析下 谢谢!
最好以数据来说明问题
如下两个?要哪个?select type , price from titles where price <$15/*
type price
------------ ---------------------
business 11.9500
business 2.9900
mod_cook 2.9900
psychology 10.9500
psychology 7.0000
psychology 7.9900
trad_cook 11.9500
trad_cook 14.9900(所影响的行数为 8 行)
*/select distinct type , price from titles where price <$15
/*
type price
------------ ---------------------
business 2.9900
business 11.9500
mod_cook 2.9900
psychology 7.0000
psychology 7.9900
psychology 10.9500
trad_cook 11.9500
trad_cook 14.9900(所影响的行数为 8 行)*/
WHERE EXISTS(SELECT 1 FROM TB WHERE type =t.type and price <> t.price )
AND price <$15
INSERT @TB
SELECT 1,15 UNION ALL
SELECT 1,17 UNION ALL
SELECT 2,19 UNION ALL
SELECT 2,16 UNION ALL
SELECT 3,14 UNION ALL
SELECT 3,13SELECT * FROM @TB T
WHERE EXISTS(SELECT 1 FROM @TB WHERE type =t.type and price <> t.price )
AND price <$15
(所影响的行数为 6 行)TYPE PRICE
----------- -----------
3 14
3 13(所影响的行数为 2 行)楼主是这个意思吧
where not exists(select 1 from titles where type=a.type and price<>a.price and price <$15 )
and a.price <$15
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
select distinct t1.type,t1.price
from titles as t1 inner join titles as t2
on t1.type =t2.type and t1.price <> t2.price
where t1.price <$15 and t2.price <$15
go
查询结果为: type 为类型列, price为价格列.
type price
business 2.99
psychology 7.00
psychology 7.99
psychology 10.95
business 11.95
trad_cook 11.95
trad_cook 14.99(2)
select distinct t1.type,t1.price
from titles as t1 inner join titles as t2
on t1.type =t2.type //这里与上一个语句相比少了一个条件
where t1.price <$15 and t2.price <$15
go
查询结果:
type price
business 2.99
mod_cook 2.99
psychology 7.00
psychology 7.99
psychology 10.95
business 11.95
trad_cook 11.95
trad_cook 14.99疑惑:第一个语句得到的答案将:mod_cook 2.99 这行给排除了,为啥?
select distinct type , price from titles where price <$15
/*
type price
------------ ---------------------
business 2.9900
business 11.9500
mod_cook 2.9900
psychology 7.0000
psychology 7.9900
psychology 10.9500
trad_cook 11.9500
trad_cook 14.9900(所影响的行数为 8 行)*/