表t(a,b,c)有如下数据:
a b c
0 0 1
0 1 2
————
1 3 1
1 5 2
————
2 4 1
2 9 2
数据规则如下:每个a对应2条数据,2条数据中c为1和2,b随机。
现在要用一个sql查询出以下数据:
a b1 b2
0 0 1
1 3 5
2 4 9
就是把表t的两条数据合并为查询结果中的一条数据?如何写?
a b c
0 0 1
0 1 2
————
1 3 1
1 5 2
————
2 4 1
2 9 2
数据规则如下:每个a对应2条数据,2条数据中c为1和2,b随机。
现在要用一个sql查询出以下数据:
a b1 b2
0 0 1
1 3 5
2 4 9
就是把表t的两条数据合并为查询结果中的一条数据?如何写?
from t
group by a
2 (select a, b as b1 , lead(b)over(partition by a order by c) as b2 from test_t )
3 where b2 is not null
4 / A B1 B2
--------------------------------------- --------------------------------------- ----------
0 0 1
1 3 5
2 4 9
to xiaoxiao1984(笨猫儿):可否讲解以下?
2 a number(1),b number(1),c number(1)
3 );Table createdSQL> insert into wiler_cs values(0, 0, 1);1 row insertedSQL> insert into wiler_cs values(0 ,1, 2);1 row insertedSQL> insert into wiler_cs values(1 ,3, 1);1 row insertedSQL> insert into wiler_cs values(1 ,5, 2);1 row insertedSQL> insert into wiler_cs values(2 ,4, 1);1 row insertedSQL> insert into wiler_cs values(2 ,9, 2);1 row insertedSQL> commit;Commit completeSQL> select a,min(b) b1,max(b) b2
2 from wiler_cs
3 group by a; A B1 B2
-- ---------- ----------
0 0 1
1 3 5
2 4 9不好意思,上面搞错了,:)
分析函数,可以在同一行内显示前/后数行的内容
google或者baidu一下分析函数,大都有比较详细的介绍
所以,如果数据为:
0 1 1
0 0 2
你的方法得到的结果并非我预期的结果。
升星拉,恭喜恭喜,咋不散点分呢
猫猫等散分等了好久呢
^_^
2 a number(1),b number(1),c number(1)
3 );Table createdSQL> insert into wiler_cs values(0, 0, 1);1 row insertedSQL> insert into wiler_cs values(0 ,1, 2);1 row insertedSQL> insert into wiler_cs values(1 ,3, 1);1 row insertedSQL> insert into wiler_cs values(1 ,5, 2);1 row insertedSQL> insert into wiler_cs values(2 ,4, 1);1 row insertedSQL> insert into wiler_cs values(2 ,9, 2);1 row insertedSQL> commit;Commit completeSQL> with
2 tab_a as (
3 select a,min(c) c1,max(c) c2
4 from wiler_cs
5 group by a
6 )
7 select tt1.a,tt1.b1,tt2.b2
8 from (
9 select t1.a,b b1
10 from wiler_cs t1,tab_a t2
11 where t1.a=t2.a and t1.c=t2.c1
12 ) tt1,(
13 select t1.a,b b2
14 from wiler_cs t1,tab_a t2
15 where t1.a=t2.a and t1.c=t2.c2
16 ) tt2
17 where tt1.a=tt2.a; A B1 B2
-- -- --
0 0 1
1 3 5
2 4 9
感觉你的方法更符合我的情况一些,不过,我执行报错
SQL statement doesn't return rows/* Formatted on 2006/09/25 17:32 (Formatter Plus v4.8.0) */
WITH tab_a AS
(SELECT a, MIN (c) c1, MAX (c) c2
FROM t
GROUP BY a)
SELECT tt1.a, tt1.b1, tt2.b2
FROM (SELECT t1.a, b b1
FROM t t1, tab_a t2
WHERE t1.a = t2.a AND t1.c = t2.c1) tt1,
(SELECT t1.a, b b2
FROM t t1, tab_a t2
WHERE t1.a = t2.a AND t1.c = t2.c2) tt2
WHERE tt1.a = tt2.a
我的脚本要9i以上才能执行,忘了告诉你了,不好意思
SQL> SELECT b1.a,b1.b b1,b2.b b2 FROM t1 b1, t1 b2 WHERE b1.a = b2.a AND b1.c<b2.c; A B1 B2
---------- ---------- ----------
0 0 1
1 3 5
2 4 9
2 FROM t1 b1, t1 b2
3 WHERE b1.a = b2.a AND b1.c<b2.c; A B1 B2
---------- ---------- ----------
0 0 1
1 3 5
2 4 9
这个看的清楚些!!
其实数据还有可能是这样的:
a b c
0 0 1
0 7 2
0 1 4
————
1 3 1
1 6 2
1 5 3
————
2 4 1
2 9 2
结果和原来一样。
select a,
sum(case c when 1 then b end) as b1,
sum(case c when 2 then b end) as b2
from t where group by a
..........................................
给分之后 给你写个动态的确-_-
set @sql='select a'
select @sql=@sql+',sum(case c when '+ltrim(str(c))+' then b end) as [b' + ltrim(str(c)) + ']' from (select distinct c from t ) a
select @sql=@sql+' from t group by a'
print @sql
exec(@sql)
//c 字段要是数字类型
0 0 1
0 7 2
0 1 4
————
1 3 1
1 6 2
1 5 3
————
2 4 1
2 9 2
这个还要类似于
a b1 b2
0 0 1
1 3 5
2 4 9
这样的结果么?楼主是这个意思?
a b c
0 0 1
0 7 2
0 1 4
0 1 1
????
比如:
id a b c
1 0 0 1
2 0 7 2
3 0 1 4
4 0 1 1
????
2、说明下C的取值范围
3、说明要取值的B对应的C的值有无要求,如C有1、2、4时,取哪两条
1、表的唯一性应该是(a,c)。
2、c没有特定的取值范围。
3、取值的b对应的c值为group a里的最小值和最大值。
如果按照这一条来说c是不是不一定为1或者2了,也可能为3,4,5 其余的什么了吧?
——其实这个问题是我对实际问题的抽象,字段类型就是int型就可以。如果按照这一条来说c是不是不一定为1或者2了,也可能为3,4,5 其余的什么了吧?
——对。
下面是我的思路:
declare @t table(a int, b int ,c int)
insert @t select 0, 0, 1
union all select 0, 1, 2
union all select 0, 2, 3
union all select 1, 3, 1
union all select 1, 4, 2
union all select 1, 5, 3
union all select 2, 6, 1
union all select 2, 7, 2
union all select 2, 8, 3--select * from @tselect a, b, c, cast(a as varchar) + cast(c as varchar) as d from @t where c=1 or c=2 select max(b) from @t where c=1 or c=2 group by a
select min(b) from @t where c=1 or c=2 group by aselect table1.a, table2.b, table3.b from (select distinct a from @t where c=1 or c=2) table1
inner join (select a, max(b) as b from @t where c=1 or c=2 group by a) table2 on table1.a=table2.a
inner join (select a, min(b) as b from @t where c=1 or c=2 group by a) table3 on table1.a=table3.a-----------------
应该还有表的方法,上面的结果仅仅是对于c 限于 1和2之间取值的
如果按照c之可能为3\4之类的话
你可以把条件更换成
where c=(select max(c) from @t group by a) or c=(select min(c) from @t group by a)
这句
where c=(select max(c) from @t group by a) or c=(select min(c) from @t group by a)
错了如果按照c=1 or c= 2
select table1.a, table2.b, table3.b from (select distinct a from @t where c=1 or c=2) table1
inner join (select a, max(b) as b from @t where c=1 or c=2 group by a) table2 on table1.a=table2.a
inner join (select a, min(b) as b from @t where c=1 or c=2 group by a) table3 on table1.a=table3.a
这个可以
但效率不高
csdn不能连续发超过3张帖子。
取值的b对应的c值为group a里的最小值和最大值。
select t1.a a,t1.b b1,t2.b b2 from t t1,t t2 where t1.a = t2.a
and t1.c in (select min(c) from t where t.a = t1.a group by a)
and t2.c in (select max(c) from t where t.1 = t2.a group by a)