问oracle - case语句和group
EN

Stack Overflow用户

提问于 2016-04-18 15:27:56

回答 2查看 6.9K关注 0票数 2

嗨-我需要你的帮助，下面的查询。我正在尝试运行下面的查询以按组排序，但我得到了以下错误：

ORA-00904：“SALES_ID_CO”：无效标识符

你能帮个忙吗？非常感谢。

Select
count(1),
sales_date,
CASE 
WHEN sales_id like '1%'
THEN substr(sales_id,2,6)
WHEN sales_id  like '3%'
THEN substr(sales_id ,2,6)
WHEN sales_id  like '4%'
THEN substr(sales_id ,2,6)
WHEN sales_id  like '5%'
THEN substr(sales_id ,2,6)
WHEN sales_id  like '9%'
THEN substr(sales_id ,1,6)
WHEN sales_id  like '70%' OR sales_id  like '90%' OR sales_id  like '31%'
THEN substr(sales_id ,2,6)
END AS SALES_ID_CO,

case
WHEN sales_branch like '10%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branch like '200%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branch like '300%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
WHEN sales_branch like '456%' AND sales_code<> '678 '
THEN substr(sales_branch,2,6)
END AS SALES_BRANCH

from tbl_sales_cde

where sales_country IN (‘USA’,’ASIA’,’EU’) 
group by SALES_ID_CO, SALES_BRANCH,SALES_DATE

sql

oracle

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-04-18 15:42:12

Oracle不支持group by子句中的列别名。使用子查询：

SELECT SALES_ID_CO, SALES_BRANCH,SALES_DATE, COUNT(*)
FROM (Select sales_date,
             (CASE  WHEN sales_id like '1%'
                    THEN substr(sales_id,2,6)
                    WHEN sales_id  like '3%'
                    THEN substr(sales_id ,2,6)
                    WHEN sales_id  like '4%'
                    THEN substr(sales_id ,2,6)
                    WHEN sales_id  like '5%'
                    THEN substr(sales_id ,2,6)
                    WHEN sales_id  like '9%'
                    THEN substr(sales_id ,1,6)
                    WHEN sales_id  like '70%' OR sales_id  like '90%' OR sales_id  like '31%'
               END) AS SALES_ID_CO,
              (case WHEN sales_branchlike '10%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                    WHEN sales_branchlike '200%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                    WHEN sales_branchlike '300%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                    WHEN sales_branchlike '456%' AND sales_code<> '678 '
                    THEN substr(sales_branch,2,6)
                END) AS SALES_BRANCH
       from tbl_sales_cde
       where sales_country IN ('USA', 'ASIA', 'EU') 
      ) s
group by SALES_ID_CO, SALES_BRANCH,SALES_DATE;

注意：where子句中也有卷曲单引号，但这可能是复制问题。

票数 3

Stack Overflow用户

发布于 2016-04-18 15:41:02

SELECT SALES_ID_CO,
       SALES_BRANCH,
       SALES_DATE,
       count(1)
FROM   (
  SELECT sales_date,
         CASE
         WHEN sales_id  LIKE '70%'
         OR   sales_id  LIKE '90%'
         OR   sales_id  LIKE '31%'
         OR   SUBSTR( sales_id, 1, 1 ) IN ( '1', '3', '4', '5' )
           THEN substr(sales_id,2,6)
         WHEN sales_id  LIKE '9%'
           THEN substr(sales_id ,1,6)
         END AS SALES_ID_CO,
         CASE
         WHEN sales_code<> '678 '
         AND  (
              sales_branch LIKE '10%'
           OR sales_branch LIKE '200%'
           OR sales_branch LIKE '300%'
           OR sales_branch LIKE '456%'
         )
           THEN substr(sales_branch,2,6)
         END AS SALES_BRANCH
  from   tbl_sales_cde
  where  sales_country IN ('USA','ASIA','EU')
)
group by SALES_ID_CO, SALES_BRANCH,SALES_DATE