查询结果表与原表比较查询

[q85958341]

之前照书写过此，忘了！
我要比较consume_main表
查询结果表与consume_main原表比较
查询出满足的结果~where a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno


我的语句

select * from (
select * from consume_main b
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a
where a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

[忆轩辕]

select * from (
select * from consume_main b
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a,b
where a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

这样？

[jia_guijun]

select * from (
select * from consume_main b
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a
join consume_main b on
a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

[忆轩辕]

select * from (
select * from consume_main b
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a,b
where a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

这样？

[jia_guijun]

select * from (
select * from consume_main 
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a
join consume_main b on
a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

[octwind]

select * from (
select * from consume_main
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a
inner join consume_main b 
on a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

[q85958341]

[Quote=引用 1 楼 colacat911 的回复:]
SQL codeselect*from (select*from consume_main bwhere stationnoin (-10,153,158,183,189)andconvert(varchar(10),curtime,120)>='2009-08-01'andconvert(varchar(10),curtime,120)<='2009-08-31'
) a,bwhere a.cardid=b.cardidand a.consumeorder=b.consumeorderand a.consume=b.consumeand a.stationno<>b.stationno

这样？
[/Quote]

不是~
消息 208，级别 16，状态 1，第 2 行
对象名 'b' 无效。

[q85958341]

上次我按书写的
类似于 嵌套吧~

[zhousq00]

select * from (
select * from consume_main b
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a,consume_main c
where a.cardid=c.cardid
and a.consumeorder=c.consumeorder
and a.consume=c.consume
and a.stationno<>c.stationno

[忆轩辕]

select * from (
select * from consume_main
where stationno in (-10,153,158,183,189)
and convert(varchar(10),curtime,120)>='2009-08-01'
and convert(varchar(10),curtime,120)<='2009-08-31'
) a,consume_main b
where a.cardid=b.cardid
and a.consumeorder=b.consumeorder
and a.consume=b.consume
and a.stationno<>b.stationno

写得有点问题，改好了

难道你是想要相关子查询？

[q85958341]

！相关子查询 的语法能发下么？

[q85958341]

我思路存在问题！我重新想想~

[q85958341]

select sum(a.consume)--a.* 
from consume_main a
Where  a.stationno=-10 and 
(not Exists(select top 1 1 from consume_main b
           where stationno>0 and convert(varchar(10),curtime,120)>='2009-08-01' 
           and b.cardid=a.cardid and b.consumeorder= a.consumeorder and b.consume=a.consume and b.spare=a.spare )
)
and convert(varchar(7),curtime,120)='2009-08'