英文:
sql combine records based on a column
问题
以下是翻译好的部分：
问题：
我得到了5条记录，但我希望它们合并成3条记录。
第1和第2条记录相同，因为它们具有相同的year、month和name；因此它们应合并为一条记录，并显示flag列值为yes的行。
第3条记录只有一条记录，所以只显示那个。
第4和第5条记录相同，因为它们具有相同的year、month和name；因此它们应合并为一条记录，并显示flag列值为yes的行。
我需要的：
| flag | year | month | name | description |
|---|---|---|---|---|
| Yes | 2017 | 2 | name1 | random description112 |
| No | 2018 | 7 | name4 | random description44 |
| Yes | 2022 | 4 | name8 | random description999 |
英文:
I have a table which holds 5 columns. I than wrote a basic sql statment which group all columns (to make sure there are no duplicates records).
select flag, year, month, name, max(description) description
from table1
group by flag, year, month, name
order by year
Sql output result:
| flag | year | month | name | description |
|---|---|---|---|---|
| No | 2017 | 2 | name1 | random description11 |
| Yes | 2017 | 2 | name1 | random description112 |
| No | 2018 | 7 | name4 | random description44 |
| Yes | 2022 | 4 | name8 | random description999 |
| No | 2022 | 4 | name8 | random description999 |
Issue:
I am getting 5 reocrds, however i want them to combine into 3 records.
1st & 2nd records are the same bc they have the same year, month, & name; therefor it should be combined into one records and show row with flag column value is yes.
3rd record is has only one record so just show that.
4th & 5th records are the same bc they have the same year, month, & name; therefor it should be combined into one records and show row with flag column value is yes.
What i need:
| flag | year | month | name | description |
|---|---|---|---|---|
| Yes | 2017 | 2 | name1 | random description112 |
| No | 2018 | 7 | name4 | random description44 |
| Yes | 2022 | 4 | name8 | random description999 |
答案1
得分: 1
你可以使用 MAX 结合 KEEP：
SELECT MAX(flag) AS flag,
year,
month,
name,
MAX(description) KEEP (DENSE_RANK LAST ORDER BY flag) AS description
FROM table1
GROUP BY year, month, name
ORDER BY year;
或者使用 ROW_NUMBER 分析函数：
SELECT flag,
year,
month,
name,
description
FROM (
SELECT flag,
year,
month,
name,
description,
ROW_NUMBER() OVER (
PARTITION BY year, month, name ORDER BY flag DESC, description DESC
) AS rn
FROM table1
)
WHERE rn = 1
ORDER BY year;
英文:
You can use MAX with KEEP:
SELECT MAX(flag) AS flag,
year,
month,
name,
MAX(description) KEEP (DENSE_RANK LAST ORDER BY flag) AS description
FROM table1
GROUP BY year, month, name
ORDER BY year;
or the ROW_NUMBER analytic function:
SELECT flag,
year,
month,
name,
description
FROM (
SELECT flag,
year,
month,
name,
description,
ROW_NUMBER() OVER (
PARTITION BY year, month, name ORDER BY flag DESC, description DESC
) AS rn
FROM table1
)
WHERE rn = 1
ORDER BY year;
答案2
得分: 0
你可以使用RWO_NUMBR来筛选所需的行
WITH CTE1 as (
select flag, year, month, name, max(description) description
from table1
group by flag, year, month, name
order by year),
CTE2 AS (SELECT "flag", "year", "month", "name", "description"
, ROW_NUMBER() OVER(PARTITION BY "year" ORDER BY "flag" DESC) rn
FROM CTE1)
SELECT "flag", "year", "month", "name", "description" FROM CTE2
WHERE rn = 1
英文:
you can use RWO_NUMBR to filter the wanted rows
WITH CTE1 as (
select flag, year, month, name, max(description) description
from table1
group by flag, year, month, name
order by year),
CTE2 AS (SELECT "flag", "year", "month", "name", "description"
, ROW_NUMBER() OVER(PARTITION BY "year" ORDER BY "flag" DESC) rn
FROM CTE1)
SELECT "flag", "year", "month", "name", "description" FROM CTE2
WHERE rn = 1
答案3
得分: 0
以下是您要翻译的内容：
WITH cte AS (
SELECT flag, year, month, name, MAX(description) AS description
FROM table1
GROUP BY flag, year, month, name
)
SELECT flag, year, month, name, description
FROM cte
ORDER BY year;
英文:
WITH cte AS (
SELECT flag, year, month, name, MAX(description) AS description
FROM table1
GROUP BY flag, year, month, name
)
SELECT flag, year, month, name, description
FROM cte
ORDER BY year;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论