规范化表上的数据

发布于 2024-08-18 08:07:30 字数 317 浏览 5 评论 0原文

我需要帮助来转换表上的数据，将一系列列减少为一个列。下面是一个示例：

Frequency_1 integer,
Frequency_2 integer,
Frequency_3 integer,
Frequency_4 integer,

这些列当前保存 1 或 0。只有一列保存 1。

新列应定义为

Frequency integer

此新列应保存 1 到 4 之间的值，具体取决于哪一旧列具有其值= 1.

你能建议一个 SQL 命令来完成这个任务吗？

原文

I need help to transform data on a table, reducing a series of columns to one single column. An example follows below:

Frequency_1 integer,
Frequency_2 integer,
Frequency_3 integer,
Frequency_4 integer,

These columns currently hold 1 or 0. Only one column will hold 1.

The new column should be defined as

Frequency integer

And this new column should hold a value between 1 and 4, depending on which of the old columns had its value = 1.

Could you suggest an SQL command to accomplish this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

叹沉浮 2024-08-25 08:07:30

如果你愿意的话，你可以想出更复杂的东西，但为什么不这样做呢？

SELECT Frequency_1 +
       (Frequency_2 * 2) +
       (Frequency_3 * 3) +
       (Frequency_4 * 4) AS Frequency

要实际进行更改，您可以先创建列，更新新列中的值，然后删除旧列。

You could come up with something more complicated if you want, but why not just do this?

SELECT Frequency_1 +
       (Frequency_2 * 2) +
       (Frequency_3 * 3) +
       (Frequency_4 * 4) AS Frequency

To actually make the change, you can create the column first, update the value in the new column, then delete the old columns.

回复收藏 0 原文

烟燃烟灭 2024-08-25 08:07:30

SELECT
    CASE WHEN Frequency_1 = 1 THEN 1 
         WHEN Frequency_2 = 1 THEN 2 
         WHEN Frequency_3 = 1 THEN 3 
         WHEN Frequency_4 = 1 THEN 4 
         ELSE 0 END AS Frequency
FROM TABLE

SELECT
    CASE WHEN Frequency_1 = 1 THEN 1 
         WHEN Frequency_2 = 1 THEN 2 
         WHEN Frequency_3 = 1 THEN 3 
         WHEN Frequency_4 = 1 THEN 4 
         ELSE 0 END AS Frequency
FROM TABLE

回复收藏 0 原文

薄荷港 2024-08-25 08:07:30

update table_name
  set frequency = 
    case when frequency_1 = 1 then 1 else
      case when frequency_2 = 1 then 2 else
        case when frequency_3 = 1 then 3 else
          case when frequency_4 = 1 then 4 
          end
        end
      end
    end

update table_name
  set frequency = 
    case when frequency_1 = 1 then 1 else
      case when frequency_2 = 1 then 2 else
        case when frequency_3 = 1 then 3 else
          case when frequency_4 = 1 then 4 
          end
        end
      end
    end

回复收藏 0 原文

说谎友 2024-08-25 08:07:30

像这样：

select Frequency =
  Frequency_1 * 1 +
  Frequency_2 * 2 +
  Frequency_3 * 3 +
  Frequency_4 * 4
from ATable

或这样：

select Frequency = case
  when Frequency_1 = 1 then 1
  when Frequency_2 = 1 then 2
  when Frequency_3 = 1 then 3
  when Frequency_4 = 1 then 4
  else 0
end
from ATable

Like this:

select Frequency =
  Frequency_1 * 1 +
  Frequency_2 * 2 +
  Frequency_3 * 3 +
  Frequency_4 * 4
from ATable

or this:

select Frequency = case
  when Frequency_1 = 1 then 1
  when Frequency_2 = 1 then 2
  when Frequency_3 = 1 then 3
  when Frequency_4 = 1 then 4
  else 0
end
from ATable

回复收藏 0 原文

~没有更多了~