MySQL用select输出两行_mySQL - 使用select返回多行來更新多個列

I have a table of postcodes and I want to update each postcode with its 3 nearest neighbours. Ie to fill in the blanks in this table:

我有一個郵政編碼表，我想用3個最近的鄰居更新每個郵政編碼。即填寫此表中的空白：

postcode nearestPostcode1 nearestPostcode2 nearestPostcode3

_______________________________________________________________

KY6 1DA - - -

KY6 1DG - - -

KY6 2DT - - -

KY6 1RG - - -

....

I've figured out a SELECT query to find the nearest postcodes and here is one clumsy way the first row could be updated:

我已經找到了一個SELECT查詢來查找最近的郵政編碼，這是第一行可以更新的一種笨拙的方式：

update table1 set

nearestPostcode1 = (select query for returning the first nearest postcode),

nearestPostcode2 = (select query for returning the second nearest postcode),

nearestPostcode3 = (select query for returning the third nearest postcode)

where postcode = 'KY6 1DA';

However this will result in 3 select queries being run for each row update. It would be more efficient if there was some way to do what is expressed by this pseudo code:

但是，這將導致為每個行更新運行3個選擇查詢。如果有一些方法來執行這個偽代碼所表達的內容，那將會更有效：

update table1 set

(nearestPostcode1, nearestPostcode2, nearestPostcode3) =

(select query to return the 3 nearest postcodes)

where postcode = 'KY6 1DA';

The 'select query' in the above looks like this:

上面的“選擇查詢”如下所示：

select postcode from postcodeTable

order by ASC

limit 3

Is there anyway for the rows returned from the select to be put into a form that they can be used to update multiple fields? Thanks.

無論如何，從select返回的行被放入一個可以用來更新多個字段的表單中嗎？謝謝。

4 个解决方案

#1

11

Update Table1

Cross Join (

Select Min( Case When Z1.Num = 1 Then Z1.postcode End ) As PostCode1

, Min( Case When Z1.Num = 2 Then Z1.postcode End ) As PostCode2

, Min( Case When Z1.Num = 3 Then Z1.postcode End ) As PostCode3

From (

Select postcode

, @num := @num + 1 As Num

From postcodeTable

Where postcode = 'KY6 IDA'

Order By ASC

Limit 3

) As Z1

) As Z

Set nearestPostCode1 = Z.PostCode1

, nearestPostCode2 = Z.PostCode2

, nearestPostCode3 = Z.PostCode3

Where Table1.postcode = 'KY6 IDA'

#2

1

You can do something similar to this:

你可以做類似的事情：

UPDATE table1

SET

nearestPostcode1 = pc1,

nearestPostcode2 = pc2,

nearestPostcode3 = pc3

FROM

(SELECT pc1, pc2, pc3 FROM ....) t

WHERE

postcode = 'KY6 1DA';

I found this related question on Stackoverflow on how to transform columns to rows:

我在Stackoverflow上找到了關於如何將列轉換為行的相關問題：

In your case, you can do something like

在你的情況下，你可以做類似的事情

SELECT

IF(@rownum=1,postcode,'') ) AS pc1,

IF(@rownum=2,postcode,'') ) AS pc2,

IF(@rownum=3,postcode,'') ) AS pc2,

FROM

(SELECT postcode

FROM postcodeTable

ORDER BY ASC

LIMIT 3)

Here is a hack to simulate the ROW_NUMBER() functionality in MySQL [1]:

這是一個模擬MySQL [1]中的ROW_NUMBER()功能的hack：

SELECT @rownum:=@rownum+1 rownum, t.*

FROM (SELECT @rownum:=0) r, mytable t;

#3

0

I think you could do this with the pseudo-code:

我想你可以用偽代碼做到這一點：

REPLACE INTO table1 (postcode, nearestPostcode1, nearestPostcode2, nearestPostcode3)

SELECT "KY6 1DA", col1, col2, col3 FROM myTable WHERE ...;

it'd be easier to specify it seeing the real SQL.

看到它真正的SQL就更容易了。

Note the first column is specified as a constant in quotes. For this to work postcode must be a UNIQUE or PRIMARY index.

請注意，第一列被指定為引號中的常量。為此，郵政編碼必須是UNIQUE或PRIMARY索引。

#4

-1

Anytime I see a table with columns that have 1-up counters after their names, I get concerned.

任何時候我看到一個表格，其列名在他們的名字后面有一個計數器，我很擔心。

In general, it is a Bad Idea (TM) to store data that can be calculated from data that is already stored. What happens if your application all of a sudden needs the 4 closest postal codes? What if the postal code boundaries change?

通常，存儲可以從已經存儲的數據計算的數據是壞想法(TM)。如果您的應用程序突然需要4個最接近的郵政編碼，會發生什么？如果郵政編碼邊界發生變化怎么辦？

Assuming the distance calculation isn't very complex, you'll be better off in the long run not explicitly storing this data.

假設距離計算不是很復雜，從長遠來看，你最好不要明確地存儲這些數據。