从 SQL 表插入和转换数据 [英] Inserting and transforming data from SQL table

问题描述

我有一个问题困扰了我几天.我有一张桌子:

I have a question which has been bugging me for a couple of days now. I have a table with:

• 日期
• 身份证
• 状态_ID
• 开始时间
• 结束时间
• Status_Time(seconds)(他们处于某种状态的时间，以秒为单位)

我想将此数据放在另一个表中，该表将 Status_ID 分组为列.这个表有这样的列:

I want to put this data in another table, that has the Status_ID grouped up as columns. This table has columns like this:

• 日期
• 身份证
• 午餐(以秒为单位)
• 中断(以秒为单位)
• 假期，(以秒为单位)等

因此，Status_ID 2 和 3 可能被归为假期、Status_ID 1 午餐等.

So, Status_ID 2 and 3 might be grouped under vacation, Status_ID 1 lunch, etc.

我想过在 while 循环中嵌套一个 Case，遍历每一行以插入到我的另一个表中.但是，我无法将这些数据从 Status_ID 中的行插入到它们现在分组的列中.

I have thought of doing a Case nested in a while loop, to go through every row to insert into my other table. However, I cannot wrap my head around inserting this data from Status_ID in rows, to columns that they are now grouped by.

推荐答案

不需要 WHILE 循环.

SELECT
    date,
    id,
    SUM(CASE WHEN status_id = 1 THEN status_time ELSE 0 END) AS lunch,
    SUM(CASE WHEN status_id = 2 THEN status_time ELSE 0 END) AS break,
    SUM(CASE WHEN status_id = 3 THEN status_time ELSE 0 END) AS vacation
FROM
    My_Table
GROUP BY
    date,
    id

此外，将 status_time 保留在表中是错误的(除非它是一个非持久的计算列).您将相同的数据有效地存储在数据库的两个位置，这最终会导致不一致.将这些数据推送到另一个表中也是如此，按状态类型划分时间.不要创建新表来保存数据，在需要时使用查询来获取数据.

Also, keeping the status_time in the table is a mistake (unless it's a non-persistent, calculated column). You are effectively storing the same data in two places in the database, which is going to end up resulting in inconsistencies. The same goes for pushing this data into another table with times broken out by status type. Don't create a new table to hold the data, use the query to get the data when you need it.

这篇关于从 SQL 表插入和转换数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！