PostgreSQL更新等价于MySQL查询

I have a simple MySQL query that I want to convert to PostgreSQL. After 3 days I finally quit as I don't understand what wrong here:

我有一个简单的MySQL查询，我想把它转换成PostgreSQL。三天后，我终于辞职了，因为我不明白这里有什么问题:

UPDATE webUsers u, 
(SELECT IFNULL(count(s.id),0) AS id, p.associatedUserId FROM pool_worker p 
LEFT JOIN shares s ON p.username=s.username 
WHERE s.our_result='Y' GROUP BY p.associatedUserId) a
SET shares_this_round = a.id WHERE u.id = a.associatedUserId

I have tried to convert it but it says error on SET. Here is my query:

我试过转换它，但是它在SET上显示错误。

UPDATE webusers 
SET (shares_this_round) = (a.id)
FROM (SELECT coalesce(count(s.id),0) AS id, p.associatedUserId FROM pool_worker p 
LEFT JOIN shares s ON p.username=s.username WHERE s.our_result='Y' GROUP BY p.associatedUserId) a, webusers w WHERE u.id = a.associatedUserId

Can anyone please tell me what's wrong with it? I can't sleep just because of this.

有人能告诉我这有什么问题吗?因为这个我睡不着。

     ------------------------------EDIT-------------------------------------

shares table

股票表

CREATE TABLE shares (
id bigint NOT NULL,
rem_host character varying(255) NOT NULL,
username character varying(120) NOT NULL,
our_result character(255) NOT NULL,
upstream_result character(255),
reason character varying(50),
solution character varying(1000) NOT NULL,
"time" timestamp without time zone DEFAULT now() NOT NULL
);

webusers table

webuser表

CREATE TABLE webusers (
id integer NOT NULL,
admin integer NOT NULL,
username character varying(40) NOT NULL,
pass character varying(255) NOT NULL,
email character varying(255) NOT NULL,
"emailAuthPin" character varying(10) NOT NULL,
secret character varying(10) NOT NULL,
"loggedIp" character varying(255) NOT NULL,
"sessionTimeoutStamp" integer NOT NULL,
"accountLocked" integer NOT NULL,
"accountFailedAttempts" integer NOT NULL,
pin character varying(255) NOT NULL,
share_count integer DEFAULT 0 NOT NULL,
stale_share_count integer DEFAULT 0 NOT NULL,
shares_this_round integer DEFAULT 0 NOT NULL,
api_key character varying(255),
"activeEmail" integer,
donate_percent character varying(11) DEFAULT '1'::character varying,
btc_lock character(255) DEFAULT '0'::bpchar NOT NULL
);

pool_workes table

pool_workes表

CREATE TABLE pool_worker (
id integer NOT NULL,
"associatedUserId" integer NOT NULL,
username character(50),
password character(255),
allowed_hosts text
);

1 个解决方案

#1

First, I formatted to arrive at this less confusing but still incorrect query:

首先，我进行了格式化，以达到这个不那么令人困惑但仍然不正确的查询:

UPDATE webusers 
SET   (shares_this_round) = (a.id)
FROM  (
   SELECT coalesce(count(s.id),0) AS id, p.associatedUserId
   FROM   pool_worker p 
   LEFT   JOIN shares s ON p.username=s.username
   WHERE  s.our_result='Y'
   GROUP  BY p.associatedUserId) a
   , webusers w
WHERE u.id = a.associatedUserId

There are multiple distinct errors and more sub-optimal parts in this statement. Errors come first and with bold emphasis. The last few items are just recommendations.

该语句中有多个不同的错误和更多的次优部分。错误首先出现，重点突出。最后几项只是推荐。

Missing alias u for webuser. A trivial mistake.

丢失了webuser的别名u。一个微不足道的错误。
Missing join between w and a. Results in a cross join, which hardly makes any sense and is a very expensive mistake as far as performance is concerned. It is also completely uncalled for, you can drop the redundant second instance of webuser from the query.

w和a之间缺少连接，导致交叉连接，这几乎没有任何意义，就性能而言，这是一个非常昂贵的错误。它也完全不需要，您可以从查询中删除webuser的冗余第二个实例。
SET (shares_this_round) = (a.id) is a syntax error. You cannot wrap a column name in the SET clause in parenthesis. It would be pointless anyway, just like the parenthesis around a.id. The latter isn't a syntax error, though.

SET (shares_this_round) = (a.id)是一个语法错误。不能在括号中的SET子句中包装列名。无论如何，这都是毫无意义的，就像a.id的括号一样。但后者并不是语法错误。
As it turns out after comments and question update, you created the table with double-quoted "CamelCase" identifiers (which I advise not to use, ever, for exactly the kind of problems we just ran into). Read the chapter Identifiers and Key Words in the manual to understand what went wrong. In short: non-standard identifiers (with upper-case letters or reserved words, ..) have to be double-quoted at all times.
I amended the query below to fit the new information.

在评论和问题更新之后，您创建了带有双引号的“CamelCase”标识符的表(我建议您永远不要使用我们刚刚遇到的问题)。阅读手册中的章节标识符和关键字，以理解哪里出了问题。简而言之:非标准标识符(带有大写字母或保留词…)必须在任何时候都被双引号。我修改了下面的查询以适应新的信息。
The aggregate function count() never returns NULL by definition. COALESCE is pointless in this context. I quote the manual on aggregate functions:

聚合函数count()从不根据定义返回NULL。在这种情况下，合并是没有意义的。我引用集合函数手册:

It should be noted that except for count, these functions return a null value when no rows are selected.

应该注意，除了count之外，这些函数在没有选择行时返回空值。

Emphasis mine. The count itself works, because NULL values are not counted, so you actually get 0 where no s.id is found.

我特别强调。计数本身是有效的，因为空值不被计数，所以在没有s的情况下，你会得到0。id是发现。
I also use a different column alias (id_ct), because id for the count is just misleading.

我还使用了一个不同的列别名(id_ct)，因为计数的id具有误导性。
WHERE s.our_result = 'Y' ... if our_result is of type boolean, like it seems it should be, you can simplify to just WHERE s.our_result. I am guessing here, because you did not provide the necessary table definition.

年代的地方。our_result = ' Y '……如果our_result是布尔类型的，就像它看起来应该的那样，那么您可以将其简化为.our_result所在的位置。我在这里猜测，因为您没有提供必要的表定义。
It is almost always a good idea to avoid UPDATEs that do not actually change anything (rare exceptions apply). I added a second WHERE clause to eliminate those:

避免实际上不会改变任何东西的更新(很少有例外)几乎总是一个好主意。我添加了第二个WHERE子句来消除这些:
```
AND   w.shares_this_round IS DISTINCT FROM a.id
```
If shares_this_round is defined NOT NULL, you can use <> instead because id_ct cannot be NULL. (Again, missing info in question.)

如果shares_this_round定义为非NULL，那么可以使用<>，因为id_ct不能为NULL。(同样的，缺少相关信息。)
USING(username) is just a notational shortcut that can be used here.

使用(用户名)只是一个符号快捷方式，可以在这里使用。

Put everything together to arrive at this correct form:

把所有的东西放在一起，得出正确的形式:

UPDATE webusers w
SET    shares_this_round = a.id_ct
FROM  (
   SELECT p."associatedUserId", count(s.id) AS id_ct
   FROM   pool_worker p 
   LEFT   JOIN shares s USING (username)
   WHERE  s.our_result = 'Y'                        -- boolean?
   GROUP  BY p."associatedUserId"
   ) a
WHERE w.id = a."associatedUserId"
AND   w.shares_this_round IS DISTINCT FROM a.id_ct  -- avoid empty updates

#1