文章/答案/技术大牛

发布

社区首页 >问答首页 >Oracle枢轴查询获取ora-00933

问Oracle枢轴查询获取ora-00933
EN

Stack Overflow用户

提问于 2014-01-03 13:53:58

回答 1查看 743关注 0票数 0

我想把我的结果转过来。

这是我的结果表：

CUMA        13:30   ORHAN SAVAS    
CUMA        14:00   FATMA ETA   
CUMA        14:30   ISMAHAN YALDIZ    
PAZARTESI   13:00   SEYHAN UNVER    
PAZARTESI   13:30   SELMA CALISKAN    
PAZARTESI   17:45   ESMA COMERT    
SALI        09:45   SEYMA DURLANIK    
SALI        10:00   HASAN GOC    
SALI        13:00   TURKAN BICAK    
SALI        14:30   ISMAHAN YALDIZ    
PERSEMBE    08:30   ZUHRE YEL    
PERSEMBE    08:48   AYSEL POLAT    
PERSEMBE    09:00   AHMET OZGUNGOR    
PERSEMBE    09:12   TELEFON RANDEVUSU

我可以像这样转换我的结果表吗？

CUMA                    PAZARTESI             SALI                  PERSEMBE
13:30 ORHAN SAVAS       13:00 SEYHAN UNVER    09:45 SEYMA DURLANIK  08:30 ZUHRE YEL
14:00 FATMA ETA         13:30 SELMA CALISKAN  10:00 HASAN GOC       08:48 AYSEL POLAT
14:30 ISMAHAN YALDIZ    17:45 ESMA COMERT     13:00 TURKAN BICAK    09:00 AHMET OZGUNGOR
                                              14:30 ISMAHAN YALDIZ  09:12 TELEFON RANDEVUSU

İt是我的查询：

WITH got_r_num  AS
(
    SELECT  TO_CHAR (t.r_tarihi, 'DY')      AS gun
    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat
    ,       h.adi || ' ' || h.soyadi        AS hasta
    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')
                                 ORDER BY   t.baslama
                               )            AS r_num
    FROM             randevu_entegre_deneme  t
    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id
    WHERE   (r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')
                        AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9)
)
SELECT   *
FROM      got_r_num PIVOT ( MIN (SAAT) AS s , MIN (HASTA) AS h
          FOR  gun IN ( 'MON'  AS monday
                      , 'TUE'  AS tuesday
                      , 'WED'  AS wednesday
                      , 'THU'  AS thursday
                      , 'FRI'  AS friday
                      )
          )
ORDER BY  r_num;

这是我的CREATE表和INSERT语句

create table RANDEVU_ENTEGRE_DENEME
(
  hasta_id                     INTEGER,
  baslama                      DATE,
  R_TARIHI                        DATE
);

insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (39733, to_date('24-05-2011 13:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (367216, to_date('23-05-2011 13:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('23-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (522956, to_date('20-05-2011 13:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (801923, to_date('23-05-2011 17:45:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('23-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (815746, to_date('24-05-2011 09:45:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (815746, to_date('20-05-2011 08:54:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (842677, to_date('20-05-2011 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (842677, to_date('24-05-2011 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (854143, to_date('26-05-2011 08:48:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (854559, to_date('23-05-2011 13:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('23-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (861624, to_date('20-05-2011 10:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('20-05-2011', 'dd-mm-yyyy'));
insert into RANDEVU_ENTEGRE_DENEME (hasta_id, baslama, r_tarihi)
values (868595, to_date('26-05-2011 08:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-05-2011', 'dd-mm-yyyy'));
commit;

create table HASTA_DENEME
(
  id                  INTEGER,
  adi                 VARCHAR2(25),
  soyadi              VARCHAR2(25)
);

insert into HASTA_DENEME (id, adi, soyadi)
values (39733, 'TURKAN', 'BICAK');
insert into HASTA_DENEME (id, adi, soyadi)
values (367216, 'SELMA', 'CALISKAN');
insert into HASTA_DENEME (id, adi, soyadi)
values (522956, 'ORHAN', 'SAVAS');
insert into HASTA_DENEME (id, adi, soyadi)
values (801923, 'ESMA', 'COMERT');
insert into HASTA_DENEME (id, adi, soyadi)
values (815746, 'SEYMA', 'DURLANIK');
insert into HASTA_DENEME (id, adi, soyadi)
values (842677, 'FATMA', 'ETA');
insert into HASTA_DENEME (id, adi, soyadi)
values (854143, 'AYSEL', 'POLAT');
insert into HASTA_DENEME (id, adi, soyadi)
values (854559, 'SEYHAN', 'UNVER');
insert into HASTA_DENEME (id, adi, soyadi)
values (861624, 'SENGUL', 'AKBAS');
insert into HASTA_DENEME (id, adi, soyadi)
values (868595, 'ZUHRE', 'YEL');
commit;

我得到了-00933。我怎么才能解决这个..。

oracle

pivot

rows

transpose

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-01-03 14:21:51

PIVOT操作符是在Oracle 11gR1中添加。试图在10 gR2中运行您的代码会导致错误：

FROM      got_r_num PIVOT ( MIN (SAAT) AS s , MIN (HASTA) AS h
                          *
ERROR at line 15:
ORA-00933: SQL command not properly ended

在11g之前，您需要使用不同的机制，就像这个。

我想你想要这样的东西：

WITH got_r_num  AS
(
    SELECT  TO_CHAR (t.r_tarihi, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS gun
    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat
    ,       h.adi || ' ' || h.soyadi        AS hasta
    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')
                                 ORDER BY   t.baslama
                               )            AS r_num
    FROM             randevu_entegre_deneme  t
    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id
    WHERE   (r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')
                        AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9)
)
SELECT    min(case when gun = 'MON' then SAAT ||' '|| HASTA end) as pazartesi
          , min(case when gun = 'TUE' then SAAT ||' '|| HASTA end) as sali
          , min(case when gun = 'WED' then SAAT ||' '|| HASTA end) as carsamba
          , min(case when gun = 'THU' then SAAT ||' '|| HASTA end) as persembe
          , min(case when gun = 'FRI' then SAAT ||' '|| HASTA end) as cuma
FROM      got_r_num
GROUP BY  r_num
ORDER BY  r_num;

使用您发布的表和insert语句提供：

PAZARTESI            SALI                 CARSAMBA             PERSEMBE             CUMA
-------------------- -------------------- -------------------- -------------------- --------------------
13:00 SEYHAN UNVER   09:45 SEYMA DURLANIK                      08:30 ZUHRE YEL      08:54 SEYMA DURLANIK
13:30 SELMA CALISKAN 13:00 TURKAN BICAK                        08:48 AYSEL POLAT    10:00 SENGUL AKBAS
17:45 ESMA COMERT    14:00 FATMA ETA                                                13:30 ORHAN SAVAS
                                                                                    14:00 FATMA ETA

..。这并不完全符合您的预期，但与数据和蓝脚的SQl Fiddle使用pivot一致。

我使用了你似乎期待的土耳其日标签；正如现实主义者所指出的，在发布问题时更改代码中的内容并不完全有帮助。这里的语言差异很重要，因为您的日常比较假设DY格式是以某种语言进行的--不管您实际使用的是英语还是土耳其缩写，关键是您假设客户端和代码使用的是同一种语言。您可以通过强制DY使用特定的语言来避免潜在的不匹配，因此您知道您将比较什么；这就是使用char()函数的可选的第三个参数所做的事情。

TO_CHAR (t.r_tarihi, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')

而且，我只报道了五天，因为你似乎对周末不感兴趣。这似乎有点奇怪，虽然你有一个日期跨度为9天，因为这将包括其中的一些天多次，你将无法知道哪个星期的特定时间是来自。你的r_num应该保持他们的秩序，所以你可能会看到，如果你有他们从两个星期的时间，虽然他们也将基于日期，你可能会看到不正确的顺序。想必这就是你想要的。

对于有关对列排序的后续查询，可以使用dense rank分配另一个伪列，并将其用于case而不是gun。

WITH got_r_num  AS
(
    SELECT  TO_CHAR (t.r_tarihi, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS gun
    ,       TO_CHAR (t.r_tarihi, 'DAY', 'NLS_DATE_LANGUAGE=TURKISH') AS d_name
    ,       DENSE_RANK () OVER ( ORDER BY TRUNC( t.r_tarihi) ) AS d_num
    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat
    ,       h.adi || ' ' || h.soyadi        AS hasta
    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')
                                 ORDER BY   t.baslama
                               )            AS r_num
    FROM             randevu_entegre_deneme  t
    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id
    WHERE   (r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')
                        AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9)
)
SELECT    min(case when mod(d_num, 7) = 1 then SAAT ||' '|| HASTA end) as day1
          , min(case when mod(d_num, 7) = 2 then SAAT ||' '|| HASTA end) as day2
          , min(case when mod(d_num, 7) = 3 then SAAT ||' '|| HASTA end) as day3
          , min(case when mod(d_num, 7) = 4 then SAAT ||' '|| HASTA end) as day4
          , min(case when mod(d_num, 7) = 5 then SAAT ||' '|| HASTA end) as day5
          , min(case when mod(d_num, 7) = 6 then SAAT ||' '|| HASTA end) as day6
          , min(case when mod(d_num, 7) = 0 then SAAT ||' '|| HASTA end) as day7
FROM      got_r_num
GROUP BY  r_num
ORDER BY  r_num;

DAY1                 DAY2                 DAY3                 DAY4                 DAY5                 DAY6                 DAY7
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   09:45 SEYMA DURLANIK 08:30 ZUHRE YEL
10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 13:00 TURKAN BICAK   08:48 AYSEL POLAT
13:30 ORHAN SAVAS    17:45 ESMA COMERT    14:00 FATMA ETA
14:00 FATMA ETA

我实际上已经不再使用gun了，但是我已经将它保留在其中，并添加了d_name作为全天名称，尽管目前这两个名称都没有被选中。这完全跳过了carsamba列，因为它没有数据，这与您的问题所显示的内容相匹配。如果你知道哪一列是哪一列，那也许就是你想要的。不幸的是，您无法动态分配列别名，因此您不知道day1实际上是cuma。如果您想要carsamba的空白，那么您可以从开始日期算出每个日期的偏移量：

    ,       TRUNC( t.r_tarihi) - TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 1 AS d_num

但是你仍然不知道第1列代表哪一天。你可以假装，但这要看你是怎么操作的。如果您从应用程序中提取数据，那么您也可以查询d_name并使用它。在SQL*Plus中，如果重复查询并使用替换变量(这可能会造成很大的开销)，或者部分解决方案只是将它们作为单独的select来选择，则可以这样做，这也不理想：

column r_num noprint
set head off
WITH got_r_num  AS
(
  ...
)
SELECT    0 as r_num
         , min(case when mod(d_num, 7) = 1 then d_name end) as day1
         ,  min(case when mod(d_num, 7) = 2 then d_name end) as day2
         ,  min(case when mod(d_num, 7) = 3 then d_name end) as day3
         ,  min(case when mod(d_num, 7) = 4 then d_name end) as day4
         ,  min(case when mod(d_num, 7) = 5 then d_name end) as day5
         ,  min(case when mod(d_num, 7) = 6 then d_name end) as day6
         ,  min(case when mod(d_num, 7) = 0 then d_name end) as day7
FROM      got_r_num
UNION ALL
SELECT    r_num
          , min(case when mod(d_num, 7) = 1 then SAAT ||' '|| HASTA end) as day1
          , min(case when mod(d_num, 7) = 2 then SAAT ||' '|| HASTA end) as day2
          , min(case when mod(d_num, 7) = 3 then SAAT ||' '|| HASTA end) as day3
          , min(case when mod(d_num, 7) = 4 then SAAT ||' '|| HASTA end) as day4
          , min(case when mod(d_num, 7) = 5 then SAAT ||' '|| HASTA end) as day5
          , min(case when mod(d_num, 7) = 6 then SAAT ||' '|| HASTA end) as day6
          , min(case when mod(d_num, 7) = 0 then SAAT ||' '|| HASTA end) as day7
FROM      got_r_num
GROUP BY  r_num
ORDER BY  r_num;

CUMA                 PAZARTESI            SALI                 PER¿EMBE
08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   09:45 SEYMA DURLANIK 08:30 ZUHRE YEL
10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 13:00 TURKAN BICAK   08:48 AYSEL POLAT
13:30 ORHAN SAVAS    17:45 ESMA COMERT    14:00 FATMA ETA
14:00 FATMA ETA

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/20905215

复制

相似问题

问Oracle枢轴查询获取ora-00933
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle枢轴查询获取ora-00933EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle枢轴查询获取ora-00933
EN