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
- SQLFiddle Demo
- SQLFiddle演示
#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
- SQLFiddle Demo
- SQLFiddle演示
#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