SQL – 基于参数的聚合

huangapple go评论77阅读模式
英文:

SQL - Aggregation based on a parameter

问题

UPDATE your_table
SET Q_Flag = 1
WHERE Q_Flag = 0
AND EXISTS (
    SELECT 1
    FROM your_table AS t2
    WHERE t2.County_and_city = your_table.County_and_city
    AND t2.Q_Flag = 1
);
英文:

I have a following table -

Play_Code    County_and_city  Q_Flag  Country
A1           County1_Denver    0       USA 
A2           County2_Denver    1       USA
A3           County3_Denver    0       USA
B1           County1_Dallas    0       USA
B2           County2_Dallas    0       USA

I want Q_Flag of all records of same city to be 1 if even one record is having Q_Flag as 1 for the same city. If none of the record for a city is having 1 then they all should remain as 0 only
So the result of above table should be like below -

Play_Code    County_and_city  Q_Flag  Country
A1           County1_Denver    1       USA 
A2           County2_Denver    1       USA
A3           County3_Denver    1       USA
B1           County1_Dallas    0       USA
B2           County2_Dallas    0       USA

One way I think is to create a separate table by doing a max condition on Q_Flag and then join it with original table. But is there way to do it within the same table using SQL?

答案1

得分: 1

假设您使用的是mysql,我们需要按城市分组我们的数据,然后使用SUBSTRING_INDEX从列County_and_city中提取城市,然后检查是否至少有一个Q_flag = 1。

select t.Play_Code, t.County_and_city, s.Q_Flag, t.Country
from mytable t
inner join (
  select SUBSTRING_INDEX(County_and_city, '_', -1) as city,
  case when sum(Q_Flag) >= 1 then 1 else 0 end as Q_flag
  from mytable
  group by SUBSTRING_INDEX(County_and_city, '_', -1)
) as s on t.County_and_city like concat('%', s.city)

假设County_and_city只包含城市:

select t.Play_Code, t.County_and_city, s.Q_Flag, t.Country
from mytable t
inner join (
  select County_and_city as city, case when sum(Q_Flag) >= 1 then 1 else 0 end as Q_flag
  from mytable
  group by County_and_city
) as s on t.County_and_city = s.city

演示在这里

英文:

Assuming you are using mysql, we need to group our data by city then using SUBSTRING_INDEX to extract city from column County_and_city, then we check if at least one Q_flag = 1.

    select t.Play_Code, t.County_and_city, s.Q_Flag, t.Country
    from mytable t
    inner join (
      select SUBSTRING_INDEX(County_and_city, '_', -1) as city,
case when sum(Q_Flag) >=1 then 1 else 0 end as Q_flag
      from mytable
      group by SUBSTRING_INDEX(County_and_city, '_', -1)
    ) as s on t.County_and_city like concat('%', s.city)

Assuming County_and_city contains city only then :

select t.Play_Code, t.County_and_city, s.Q_Flag, t.Country
from mytable t
inner join (
  select County_and_city as city, case when sum(Q_Flag) >=1 then 1 else 0 end as Q_flag
  from mytable
  group by County_and_city
) as s on t.County_and_city = s.city

Demo here

huangapple
  • 本文由 发表于 2023年4月4日 07:15:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75924370.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定