sql请求有问题

vngu2lb8 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(324)

我只想输出那些记录，对于相同的名称customer\u name，cust\u valid='i'和cust\u valid='a'
我试过这样做，但是客户的rezult只有记录'a'在这里输入代码

SELECT c.cust_first_name ||' '|| c.cust_last_name AS CUSTOMER_NAME, 
            to_number(SUBSTR(c.cust_income_level, INSTR(c.cust_income_level, '-')+2), '999999') as UPPER_INCOME_LEVEL, 
            sum(s.amount_sold) as TOTAL_AMOUNT, 
            (CASE  WHEN c.cust_credit_limit <= 1500 THEN 'Low_limit'
             ELSE 'High_limit'
             END) credit_limit_level,
             c.cust_valid
FROM SH.customers c  
JOIN sh.sales s on c.cust_id = s.cust_id
WHERE c.cust_valid = 'A' AND c.cust_income_level like '%-%' 
GROUP BY c.cust_first_name, c.cust_last_name, c.cust_income_level, c.cust_credit_limit, c.cust_valid 
HAVING SUM(s.amount_sold) > (c.cust_credit_limit * 50)
ORDER BY UPPER_INCOME_LEVEL DESC, CUSTOMER_NAME;

sql group-by select oracle having-clause

来源：https://stackoverflow.com/questions/62479438/trouble-with-sql-request

3条答案

按热度按时间

cedebl8k1#

你需要使用 EXISTS 中的子句 WHERE 条件如下：

and exists (select 1
              from sh.customers cin
             where cin.cust_id = c.cust_id
               and c.c.cust_valid = 'I'
           )

赞(0）回复(0）举报 2021-07-26

s5a0g9ez2#

如果我正确地跟踪了您，您可以通过修改 having 条款：

HAVING 
    SUM(s.amount_sold) > (c.cust_credit_limit * 50)
    AND MAX(CASE WHEN cust_valid= 'I' THEN 1 ELSE 0 END) = 1
    AND MAX(CASE WHEN cust_valid= 'A' THEN 1 ELSE 0 END) = 1

和cust\u valid='a'

赞(0）回复(0）举报 2021-07-26

pgky5nke3#

我试过了，但没有结果。我认为我的情况不正确。
我必须解释。。。修改查询以显示客户机的名称（customer\u name），其中有cust\u valid='a'的行和cust\u valid='i'的行作为单独的结果。

赞(0）回复(0）举报 2021-07-26