代码之家 › 专栏 › 技术社区 › Boudhayan Dev

如何为以下结果创建SQL语句?

postgresql database sql mysql

Boudhayan Dev · 技术社区 · 6 年前

我有一张桌子,里面有下列内容-

我试图创建4列,如下所示-

风险
风险计算
收入
收入计数

下面的SQL查询为我提供了所需的4列,但它也生成空值。

select CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then BUCKET end as Revenue,
CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then CUSTOMER_COUNT end as Revenue_count,
 CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk' then BUCKET end as Risk,
  CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk' then CUSTOMER_COUNT end as Risk_count
FROM "TABLE_NAME"

结果-

如何删除空值并将结果放在一行中,因此理想情况下,输出应包含3行4列。

当做

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

你想要的 SUM() 或 MAX() :

select MAX(CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then BUCKET end) as Revenue,
       MAX(CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then CUSTOMER_COUNT end) as Revenue_count,
       MAX(CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk' then BUCKET end) as Risk,
       MAX(CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk') then CUSTOMER_COUNT end as Risk_count
FROM "TABLE_NAME"

然后可以使用 IN 或 LIKE :

select max(case when "BUCKET" in ('High Revenue', 'Low Revenue', 'Medium Revenue') then BUCKET end) as Revenue,
       max(case when "BUCKET" in ('High Revenue', 'Low Revenue', 'Medium Revenue') then CUSTOMER_COUNT end) as Revenue_count,
       max(case when "BUCKET" in ('High Risk', 'Low Risk', 'Medium Risk') then BUCKET end) as Risk,
       max(case when "BUCKET" in ('High Risk', 'Low Risk', 'Medium Risk') then CUSTOMER_COUNT end as Risk_count
FROM "TABLE_NAME";

我还建议您去掉标识符周围的双引号。只有在确实需要引用标识符时才引用标识符,然后选择不需要引用的标识符(例如您拥有的标识符)。

编辑:

我想你想要:

select max(case when bucket like '%Revenue' then BUCKET end) as Revenue,
       max(case when bucket like '%Revenue' then CUSTOMER_COUNT end) as Revenue_count,
       max(case when bucket like '%Risk' then BUCKET end) as Risk,
       max(case when bucket like '%Risk' then CUSTOMER_COUNT end) as Risk_count
FROM "TABLE_NAME"
GROUP BY LEFT(bucket, 3)  -- sufficient to distinct high/medium/low

Jim Castro 6 年前

您可以使用aggregation和max()来获得所需的结果,因为您不能对字母数字字段使用sum()-它将抛出一个错误。

select Revenue, Revenue_Count, Risk, Risk_Count from 
(
select 
     case 
          when bucket like 'High%' then 1
          when bucket like 'Low%' then 2
          else 3
     end, 
     max(case when bucket like '%Revenue%' then bucket else null end) as Revenue,
     max(case when bucket like '%Revenue%' then customer_count else null end) as Revenue_Count,
     max(case when bucket like '%Risk%' then bucket else null end) as Risk,
     max(case when bucket like '%Risk%' then customer_count else null end) as Risk_Count
 from public.table_name 
 group by 
    case 
       when bucket like 'High%' then 1
       when bucket like 'Low%' then 2
       else 3
    end
  order by 1    
) x;

forpas 6 年前

您可以使用UNION执行此操作:

select 
  'High Revenue' REVENUE,
  (select CUSTOMER_COUNT from TABLE_NAME where BUCKET = 'High Revenue') REVENUE_COUNT,
  'High Risk' RISK,
  (select CUSTOMER_COUNT from TABLE_NAME where BUCKET = 'High Risk') RISK_COUNT
union all
select 
  'Medium Revenue' REVENUE,
  (select CUSTOMER_COUNT from TABLE_NAME where BUCKET = 'Medium Revenue') REVENUE_COUNT,
  'Medium Risk' RISK,
  (select CUSTOMER_COUNT from TABLE_NAME where BUCKET = 'Medium Risk') RISK_COUNT
union all
select 
  'Low Revenue' REVENUE,
  (select CUSTOMER_COUNT from TABLE_NAME where BUCKET = 'Low Revenue') REVENUE_COUNT,
  'Low Risk' RISK,
  (select CUSTOMER_COUNT from TABLE_NAME where BUCKET = 'Low Risk') RISK_COUNT

查看MySql demo
看后记 demo