--结果1
select * from consume_main_m where stationno=-10
--结果2
select a.cardid,a.consumeorder,a.curtime,a.stationno,a.consume,a.spare,a.computer_id,a.manage_id,a.manage_name from consume_main_m a,
(
select * from consume_main_m
where
convert(varchar(10),curtime,120)>='2009-08-25'
) b
where a.cardid=b.cardid and a.consumeorder=b.consumeorder and
a.consume=b.consume and a.spare=b.spare and a.stationno!=b.stationno
--try:
select * from consume_main_m t where stationno=-10
and not exists
(select 1 from consume_main_m a,
(select * from consume_main_m where convert(varchar(10),curtime,120)>='2009-08-25') b
where a.cardid=b.cardid and a.consumeorder=b.consumeorder and a.consume=b.consume
and a.spare=b.spare and a.stationno!=b.stationno and cardid=t.cardid)
;with f1 as
(
select * from consume_main_m where stationno=-10
)
f2 as
(
select a.cardid,a.consumeorder,a.curtime,a.stationno,a.consume,a.spare,a.computer_id,a.manage_id,a.manage_name from consume_main_m a,
(
select * from consume_main_m
where
convert(varchar(10),curtime,120)>='2009-08-25'
) b
where a.cardid=b.cardid and a.consumeorder=b.consumeorder and
a.consume=b.consume and a.spare=b.spare and a.stationno!=b.stationno
)
select
*
from
f2
where
not exists(select 1 from f1 where stationno=f2.stationno)
select * from consume_main_m t where stationno=-10
and not exists
(select 1 from consume_main_m a,
(select * from consume_main_m where convert(varchar(10),curtime,120)>='2009-08-25') b
where a.cardid=b.cardid and a.consumeorder=b.consumeorder and a.consume=b.consume
and a.spare=b.spare and a.stationno!=b.stationno
and a.cardid=t.cardid)
