SELECT DISTINCT值和INSERT INTO表

时间:2021-12-05 22:45:56

I want to take a column with values that repeat multiple times and get that value only once and store it for later use, but at the same time I would like to get another value in the same row as that distinct column.

我想要一个具有重复多次值的列,并且只获取该值一次并存储以供以后使用,但同时我希望在与该不同列相同的行中获得另一个值。

A      B       C
32263  123456  44
32263  123456  45
32263  123456  46
32264  246802  44
32263  246802  45
32264  246802  46
32265  369258  44
32265  369258  45
32265  369258  46

A, B, C represent three columns. Ignore C for now.

A,B,C代表三列。现在忽略C.

My question is: How can I get this information in this table and store it for I can use it later in the script?

我的问题是:如何在此表中获取此信息并将其存储,以便稍后在脚本中使用它?

Here is what I tried:

这是我尝试过的:

use databaseName

select distinct A from tableName
order by A

The result is:

结果是:

A
32263
32264
32265

I'm trying to get it to also give me B's value. (Note it does not matter at all which row I get since no matter what A I choose the value of B will be the same for given A.) We are ignoring C for now.

我试图让它也给我B的价值。 (注意,无论我得到哪一行都没关系,因为无论我选择什么,B的值对于给定的A都是相同的。)我们现在忽略了C.

The result should be:

结果应该是:

A      B
32263  123456
32264  246802
32265  369258

Now, once I get it like that I want to insert a row using the values I got from the query. This is where C comes in. I want to do something like this:

现在,一旦我得到它,我想使用从查询中获得的值插入一行。这就是C的用武之地。我想做这样的事情:

use databaseName

insert into tableName (A, B, C)
values (32263, 123456, 47)

Of course I don't want to put the values directly inside of there, instead have some type of loop that will cycle through each of the 3 distinct A values I found.

当然我不想将值直接放在那里,而是有一些类型的循环将循环通过我找到的3个不同的A值中的每一个。

In short, my table should go from:

简而言之,我的桌子应该来自:

A      B       C
32263  123456  44
32263  123456  45
32263  123456  46
32264  246802  44
32263  246802  45
32264  246802  46
32265  369258  44
32265  369258  45
32265  369258  46

To:

至:

A      B       C
32263  123456  44
32263  123456  45
32263  123456  46
32263  123456  47 -
32264  246802  44
32263  246802  45
32264  246802  46
32264  246802  47 -
32265  369258  44
32265  369258  45
32265  369258  46
32265  369258  47 -

I placed dashes next to the newly added rows to help you see the changes.

我在新添加的行旁边放置了破折号,以帮助您查看更改。

I figure I should perhaps do some type of loop that will cycle through all three distinct A values, but my problem is how to do that?

我想我应该做一些循环遍历所有三个不同的A值的循环,但我的问题是如何做到这一点?

Thanks for your time.

谢谢你的时间。

4 个解决方案

#1


10  

You can use INSERT INTO... SELECT statement on this,

你可以在这上面使用INSERT INTO ... SELECT语句,

INSERT INTO tableName (A, B, C)
SELECT A, B, MAX(C) + 1
FROM tableName
GROUP BY A, B

#2


0  

If I understand what you're trying to accomplish correctly, you should be able to do this:

如果我理解你想要正确完成的事情,你应该能够做到这一点:

INSERT INTO Table
SELECT A, B, MAX(C) + 1
FROM Table
GROUP A, B

This should insert a row for every distinct combination of A and B as well as increment C.

这应该为A和B的每个不同组合插入一行以及增加C.

#3


0  

This can be done using windowing functions.

这可以使用窗口函数来完成。

INSERT INTO TABLENAME (A, B, C)
SELECT A, B, C + 1
FROM(
select A, B, C, MAX(C) OVER(PARTITION BY A, B) AS MAXC
FROM TABLENAME 
) AS NEW_ROWS
WHERE C = MAXC

#4


0  

Try this:

尝试这个:

SELECT * INTO new_table FROM
   (SELECT DISTINICT * FROM table_name) x

#1


10  

You can use INSERT INTO... SELECT statement on this,

你可以在这上面使用INSERT INTO ... SELECT语句,

INSERT INTO tableName (A, B, C)
SELECT A, B, MAX(C) + 1
FROM tableName
GROUP BY A, B

#2


0  

If I understand what you're trying to accomplish correctly, you should be able to do this:

如果我理解你想要正确完成的事情,你应该能够做到这一点:

INSERT INTO Table
SELECT A, B, MAX(C) + 1
FROM Table
GROUP A, B

This should insert a row for every distinct combination of A and B as well as increment C.

这应该为A和B的每个不同组合插入一行以及增加C.

#3


0  

This can be done using windowing functions.

这可以使用窗口函数来完成。

INSERT INTO TABLENAME (A, B, C)
SELECT A, B, C + 1
FROM(
select A, B, C, MAX(C) OVER(PARTITION BY A, B) AS MAXC
FROM TABLENAME 
) AS NEW_ROWS
WHERE C = MAXC

#4


0  

Try this:

尝试这个:

SELECT * INTO new_table FROM
   (SELECT DISTINICT * FROM table_name) x