insert into yourtable
select t1.date, case when flag = 0 then 1 else 0 end, 0
from yourtable t1
where not exists (select * from yourtable t2 where t1.date = t2.date and t1.flag <> t2.flag)select * from yourtable
select t1.date, case when flag = 0 then 1 else 0 end, 0
from yourtable t1
where not exists (select * from yourtable t2 where t1.date = t2.date and t1.flag <> t2.flag)select * from yourtable
insert @a values('2002/5', 0 , 320)
insert @a values('2002/5', 1 , 550)
insert @a values('2002/6', 0 , 100)
insert @a values('2002/6', 1 , 300)
insert @a values('2002/7', 1 , 500)select c.date,c.flag,isnull(d.count,0) as count from
(select * from (select distinct flag from @a) a,
(select distinct date from @a) b) c
left join @a d on c.date=d.date and c.flag=d.flagresult:
date flag count
---------- ---- ------------
2002/5 0 320.00
2002/5 1 550.00
2002/6 0 100.00
2002/6 1 300.00
2002/7 0 .00
2002/7 1 500.00
union
select t1.date, case when t1.flag = 0 then 1 else 0 end as flag, 0 as count
from yourtable t1
where not exists (select * from yourtable t2 where t1.date = t2.date and t1.flag <> t2.flag)
select * from yourtable ...
我是不是要建一个临时表,或者还有什么更好的方法?
还望saucer(思归)兄不吝赐教,多谢,多谢!
:)