postgresql - PostgreSQL:使用for循环遍历表行，根据当前行检索列值

我有以下两张桌子

CREATE TABLE salesperson_t (
    salespersonid numeric(4,0) NOT NULL,
    salespersonname character varying(25),
    salespersontelephone character varying(50),
    salespersonfax character varying(50),
    salespersonaddress character varying(30),
    salespersoncity character varying(20),
    salespersonstate character(2),
    salespersonzip character varying(20),
    salesterritoryid numeric(4,0),
    CONSTRAINT salesperson_pk PRIMARY KEY (salespersonid)
);

INSERT INTO salesperson_t VALUES (1, 'Doug Henny', '8134445555', NULL, NULL, NULL, NULL, NULL, 2);
INSERT INTO salesperson_t VALUES (2, 'Robert Lewis', '8139264006', NULL, '124 Deerfield', 'Lutz', 'FL', '33549', 13);
INSERT INTO salesperson_t VALUES (3, 'William Strong', '3153821212', NULL, '787 Syracuse Lane', 'Syracuse', 'NY', '33240', 3);
INSERT INTO salesperson_t VALUES (4, 'Julie Dawson', '4355346677', NULL, NULL, NULL, NULL, NULL, 4);
INSERT INTO salesperson_t VALUES (5, 'Jacob Winslow', '2238973498', NULL, NULL, NULL, NULL, NULL, 5);
INSERT INTO salesperson_t VALUES (6, 'Pepe Lepue', NULL, NULL, NULL, 'Platsburg', 'NY', NULL, 13);
INSERT INTO salesperson_t VALUES (8, 'Fred Flinstone', NULL, NULL, '1 Rock Lane', 'Bedrock', 'Ca', '99999', 2);
INSERT INTO salesperson_t VALUES (9, 'Mary James', '3035555454', NULL, '9 Red Line', 'Denver', 'CO', '55555', 4);
INSERT INTO salesperson_t VALUES (10, 'Mary Smithson', '4075555555', NULL, '4585 Maple Dr', 'Orlando', 'FL', '32826', 15);

CREATE TABLE territory2_t (
    territoryid numeric(4,0),
    territoryname character varying(50),
    total_sales_person integer,
    CONSTRAINT territory2_t_pk PRIMARY KEY (territoryid)
);

INSERT INTO territory2_t VALUES (1, 'SouthEast', NULL);
INSERT INTO territory2_t VALUES (2, 'SouthWest', NULL);
INSERT INTO territory2_t VALUES (3, 'NorthEast', NULL);
INSERT INTO territory2_t VALUES (4, 'NorthWest', NULL);
INSERT INTO territory2_t VALUES (5, 'Central', NULL);
INSERT INTO territory2_t VALUES (6, 'Alaska', NULL);
INSERT INTO territory2_t VALUES (12, 'Hawaii', NULL);
INSERT INTO territory2_t VALUES (13, 'Colorado', NULL);
INSERT INTO territory2_t VALUES (15, 'Arizona', NULL);

我有以下伪代码：

DO $$
DECLARE
    -- currentRow [relevant datatype];
BEGIN
    FOR counter IN 1..(SELECT count(*)FROM territory2_t) LOOP -- There are 13 total rows

        -- **assign currentRow to counter**

        RAISE NOTICE 'Counter: %', counter; -- debugging purposes

        UPDATE terriory2_t
        SET total_sales_person = ((SELECT count(*)
                                    FROM salesperson_t
                                    WHERE salesterritoryid = currentRow.territoryid)*1) -- *1 is for debuggin puporses
        WHERE territoryid = currentRow.territoryid;

        -- **increase currentRow by 1**
    END LOOP;
END; $$

它的目的是计算表中有多少行（sales person）具有当前行的“territoryid”->“territory2.territoryid”，然后将该数量分配给当前行->territory2.total\u sales\u person。

最佳答案

你不需要一个循环，甚至不需要一个函数。
您要执行的操作可以在单个update语句中完成，因为每个区域的总计数可以通过单个聚合计算：

SELECT salesterritoryid, count(*) as total_count
FROM salesperson_t
group by salesterritoryid

然后可以将其用作更新territory表的源：

UPDATE territory2_t
   SET total_sales_person = t.total_count
FROM (
   SELECT salesterritoryid, count(*) as total_count
   FROM salesperson_t
   group by salesterritoryid
) t
WHERE territoryid = t.salesterritoryid;

另一种可能更容易理解，但对于较大的表来说会更慢的方法是使用一个相关联的子查询进行更新

UPDATE territory2_t tg
   SET total_sales_person = (select count(*)
                             from salesperson_t sp
                             where sp.salesterritoryid = tg.territoryid);

第一次和第二次更新之间略有不同：第二次更新将在根本没有销售人员的地区将销售人员总数更新为0（零）。第一个只更新salesperson表中实际存在的区域的计数。
不相关，但是：有一个“类型识别”前缀或后缀的标识符通常是无用的，并没有真正的帮助。见a related discussion on dba.stackexchange

关于postgresql - PostgreSQL:使用for循环遍历表行，根据当前行检索列值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40831736/