PostgreSQL：用for循环遍历表行，根据当前行检索列值 [英] PostgreSQL: Iterate through a tables rows with for loop, retrieve column value based on current row

问题描述

我有以下2个表

  CREATE TABLE salesperson_t（
 salespersonid numeric（4,0）NOT NULL ，
 salespersonname字符变化（25），
 salespersontelephone字符变化（50），
 salespersonfax字符变化（50），
 salesperson地址字符变化（30），
 salespersoncity字符变化（20），
 salespersonstate字符（2），
 salespersonzip字符变化（20），
 salesterritoryid数字（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，'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字符变化（50），
 total_sales_person整数，
 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）; 
  

我有以下伪代码：

<$ $ $
DECLARE
- currentRow [相关数据类型];
BEGIN
FOR COUNTER 1 ..（SELECT count（*）FROM territory2_t）LOOP - 共有13行

- **指定currentRow计数器**

RAISE NOTICE'Counter：％'，counter; - 调试目的

UPDATE terriory2_t
SET total_sales_person =（（SELECT count（*）
FROM salesperson_t
WHERE salesterritoryid = currentRow.territoryid）* 1） - * 1用于debuggin puporses
其中territoryid = currentRow.territoryid;

- **将currentRow增加1 **
END LOOP;
END; $




$ b

计算表（销售人员）有多少行具有territoryid currentRows - >'territory2.territoryid'，然后将该数量分配给currentRows-> territory2.total_sales_person。

解决方案

您不需要一个循环甚至是一个函数。

您可以在单个更新语句中完成您想要做的事情，因为每个地区的总计数可以通过一个汇总计算：

  SELECT salesterritoryid，count（*）as total_count 
 FROM salesperson_t 
 by salesterritoryid 
  

然后，可以将其用作更新地区表的来源：

  UPDATE territory2_t 
 SET total_sales_person = t.total_count 
 FROM（
 SELECT salesterritoryid，count（*）as total_count $ b $ FROM salesperson_t $ b $ group by salesterritoryid 
）t 
其中territoryid = t.salesterritoryid; 
  

---

另一种可能更容易理解但会对于较大的表是更慢的一个更新与一个相关的子查询

pre $ UPDATE territory2_t tg
SET total_sales_person = （从salesperson_t中选择count（*）
sp
其中sp.salesterritoryid = tg.territoryid）;

第一次和第二次更新之间略有不同：第二次更新将total_sales_person更新为 0 （零）对于没有销售人员的地区。第一个只会更新实际存在于销售员表中的地区的数量。

---

^{无关，但是：为一个标识符设置类型标识前缀或后缀通常是无用的，而且根本没有帮助。请参阅关于dba.stackexchange的相关讨论}

I have the following 2 tables

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);

I have the following pseudo code:

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; $$

It's purpose is count how many rows in the table (salesperson) have the 'territoryid' of the the currentRows->'territory2.territoryid', and then assign that quantity to currentRows->territory2.total_sales_person.

解决方案

You don't need a loop or even a function for this.

What you want to do can be done in a single update statement because the total count per territory can be calculated with a single aggregation:

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

This can then be used as the source to update the territory table:

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;

---

An alternative that might be easier to understand but will be slower for larger tables is an update with a co-related sub-query

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

There is a slight difference between the first and second update: the second one will update the total_sales_person to 0 (zero) for those territories where there is no salesperson at all. The first one will only update the count for territories that are actually present in the salesperson table.

---

^{Unrelated, but: having a "type identifying" prefix or suffix for an identifier is usually useless and doesn't really help at all. See a related discussion on dba.stackexchange}

这篇关于PostgreSQL：用for循环遍历表行，根据当前行检索列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！