--测试一些数据!PASS with temp as ( select 1 a,2 b from dual union all select 1,3 from dual union all select 4,3 from dual union all select 7,3 from dual union all select 0,3 from dual union all select 7,9 from dual union all select 3,6 from dual union all select 8,3 from dual ) select a,b,a+b from temp order by a+b desc
可能这里没有说清楚, city num1 num2 flag pek 1000 0 1 can 0 2000 0 sha 0 1000 1 khn 1300 0 1 pek 0 1200 1 这个表中同一个city,比如pek,可能有多行记录,当flag为1时,num1有值,反之num2有值,通过sql我要的是形如下面格式: city num1 num2 flag pek 1000 0 1 pek 0 1200 0 can 0 2000 0 khn 1300 0 1 sha 1000 0 1 即按照num1+num2的总和逆序排列,并且同一个city的记录是连着的,没有被其它的记录隔开,例如前两行的pek,楼上老师说的应该是统计处num1+num2的值,然后按这个总和逆序排列,有可能出现 city num1 num2 flag pek 1000 0 1 can 0 2000 0 khn 1300 0 1 pek 0 1200 0 sha 1000 0 1 这种情况,所以还请老师指点!
order by city,t1.num1+t1.num2 desc就可以了
shiyiwan说的可能不太适合我这里,一方面我是需要按照city的num1+num2总和来逆序排列,如果用“order by city,t1.num1+t1.num2 desc”的话,就可能出现先按city排序,这样会出现按照city排序,但排在后面的city的num1+num2可能大于前面的city,另外,用order by的话效率实在太低,如果这里用索引的话是不是会好一些呢?关于索引的建法,在这里有什么技巧么?我要将: city num1 num2 flag pek 1000 0 1 can 0 2000 0 sha 0 1000 1 khn 1300 0 1 pek 0 1200 1 排序成: city num1 num2 flag pek 1000 0 1 pek 0 1200 0 can 0 2000 0 khn 1300 0 1 sha 1000 0 1
with temp as ( select 'pek' a,1000 b, 0 c, 1 d from dual union all select 'can', 0, 2000, 0 from dual union all select 'sha', 0, 1000, 1 from dual union all select 'khn', 1300, 0 , 1 from dual union all select 'pek', 0 , 1200, 1 from dual union all select 'khn', 0, 1500 , 1 from dual ) select a,b,c,case when c>0 then 0 else 1 end d from ( select t.*,row_number()over(partition by a order by b desc) from temp t ) a order by a
--result: can 0 2000 0 khn 1300 0 1 khn 0 1500 0 pek 1000 0 1 pek 0 1200 0 sha 0 1000 0
谢谢cosio的回答,您写的sql最后是按照city排序,如果city相同,则按照num排序的,但我需要的是按照num1+num2总和的逆排,同时要将num1+num2总和所对应的同一city放在一块,如果您的结果是: khn 1300 0 1 khn 0 1500 0 pek 1000 0 1 pek 0 1200 0 can 0 2000 0 sha 0 1000 0 就对了,不知道我是否阐述清楚,而且我用: select t1.city, t1.flag, t1.num1+t1.num2 from tb t1, (select city, sum(num1+num2) as num from tb group by city) t2 where t1.ciy=t2.city order by t2.num desc; 这个sql已经能够得到这个result,在本地DB中用小数据量做测试没有问题,关键是有800万的数据量在实际的DB中,所以这个sql执行起来肯定是不行的,不知道这个sql如果做优化哪里可以从哪里着想呢?另外如果用索引的话是不是好一些呢?谢谢!
大于800万的数据量排序,是用sql还是c语言做可行呢?高手请指点!
select t1.city, t1.flag, t1.num1+t1.num2,sum(t1.num1+t1.num2)over(partition by t1.city) from tb t1 order by 4 desc
sbaz您好, 我把您这段sql写在pc文件里: EXEC SQL DECLARE c5 CURSOR FOR SELECT city, flag, num1+num2, sum(num1+num2) over (partition by city) FROM tbl WHERE cki_date>=:sdate and cki_date<=:edate and airline=:airline order by 4 desc; EXEC SQL OPEN c5; 为什么总是提示sum(num1+num2) over (partition by city)中的“) over (”的r处 PCC-S-02201, Encountered the symbol "(" when expecting one of the following: , into, from, 我试了 ) over ( ) over( )over ( )over( 都不行,但我在plsql中用这个sql就没问题,请问是格式的问题么?谢谢!
如果使用动态sql可否解决这个分析函数的问题呢? char str_sql[300]; ... strcpy(str_sql, "SELECT city, flag, num1+num2, sum(num1+num2) over (partition by city) num FROM tbl WHERE cdate>=:sdate and cdate<=:edate and airline=:airline order by num desc); EXEC SQL PREPARE stat FROM :str_sql; EXEC SQL DECLARE c5 CURSOR FOR stat; EXEC SQL OPEN c5 USING :cdate, :cdate, :airline; 但总是提示: EXEC SQL PREPARE stat FROM :str_sql; 1 PCC-S-02021, Found newline while scanning string literal 这是怎么回事呢?格式不对?
--测试一些数据!PASS
with temp as
(
select 1 a,2 b from dual
union all
select 1,3 from dual
union all
select 4,3 from dual
union all
select 7,3 from dual
union all
select 0,3 from dual
union all
select 7,9 from dual
union all
select 3,6 from dual
union all
select 8,3 from dual
)
select a,b,a+b from temp order by a+b desc
city num1 num2 flag
pek 1000 0 1
can 0 2000 0
sha 0 1000 1
khn 1300 0 1
pek 0 1200 1
这个表中同一个city,比如pek,可能有多行记录,当flag为1时,num1有值,反之num2有值,通过sql我要的是形如下面格式:
city num1 num2 flag
pek 1000 0 1
pek 0 1200 0
can 0 2000 0
khn 1300 0 1
sha 1000 0 1
即按照num1+num2的总和逆序排列,并且同一个city的记录是连着的,没有被其它的记录隔开,例如前两行的pek,楼上老师说的应该是统计处num1+num2的值,然后按这个总和逆序排列,有可能出现
city num1 num2 flag
pek 1000 0 1
can 0 2000 0
khn 1300 0 1
pek 0 1200 0
sha 1000 0 1
这种情况,所以还请老师指点!
city num1 num2 flag
pek 1000 0 1
can 0 2000 0
sha 0 1000 1
khn 1300 0 1
pek 0 1200 1
排序成:
city num1 num2 flag
pek 1000 0 1
pek 0 1200 0
can 0 2000 0
khn 1300 0 1
sha 1000 0 1
(
select 'pek' a,1000 b, 0 c, 1 d from dual
union all
select 'can', 0, 2000, 0 from dual
union all
select 'sha', 0, 1000, 1 from dual
union all
select 'khn', 1300, 0 , 1 from dual
union all
select 'pek', 0 , 1200, 1 from dual
union all
select 'khn', 0, 1500 , 1 from dual
)
select a,b,c,case when c>0 then 0 else 1 end d from
(
select t.*,row_number()over(partition by a order by b desc) from temp t
) a
order by a
--result:
can 0 2000 0
khn 1300 0 1
khn 0 1500 0
pek 1000 0 1
pek 0 1200 0
sha 0 1000 0
khn 1300 0 1
khn 0 1500 0
pek 1000 0 1
pek 0 1200 0
can 0 2000 0
sha 0 1000 0
就对了,不知道我是否阐述清楚,而且我用:
select t1.city, t1.flag, t1.num1+t1.num2 from tb t1, (select city, sum(num1+num2) as num from tb group by city) t2 where t1.ciy=t2.city order by t2.num desc;
这个sql已经能够得到这个result,在本地DB中用小数据量做测试没有问题,关键是有800万的数据量在实际的DB中,所以这个sql执行起来肯定是不行的,不知道这个sql如果做优化哪里可以从哪里着想呢?另外如果用索引的话是不是好一些呢?谢谢!
我把您这段sql写在pc文件里:
EXEC SQL DECLARE c5 CURSOR FOR
SELECT city, flag, num1+num2, sum(num1+num2) over (partition by city)
FROM tbl
WHERE cki_date>=:sdate and cki_date<=:edate and airline=:airline
order by 4 desc;
EXEC SQL OPEN c5;
为什么总是提示sum(num1+num2) over (partition by city)中的“) over (”的r处
PCC-S-02201, Encountered the symbol "(" when expecting one of the following:
, into, from,
我试了
) over (
) over(
)over (
)over(
都不行,但我在plsql中用这个sql就没问题,请问是格式的问题么?谢谢!
char str_sql[300];
...
strcpy(str_sql, "SELECT city, flag, num1+num2, sum(num1+num2) over (partition by city) num FROM tbl WHERE cdate>=:sdate and cdate<=:edate and airline=:airline order by num desc);
EXEC SQL PREPARE stat FROM :str_sql;
EXEC SQL DECLARE c5 CURSOR FOR stat;
EXEC SQL OPEN c5 USING :cdate, :cdate, :airline;
但总是提示:
EXEC SQL PREPARE stat FROM :str_sql;
1
PCC-S-02021, Found newline while scanning string literal
这是怎么回事呢?格式不对?