select d.end from 表 d where not exists
(select 1 from (select a.* from 表 a ,表 b where a.end = b.start and a.step +1 = b.step) c where c.step = d.step and c.start = d.start and c.end = d.end)
(select 1 from (select a.* from 表 a ,表 b where a.end = b.start and a.step +1 = b.step) c where c.step = d.step and c.start = d.start and c.end = d.end)
解决方案 »
- 一个存储过程提示:数据库引擎的实例此时无法获得 LOCK 资源
- 数据库工程师必备知识
- |zyciis| 在数据库中表设置是不是要去掉自增的ID呢,有时很麻烦,第二贴 大家来讨论
- 再问一个时间随机生成的SQL语句
- 如何自动备份作业,注意,我不想备份整个库(太大了),只想备份作业
- 请问在SQL2005里面怎么把数据库中所有表字段为char的修改为nvarhcar的语句?
- 试了好几次,报错误的一条语句..关于if()..关联两个表..在线等结贴 (刚才忘给分了,现在补上)
- 视图是个怎样的概念它和表有什么区别和联系?
- 关于视图是否可以提高查询效率?
- 如何配置这些参数?
- 有用过SQL Server 6.5的兄弟吗?帮个忙
- 如何反向导出数据库结构文档
d.[end]
from
表名 d
where not exists
(select
1
from
(select
a.*
from
表名 a ,表名 b
where
a.[end] = b.start and a.step +1 = b.step) c
where
c.step = d.step and c.start = d.start and c.[end]= d.[end])
select distinct end from table where end not in (select distinct start from table )
to:wutao411(了缘),dulei115() 如果第四步麻六又把该物给张三了 则当前该物的位置应该在张三和王五手上,而用你们的方法查询出来的是不会显示张三的
select a.end
from (select end, max(step) as step
from tablename
group by end) a
left join (select start, max(step) as step
from tablename
group by start) b on a.end = b.strart and a.step > isnull(b.step, 0)
1 张三 李四
1 张三 王五
2 李四 张三应该出来的结果是 张三和王五
但用你的sql语句出来的是 张三,李四,王五
from test t
where not exists (select 1 from test where start = t.end)
from (select [end], max(step) as step
from tablename
group by [end]) a
left join (select start, max(step) as step
from tablename
group by start) b on a.[end] = b.start
where a.step > isnull(b.step, 0)
select a.[end]
from (select [end], max(step) as step
from tablename
group by [end]) a
left join (select start, max(step) as step
from tablename
group by start) b on a.[end] = b.start
where a.step > isnull(b.step, 0)
select a.[end]
from (select [end], max(step) as step
from tablename
group by [end]) a
left join (select start, max(step) as step
from tablename
group by start) b on a.[end] = b.start
where a.step > isnull(b.step, 0)
if object_id('tablename') is not null
drop table tablename
select 1 as step, '张三' as start, '李四' as [end]
into tablename
union
select 1, '张三', '王五'
union
select 2, '李四', '张三'
union
select 3, '张三', '麻六'select * from tablenameselect a.[end]
from (select [end], max(step) as step
from tablename
group by [end]) a
left join (select start, max(step) as step
from tablename
group by start) b on a.[end] = b.start
where a.step > isnull(b.step, 0)drop table tablename/*
1 张三 李四
1 张三 王五
2 李四 张三
3 张三 麻六
*//*
麻六
王五
*/
if object_id('tablename') is not null
drop table tablename
select 1 as step, '张三' as start, '李四' as [end]
into tablename
union
select 1, '张三', '王五'
union
select 2, '李四', '张三'select * from tablenameselect a.[end]
from (select [end], max(step) as step
from tablename
group by [end]) a
left join (select start, max(step) as step
from tablename
group by start) b on a.[end] = b.start
where a.step > isnull(b.step, 0)drop table tablename/*
1 张三 李四
1 张三 王五
2 李四 张三
*//*
王五
张三
*/
李四
麻六
王五
张三
*/
你的代码放在查询分析器里没有任何问题 结果是对的
但如果直接在sqlserver的sql窗口里做 查出来的结果是所有的人
但放在sqlserver的sql窗口中 查出来的结果就是所有的人 真是奇怪
????????
什么意思
SELECT a.[end]
FROM (SELECT [end], MAX(step) AS step
FROM tablename
GROUP BY [end]) a LEFT OUTER JOIN
(SELECT start, MAX(step) AS step
FROM tablename
GROUP BY start) b ON a.step > ISNULL(b.step, 0) AND a.[end] = b.start至于为什么会变成这样,我就不知道了!
http://community.csdn.net/Expert/topic/3669/3669085.xml?temp=.8003351