时间戳

首页 > 解决方案 > 结合创建的表和 Listagg 查询

sql - 结合创建的表和 Listagg 查询

问题描述

目前我有两段代码，一段用于创建表格，另一段用于列出表格。代码看起来像这样：

Create table NewTable nologging as
select  distinct x as customerNumber,
y as items bought,
z as prices
from 
    customerNum,
    numItems,
    itemPrice
where  (z not in ('2.00','3.00','NA'))

然后是一个 Listagg

create table formattingSection nologging as
select newTable.customerNumber, listagg(newTable.bought,',') within group (order 
by bought) as boughtdesc
from newTable
group by customerNumber

是否可以将这两个部分合并为一个部分？这样我就没有2张桌子了吗？

标签： sqloraclelistagg

解决方案

像这样的东西，我想：

create table formattingSection nologging as
select 
  customerNumber, 
  listagg(bought, ',') within group (order by bought) as boughtdesc
from 
  -- instead of "newTable" itself, use SELECT statement from "CREATE TABLE newTable"
  (select distinct x as customerNumber,
                   y as items bought,
                   z as prices
   from 
     customerNum,
     numItems,
     itemPrice
   where (z not in ('2.00', '3.00', 'NA'))
     -- and ... --> you should join those tables, somehow. Otherwise, Cartesian product 
     -- will be created, and that's probably not what you want. 
  )
group by customerNumber;

除了我在代码中写的注释之外，“z”列似乎代表“价格”，并且您似乎将它存储到一个VARCHAR2列中。我建议你不要这样做；使用NUMBER数据类型。为什么？因为没有什么能阻止您将价格输入为“x.%i”或“2=&3”。如果因为“NA”而做出这样的决定，没问题 - 你只需将其留空，其值为 NULL。出于显示目的，您需要DECODE这样一个值：decode(z, null, 'NA', z).

推荐阅读