还有一种办法(select value1,date1 from mytable where stat='1111' and date1>'2004-1-1') union (select value1,date1 from mytable where stat='2222' and date1>'2004-1-1')
select value1,date1 from mytable where (stat='1111' or stat='2222' ) and date1>'2004-1-1'
用UNION把你现有的两条SQL语句合并起来就可以了!试试看!
参考 create table #t1 ( a int, b int ) insert #t1 select 1, 10 insert #t1 select 2, 20create table #t2 ( a int, c int ) insert #t2 select 1, 100 insert #t2 select 2, 200 insert #t2 select 3, 300select distinct #t1.a, #t1.b, #t2.c from #t1 cross join #t2 where #t1.a = #t2.adrop table #t1, #t2
写存储过程吧,建立一个临时表 create table #mytable(value1 decimal(18,0),value decimal(18,0),date1 datetime) 然后插入数据insert #mytable(value1,date1) select value1,date1 from mytable where stat='1111' and date1>'2004-1-1' update #mytable set value = mytable.value1 from #mytable,mytable where #mytable.date1 = mytable.date1select * #mytable 就ok了.
落条件了. create table #mytable(value1 decimal(18,0),value decimal(18,0),date1 datetime) 然后插入数据insert #mytable(value1,date1) select value1,date1 from mytable where stat='1111' and date1>'2004-1-1' update #mytable set value = mytable.value1 from #mytable,mytable where #mytable.date1 = mytable.date1 and stat='2222' and date1>'2004-1-1'select * #mytable 就ok了. 可以添加输入参数和输出参数
呵呵,现在的高手可真多,一个查询就搞的大段大段的,试试这样写 select t1.value1,t1.date1,t2.data1 from mytable t1,mytable t2 where t1.stat='1111' and t2.star='2222' and t1.date1>'2004-1-1' and t1.date1 = t2.date1
select (case stat when '111' then value1 else 0) as value11,(case stat when '222' then value1 else 0) as value22,date1 from mytable where date1>'2004-1-1'
表结构: stat char 8 date smalldatime 9 value number 10
还有一种方法: select value1,(select value1 from mytable where stat='2222' and date1>'2004-1-1' ) as value,date1 from mytable where stat='1111' and date1>'2004-1-1' 你试试.
搂主看看我这个如何:/*先创建两个视图*/ CREATE VIEW IS_1111 AS Select date1, value1 from mytable where stat='1111' and date1>'2004-1-1' GOCREATE VIEW IS_2222 AS Select date1, value1 from mytable where stat='2222' and date1>'2004-1-1' GOSelect I1.date1 as date1, I1.value1 as value1, I2.value1 as value from IS_1111 I1 FULL OUTER JOIN IS_2222 I2 ON I1.date1 = I2.date2
没有猜错楼主意思得话,应该师这样: select (select sum(b.value1) from mytable b where b.stat='111' and a.date1=b.date1) as value11,(select sum(c.value1) from mytable c where c.stat='222' and a.date1=c.date1) as value22,a.date1 from mytable where a.date1>'2004-1-1' group by a.date1
因为查询出来的value1和value时间要一致的,一一对应的,要求的结果大致如下:. 并不是很简单的事情啊.
date1, value1, value
2004-1-1, 0.98, 0.10
2004-1-2, 0.5, 0.59
.....................
create table #t1
(
a int,
b int
)
insert #t1
select 1, 10
insert #t1
select 2, 20create table #t2
(
a int,
c int
)
insert #t2
select 1, 100
insert #t2
select 2, 200
insert #t2
select 3, 300select distinct #t1.a, #t1.b, #t2.c from #t1
cross join #t2
where #t1.a = #t2.adrop table #t1, #t2
create table #mytable(value1 decimal(18,0),value decimal(18,0),date1 datetime)
然后插入数据insert #mytable(value1,date1) select value1,date1 from mytable where stat='1111' and date1>'2004-1-1'
update #mytable set value = mytable.value1 from #mytable,mytable where #mytable.date1 = mytable.date1select * #mytable 就ok了.
create table #mytable(value1 decimal(18,0),value decimal(18,0),date1 datetime)
然后插入数据insert #mytable(value1,date1) select value1,date1 from mytable where stat='1111' and date1>'2004-1-1'
update #mytable set value = mytable.value1 from #mytable,mytable where #mytable.date1 = mytable.date1 and stat='2222' and date1>'2004-1-1'select * #mytable 就ok了.
可以添加输入参数和输出参数
select t1.value1,t1.date1,t2.data1
from mytable t1,mytable t2
where t1.stat='1111' and t2.star='2222' and t1.date1>'2004-1-1' and t1.date1 = t2.date1
stat char 8
date smalldatime 9
value number 10
select value1,(select value1 from mytable where stat='2222' and date1>'2004-1-1'
) as value,date1 from mytable where stat='1111' and date1>'2004-1-1' 你试试.
CREATE VIEW IS_1111
AS
Select date1, value1
from mytable
where stat='1111' and date1>'2004-1-1'
GOCREATE VIEW IS_2222
AS
Select date1, value1
from mytable
where stat='2222' and date1>'2004-1-1'
GOSelect I1.date1 as date1,
I1.value1 as value1,
I2.value1 as value
from IS_1111 I1 FULL OUTER JOIN IS_2222 I2
ON I1.date1 = I2.date2
select (select sum(b.value1) from mytable b where b.stat='111' and a.date1=b.date1) as value11,(select sum(c.value1) from mytable c where c.stat='222' and a.date1=c.date1) as value22,a.date1 from mytable where a.date1>'2004-1-1' group by a.date1
只有那位 shuazi1979(刷子) 才是菜鸟。