英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论