SELECT DISTINCT 值和 INSERT INTO 表 [英] SELECT DISTINCT values and INSERT INTO table

问题描述

我想取一个值重复多次的列，只获取一次该值并将其存储以备后用，但同时我想在与该不同列相同的行中获取另一个值.

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 代表三列.暂时忽略 C.

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

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

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

这是我尝试过的:

use databaseName

select distinct A from tableName
order by A

结果是:

A
32263
32264
32265

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

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.

结果应该是:

A      B
32263  123456
32264  246802
32265  369258

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

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:

use databaseName

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

当然，我不想将值直接放在那里，而是使用某种类型的循环来循环遍历我找到的 3 个不同的 A 值.

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.

简而言之，我的表应该来自:

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

致:

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.

我想我也许应该做某种类型的循环，循环遍历所有三个不同的 A 值，但我的问题是如何做到这一点?

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?

感谢您的时间.

推荐答案

您可以使用 INSERT INTO... SELECT 语句对此，

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

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

• SQLFiddle 演示

这篇关于SELECT DISTINCT 值和 INSERT INTO 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！