如何用SQL实现如下功能。（SQL高手进，在线等）

rownum是选出数据后才自动确定的，恐怕拟用的有误。我最烦表名、字段名用中文表示了，没心情看了。

解决方案 »

  1.   

    天，用oracle的表名不用中文，那还不如去用sybase好了，表名只支持一个中文字，那就是“表”，别个一个都不支持。
      

  2.   

    select a.病人id,a.序号,a.序号-b.row1+b.row2 序号2,a.信息行 from
    （select 病人id,rownum-1  序号,row2  序号2,收据费目||':'||ltrim(TO_CHAR(金额),'999990.00')||'┊'  信息行
          from 
            (select 病人id,rownum  row2,收据费目,Sum(实收金额)  金额
             from 病人费用记录 
          where 登记时间 between sysdate-9 and sysdate-8
          group by 病人id,rownum,收据费目
          having Sum(实收金额)<>0)）A,
    (select 病人id,max(rownum)  row1,count(*)  row2
             from 病人费用记录 
          where 登记时间 between sysdate-9 and sysdate-8
          group by 病人id) B
    where a.病人id=b.病人id(+);
      

  3.   

    如果你的oracle版本是8.1.6及以上版本，可以用分析函数实现，否则需要写自定义函数来解决。
    分析函数的方法：
    select 病人id,rownum-1 as 序号,row2-1 as 序号2,收据费目||':'||ltrim(TO_CHAR(金额),'999990.00')||'┊' as 信息行
          from 
            (select 病人id,sum(1) over(partition by 病人id oder by rownum) as row2,收据费目,实收金额 as 金额
             from 病人费用记录 
          where 登记时间 between sysdate-9 and sysdate-8
            and 实收金额<>0) A
      

  4.   

    bzszp(SongZip) ，你给我的数据源中，实现了对row2的递增算法，但是，针对每个病人id的起始row2不是0，而是一个随机数，还有很多是负数，请继续指教。
      

  5.   

    你的sql执行效果如下：
                 病人ID       序号      序号2 信息行
    ------------------- ---------- ---------- ---------------------------------------------------------------
                    268          0        -26 诊疗费:1┊
                    268          1        -25 诊疗费:1┊
                    268          2        -24 诊疗费:1┊
                    268          3        -23 诊疗费:1┊
                    268          4        -22 诊疗费:1┊
                    268          5        -21 诊疗费:1┊
                    268          6        -20 诊疗费:1┊
                    268          7        -19 诊疗费:1┊
                    268          8        -18 诊疗费:1┊
                    268          9        -17 诊疗费:1┊
                    268         10        -16 诊疗费:1┊
                    268         11        -15 诊疗费:1┊
                    268         12        -14 诊疗费:1┊
                    268         13        -13 诊疗费:1┊
                    268         14        -12 诊疗费:1┊
                    268         15        -11 诊疗费:1┊
                    292         16         16 诊疗费:1┊
                    292         17         17 诊疗费:1┊
                    292         18         18 诊疗费:1┊
                    292         19         19 诊疗费:1┊             病人ID       序号      序号2 信息行
    ------------------- ---------- ---------- ---------------------------------------------------------------
                    292         20         20 诊疗费:1┊
                    292         21         21 诊疗费:1┊
                    292         22         22 诊疗费:1┊
                    292         23         23 诊疗费:1┊
                    292         24         24 诊疗费:1┊
                    292         25         25 诊疗费:1┊
                    292         26         26 诊疗费:1┊
                    292         27         27 诊疗费:1┊
                    292         28         28 诊疗费:1┊
                    294         29         16 诊疗费:1┊
                    294         30         17 诊疗费:1┊
                    294         31         18 诊疗费:1┊
                    294         32         19 诊疗费:1┊
                    294         33         20 诊疗费:1┊
                    294         34         21 诊疗费:1┊
                    294         35         22 诊疗费:1┊
                    294         36         23 诊疗费:1┊
                    294         37         24 诊疗费:1┊
                    294         38         25 诊疗费:1┊
                    294         39         26 诊疗费:1┊
                    294         40         27 诊疗费:1┊             病人ID       序号      序号2 信息行
    ------------------- ---------- ---------- ---------------------------------------------------------------
                    294         41         28 诊疗费:1┊
                    304         42         -2 材料费:10┊
                    306         43         -4 中草药:3.33┊
                    306         44         -3 西药费:2254┊
                    307         45        -10 西药费:22580┊
                    307         46         -9 西药费:58.7┊
                    307         47         -8 西药费:700┊
                    307         48         -7 西药费:676.2┊
                    307         49         -6 西药费:700┊
                    307         50         -5 西药费:676.2┊
                    307         51         -4 治疗费:10┊
                    308         52         -3 西药费:┊
                    308         53         -2 西药费:225.8┊
                    308         54         -1 治疗费:3┊
                    308         55          0 西药费:┊
                    308         56          1 西药费:225.8┊
                    308         57          2 治疗费:3┊
                                58            西药费:225.8┊
                                59            化验费:10┊
                                60            挂号费:5┊
                                61            诊疗费:3┊62 rows selected
      

  6.   

    select 病人id,rownum-1 序号,row_number() over(partition by 病人id order by rownum) 序号2,收据费目||':'||ltrim(TO_CHAR(nvl(金额,0)),'999990.00')||'┊' 信息行
    from 病人费用记录 
    where 登记时间 between sysdate-9 and sysdate-8
      

  7.   

    我没看出有什么问题
    你执行以下代码，把执行情况贴出来看看select 病人id,rownum  row2,收据费目,Sum(实收金额)  金额
             from 病人费用记录 
          where 登记时间 between sysdate-9 and sysdate-8
          group by 病人id,rownum,收据费目
          having Sum(实收金额)<>0;select 病人id,max(rownum)  row1,count(*)  row2
             from 病人费用记录 
          where 登记时间 between sysdate-9 and sysdate-8
          group by 病人id;还有上次贴的代码
      

  8.   

    sorry:要使用分组如下：select 病人id,rownum-1 序号,row_number() over(partition by 病人id order by rownum) 序号2,max(收据费目||':'||ltrim(TO_CHAR(nvl(金额,0)),'999990.00')||'┊') 信息行
    from 病人费用记录 
    where 登记时间 between sysdate-9 and sysdate-8
    group by 病人id,rownum-1,row_number() over(partition by 病人id order by rownum)试试这个，有什么再讨论
      

  9.   

    beckhambobo(beckham) 你的方法已经解决了这个问题，十分感谢。请讲解
    “row_number() over(partition by 病人id order by rownum)”这段数据源的意义及这样做的想法。然后请讲解over，partiton by的具体用法，好么？
      

  10.   

    增加了分组的那个数据源反而不能通过。报row_number()此函数在windows中禁用，由于没有看懂这样写的原因，所以不知错在那里。
      

  11.   

    bzszp(SongZip) 
    我对你的数据源进行了修改，将“a.序号-b.row1+b.row2 序号2”改为
    “-1-b.row1+b.row2+a.序号2 序号2”，这样改后可以实现大多数情况，但是仍有例外，比如病人id为“307”时：
                 病人ID       序号      序号2 信息行
    ------------------- ---------- ---------- ---------------------------------------------------------------
                    268          0          0 诊疗费:1┊
                    268          1          1 诊疗费:1┊
                    268          2          2 诊疗费:1┊
                    268          3          3 诊疗费:1┊
                    268          4          4 诊疗费:1┊
                    268          5          5 诊疗费:1┊
                    268          6          6 诊疗费:1┊
                    268          7          7 诊疗费:1┊
                    268          8          8 诊疗费:1┊
                    268          9          9 诊疗费:1┊
                    268         10         10 诊疗费:1┊
                    268         11         11 诊疗费:1┊
                    268         12         12 诊疗费:1┊
                    268         13         13 诊疗费:1┊
                    268         14         14 诊疗费:1┊
                    268         15         15 诊疗费:1┊
                    292         16          0 诊疗费:1┊
                    292         17          1 诊疗费:1┊
                    292         18          2 诊疗费:1┊
                    292         19          3 诊疗费:1┊             病人ID       序号      序号2 信息行
    ------------------- ---------- ---------- ---------------------------------------------------------------
                    292         20          4 诊疗费:1┊
                    292         21          5 诊疗费:1┊
                    292         22          6 诊疗费:1┊
                    292         23          7 诊疗费:1┊
                    292         24          8 诊疗费:1┊
                    292         25          9 诊疗费:1┊
                    292         26         10 诊疗费:1┊
                    292         27         11 诊疗费:1┊
                    292         28         12 诊疗费:1┊
                    294         29          0 诊疗费:1┊
                    294         30          1 诊疗费:1┊
                    294         31          2 诊疗费:1┊
                    294         32          3 诊疗费:1┊
                    294         33          4 诊疗费:1┊
                    294         34          5 诊疗费:1┊
                    294         35          6 诊疗费:1┊
                    294         36          7 诊疗费:1┊
                    294         37          8 诊疗费:1┊
                    294         38          9 诊疗费:1┊
                    294         39         10 诊疗费:1┊
                    294         40         11 诊疗费:1┊             病人ID       序号      序号2 信息行
    ------------------- ---------- ---------- ---------------------------------------------------------------
                    294         41         12 诊疗费:1┊
                    304         42          0 材料费:10┊
                    306         43          0 中草药:3.33┊
                    306         44          1 西药费:2254┊
                    307         45         -6 西药费:22580┊
                    307         46         -5 西药费:58.7┊
                    307         47         -4 西药费:700┊
                    307         48         -3 西药费:676.2┊
                    307         49         -2 西药费:700┊
                    307         50         -1 西药费:676.2┊
                    307         51          6 治疗费:10┊
                    308         52          0 西药费:┊
                    308         53          1 西药费:225.8┊
                    308         54          2 治疗费:3┊
                    308         55          3 西药费:┊
                    308         56          4 西药费:225.8┊
                    308         57          5 治疗费:3┊
                                58            西药费:225.8┊
                                59            化验费:10┊
                                60            挂号费:5┊
                                61            诊疗费:3┊62 rows selected