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

问题描述

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

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语句，

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

• SQLFiddle演示
• SQLFiddle Demo

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