db2 在sql上创建多个列

rqdpfwrv 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(257)

写入的查询：

CREATE TABLE CEREAL_LIST_TAB
CEREAL_DESC VARCHAR(10)NOT NULL
INSERT INTO CEREAL_LIST_TAB

SELECT 
NVL(CASE WHEN INDICATOR >= '01' AND INDICATOR <= '05' THEN 'WHEAT' 
ELSE CASE WHEN INDICATOR >= '06' AND INDICATOR <= '10' THEN 'RICE' 
ELSE CASE WHEN INDICATOR >= '11' AND INDICATOR <= '15' THEN 'BARLEY' 
ELSE CASE WHEN INDICATOR >= '16' AND INDICATOR <= '20' THEN 'OATS'
ELSE CASE WHEN INDICATOR = '21' AND INDICATOR = '22' THEN 'OTHER' END END END END END,' ')

COUNT (CASE WHEN REVENUE <'1000000' THEN  KEY_FIELD END) as 'Less than $1M',
COUNT (CASE WHEN REVENUE >='1000000' AND REVENUE <='5000000'  THEN KEY_FIELD END) as     '$1M-$5M',
COUNT (CASE WHEN REVENUE >='5000001' AND REVENUE <='10000000'  THEN KEY_FIELD END) as '$5M-$10M',
COUNT (CASE WHEN REVENUE >='10000001' AND REVENUE <='25000000'  THEN KEY_FIELD END) as '$10M-$25M';

FROM TABLE_REVENUE
GROUP BY CEREAL_DESC
ORDER BY CEREAL_DESC;

预期结果：我需要计数的每一个谷物在每个收入桶中列出的查询，但它的抛出错误，请帮助让我知道我在哪里犯错误

db2

来源：https://stackoverflow.com/questions/70941548/create-multiple-columns-on-sql

1条答案

按热度按时间

hk8txs481#

这里有很多东西，

您不需要重复case表达式，只需一个case表达式就可以生成CEREAL_DESC，而无需NVL
相关名称必须在"之间编码，而不是'
不能在SELECT子句中创建CEREAL_DESCAND在GROUP BY子句中使用它，必须使用子查询或CTE
a BETWEEN b and c对我来说比a >= b and a <= c更可读
如果列是数值型的，则将其与数值进行比较，否则可能会触发不必要的转换，从而导致性能下降

试试这个

with
descs as (
SELECT 
  CASE
  WHEN INDICATOR between '01' and '05' THEN 'WHEAT' 
  WHEN INDICATOR between '06' and '10' THEN 'RICE' 
  WHEN INDICATOR between '11' and '15' THEN 'BARLEY' 
  WHEN INDICATOR between '16' and '20' THEN 'OATS' 
  WHEN INDICATOR between '21' and '22' THEN 'OTHER' 
  ELSE '' end as CEREAL_DESC,
  REVENUE
FROM TABLE_REVENUE
)
select
  CEREAL_DESC,
  sum(REVENUE < 1000000) as "Less than $1M",
  sum(REVENUE between 1000001 and 5000000) as "$1M-$5M",
  sum(REVENUE between 5000001 and 10000000) as "$5M-$10M",
  sum(REVENUE between 10000001 and 25000000) as "$10M-$25M"
 from descs
GROUP BY CEREAL_DESC
ORDER BY CEREAL_DESC;

赞(0）回复(0）举报 2022-11-07

我来回答

db2 在sql上创建多个列

1条答案

相关问题

热门标签

最新问答