问题更新查询 – 我没有得到我想要的结果。

huangapple
go评论102阅读模式
英文:

Problem with update query - I don't get the results I want

问题

I understand your request. Here's the translated code:

  1. 我负责一个大学的数据库，我需要更新一个名为register的表的几个列，这些列的名称分别是exam_grade、lab_grade和final_grade。final_grade是基于exam_grade和lab_grade的结果计算的，但是当lab_grade的结果为NULL时，final_grade的结果不应该直接等于exam_grade，而应该保持为NULL。以下是该函数的代码：
  3. ```sql
  4. DROP FUNCTION public.fn_question_2_2(integer);
  6. CREATE OR REPLACE FUNCTION public.fn_question_2_2(num integer)
  7. RETURNS void
  8. AS
  9. $$
  10. DECLARE
  11.        pointer record;
  12.        percentage numeric;
  13.        exam_i numeric;
  14.        lab_i numeric;
  15. BEGIN
  16.      FOR pointer IN
  17.                    (SELECT rg.amka, rg.lab_grade, rg.exam_grade,
  18.                            rg.serial_number, rg.register_status, 
  19.                            cr.course_code, cr.lab_hours
  20.                     FROM "Register" rg
  21.                     JOIN (SELECT course_code, lab_hours FROM "Course") cr USING (course_code)
  22.                     WHERE rg.register_status = 'approved' AND rg.serial_number = num)
  23.                     LOOP
  24.                         IF (pointer.exam_grade IS NULL) THEN
  25.                             exam_i = floor((random()*(10-1)+1));
  26.                         ELSE
  27.                             exam_i = pointer.exam_grade;
  28.                         END IF;
  29.                         IF (pointer.lab_grade IS NULL AND  pointer.lab_hours > 0) THEN
  30.                             lab_i = floor((random()*(10-1)+1));
  31.                         ELSE
  32.                             lab_i = pointer.lab_grade;
  33.                         END IF;
  34.                         percentage = (SELECT exam_percentage FROM "CourseRun" 
  35.                                       WHERE course_code = pointer.course_code AND serial_number = pointer.serial_number);
  36.                         UPDATE "Register" r
  37.                         SET lab_grade = lab_i ,exam_grade = exam_i,
  38.                             final_grade = (SELECT
  39.                                            CASE WHEN pointer.lab_hours IS NOT NULL 
  40.                                            THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
  41.                                            ELSE (exam_i)
  42.                                            END)
  43.                         WHERE (final_grade IS NULL)
  44.                         AND r.amka = pointer.amka  
  45.                         AND r.course_code = pointer.course_code
  46.                         AND r.register_status = 'approved';
  47.                         
  48.                     END LOOP;
  49. END;
  50. $$
  52. LANGUAGE 'plpgsql';
  1. -- Table: public.CourseRun
  3. -- DROP TABLE IF EXISTS public."CourseRun";
  5. CREATE TABLE IF NOT EXISTS public."CourseRun"
  6. (
  7.     course_code character(7) COLLATE pg_catalog."default" NOT NULL,
  8.     serial_number integer NOT NULL,
  9.     exam_min numeric,
  10.     lab_min numeric,
  11.     exam_percentage numeric,
  12.     labuses integer,
  13.     semesterrunsin integer NOT NULL,
  14.     CONSTRAINT "CourseRun_pkey" PRIMARY KEY (course_code, serial_number),
  15.     CONSTRAINT "CourseRun_course_code_fkey" FOREIGN KEY (course_code)
  16.         REFERENCES public."Course" (course_code) MATCH SIMPLE
  17.         ON UPDATE NO ACTION
  18.         ON DELETE NO ACTION,
  19.     CONSTRAINT "CourseRun_labuses_fkey" FOREIGN KEY (labuses)
  20.         REFERENCES public."Lab" (lab_code) MATCH SIMPLE
  21.         ON UPDATE NO ACTION
  22.         ON DELETE NO ACTION,
  23.     CONSTRAINT "CourseRun_semesterrunsin_fkey" FOREIGN KEY (semesterrunsin)
  24.         REFERENCES public."Semester" (semester_id) MATCH SIMPLE
  25.         ON UPDATE NO ACTION
  26.         ON DELETE NO ACTION
  27. )
  29. TABLESPACE pg_default;
  31. ALTER TABLE IF EXISTS public."CourseRun"
  32.     OWNER to postgres;
  1. -- Table: public.Course
  3. -- DROP TABLE IF EXISTS public."Course";
  5. CREATE TABLE IF NOT EXISTS public."Course"
  6. (
  7.     course_code character(7) COLLATE pg_catalog."default" NOT NULL,
  8.     course_title character(100) COLLATE pg_catalog."default" NOT NULL,
  9.     units smallint NOT NULL,
  10.     lecture_hours smallint NOT NULL,
  11.     tutorial_hours smallint NOT NULL,
  12.     lab_hours smallint NOT NULL,
  13.     typical_year smallint NOT NULL,
  14.     typical_season semester_season_type NOT NULL,
  15.     obligatory boolean NOT NULL,
  16.     course_description character varying COLLATE pg_catalog."default",
  17.     CONSTRAINT "Course_pkey" PRIMARY KEY (course_code)
  18. )
  20. TABLESPACE pg_default;
  22. ALTER TABLE IF EXISTS public."Course"
  23.     OWNER to postgres;
  1. -- Table: public.Register
  3. -- DROP TABLE IF EXISTS public."Register";
  5. CREATE TABLE IF NOT EXISTS public."Register"
  6. (
  7.     amka character varying COLLATE pg_catalog."default" NOT NULL,
  8.     serial_number integer NOT NULL,
  9.     course_code character(7) COLLATE pg_catalog."default" NOT NULL,
  10.     exam_grade numeric,
  11.     final_grade numeric,
  12.     lab_grade numeric,
  13.     register_status register_status_type,
  14.     CONSTRAINT "Register_pkey" PRIMARY KEY (course_code, serial_number, amka),
  15.     CONSTRAINT "Register_course_run_fkey" FOREIGN KEY (serial_number, course_code)
  16.         REFERENCES public."CourseRun" (serial_number, course_code) MATCH SIMPLE
  17.         ON UPDATE CASCADE
  18.         ON DELETE CASCADE,
  19.     CONSTRAINT "Register_student_fkey" FOREIGN KEY (amka)
  20.         REFERENCES public."Student" (amka) MATCH SIMPLE
  21.         ON UPDATE CASCADE
  22.         ON DELETE CASCADE
  23.         NOT VALID
  24. )
  26. TABLESPACE pg_default;
  28. ALTER TABLE IF EXISTS public."Register"
  29.     OWNER to postgres;

希望对你有所帮助。

英文:

So I database for a university and I need to update several columns of a table named register going with the name of exam_grade, lab_grade, and final grade. The final grade is calculated based on the results of exam_grade and lab_grade, but whenever the result of lab_grade is NULL then the result of final_grade is not becoming directly the exam_grade, but also stays NULL. Here is the code for the function:

  2. CREATE OR REPLACE FUNCTION public.fn_question_2_2(num integer)
  3. RETURNS void
  4. AS
  5. $$
  6. DECLARE
  7. pointer record;
  8. percentage numeric;
  9. exam_i numeric;
  10. lab_i numeric;
  11. BEGIN
  12. FOR pointer IN
  13. (SELECT rg.amka, rg.lab_grade, rg.exam_grade,
  14. rg.serial_number, rg.register_status, 
  15. cr.course_code, cr.lab_hours
  16. FROM "Register" rg
  17. JOIN (SELECT course_code, lab_hours FROM "Course") cr USING (course_code)
  18. WHERE rg.register_status = 'approved' AND rg.serial_number = num)
  19. LOOP
  20. IF (pointer.exam_grade IS NULL) THEN
  21. exam_i = floor((random()*(10-1)+1));
  22. ELSE
  23. exam_i = pointer.exam_grade;
  24. END IF;
  25. IF (pointer.lab_grade IS NULL AND  pointer.lab_hours > 0) THEN
  26. lab_i = floor((random()*(10-1)+1));
  27. ELSE
  28. lab_i = pointer.lab_grade;
  29. END IF;
  30. percentage = (SELECT exam_percentage FROM "CourseRun" 
  31. WHERE course_code = pointer.course_code AND serial_number = pointer.serial_number);
  32. UPDATE "Register" r
  33. SET lab_grade = lab_i ,exam_grade = exam_i,
  34. final_grade = (SELECT
  35. CASE WHEN pointer.lab_hours IS NOT NULL 
  36. THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
  37. ELSE (exam_i)
  38. END)
  39. WHERE (final_grade IS NULL)
  40. AND r.amka = pointer.amka  
  41. AND r.course_code = pointer.course_code
  42. AND r.register_status = 'approved';
  43. END LOOP;
  44. END;
  45. $$
  46. LANGUAGE 'plpgsql';
  2. -- DROP TABLE IF EXISTS public."CourseRun";
  3. CREATE TABLE IF NOT EXISTS public."CourseRun"
  4. (
  5. course_code character(7) COLLATE pg_catalog."default" NOT NULL,
  6. serial_number integer NOT NULL,
  7. exam_min numeric,
  8. lab_min numeric,
  9. exam_percentage numeric,
  10. labuses integer,
  11. semesterrunsin integer NOT NULL,
  12. CONSTRAINT "CourseRun_pkey" PRIMARY KEY (course_code, serial_number),
  13. CONSTRAINT "CourseRun_course_code_fkey" FOREIGN KEY (course_code)
  14. REFERENCES public."Course" (course_code) MATCH SIMPLE
  15. ON UPDATE NO ACTION
  16. ON DELETE NO ACTION,
  17. CONSTRAINT "CourseRun_labuses_fkey" FOREIGN KEY (labuses)
  18. REFERENCES public."Lab" (lab_code) MATCH SIMPLE
  19. ON UPDATE NO ACTION
  20. ON DELETE NO ACTION,
  21. CONSTRAINT "CourseRun_semesterrunsin_fkey" FOREIGN KEY (semesterrunsin)
  22. REFERENCES public."Semester" (semester_id) MATCH SIMPLE
  23. ON UPDATE NO ACTION
  24. ON DELETE NO ACTION
  25. )
  26. TABLESPACE pg_default;
  27. ALTER TABLE IF EXISTS public."CourseRun"
  28. OWNER to postgres;```
  29. ```-- Table: public.Course
  30. -- DROP TABLE IF EXISTS public."Course";
  31. CREATE TABLE IF NOT EXISTS public."Course"
  32. (
  33. course_code character(7) COLLATE pg_catalog."default" NOT NULL,
  34. course_title character(100) COLLATE pg_catalog."default" NOT NULL,
  35. units smallint NOT NULL,
  36. lecture_hours smallint NOT NULL,
  37. tutorial_hours smallint NOT NULL,
  38. lab_hours smallint NOT NULL,
  39. typical_year smallint NOT NULL,
  40. typical_season semester_season_type NOT NULL,
  41. obligatory boolean NOT NULL,
  42. course_description character varying COLLATE pg_catalog."default",
  43. CONSTRAINT "Course_pkey" PRIMARY KEY (course_code)
  44. )
  45. TABLESPACE pg_default;
  46. ALTER TABLE IF EXISTS public."Course"
  47. OWNER to postgres;```
  48. ```-- Table: public.Register
  49. -- DROP TABLE IF EXISTS public."Register";
  50. CREATE TABLE IF NOT EXISTS public."Register"
  51. (
  52. amka character varying COLLATE pg_catalog."default" NOT NULL,
  53. serial_number integer NOT NULL,
  54. course_code character(7) COLLATE pg_catalog."default" NOT NULL,
  55. exam_grade numeric,
  56. final_grade numeric,
  57. lab_grade numeric,
  58. register_status register_status_type,
  59. CONSTRAINT "Register_pkey" PRIMARY KEY (course_code, serial_number, amka),
  60. CONSTRAINT "Register_course_run_fkey" FOREIGN KEY (serial_number, course_code)
  61. REFERENCES public."CourseRun" (serial_number, course_code) MATCH SIMPLE
  62. ON UPDATE CASCADE
  63. ON DELETE CASCADE,
  64. CONSTRAINT "Register_student_fkey" FOREIGN KEY (amka)
  65. REFERENCES public."Student" (amka) MATCH SIMPLE
  66. ON UPDATE CASCADE
  67. ON DELETE CASCADE
  68. NOT VALID
  69. )
  70. TABLESPACE pg_default;
  71. ALTER TABLE IF EXISTS public."Register"
  72. OWNER to postgres;```
  1. amka|serial_number|course_code|exam_grade|final_grade|lab_grade|semes_status
  2. "01010104188"	12	"ΑΓΓ 201"	6	6	10	"pass"
  3. "01010104188"	12	"ΑΓΓ 202"	2			"approved"
  4. "01010104188"	12	"ΗΡΥ 201"	8	9	10	"pass"
  5. "01010104188"	12	"ΗΡΥ 202"	9	8	7	"pass"
  6. "01010104188"	12	"ΗΡΥ 203"	7	8.40	9	"approved"
  7. "01010104188"	12	"ΗΡΥ 204"	7	5.50	4	"approved"
  8. "01010104188"	12	"ΗΡΥ 211"	9			"approved"
  9. "01010104188"	12	"ΜΑΘ 107"	2	2	6	"fail"
  10. "01010104188"	12	"ΠΛΗ 201"	7	0	2	"fail"
  11. "01010104188"	12	"ΠΛΗ 202"	2	2.70	3	"approved"
  12. "01010104188"	12	"ΠΛΗ 211"	8	7	5	"pass"
  13. "01010104188"	12	"ΤΗΛ 201"	7	7	7	"pass"
  14. "01010104188"	12	"ΤΗΛ 202"	4	3.20	2	"approved"
  15. "01010104188"	12	"ΤΗΛ 211"	5	7.00	9	"approved"

Any help will be appreciated, and if anything needs to be added so that you get a better perspective please say so.

I tried many different ways of writing the query and the results are every time the same. I cannot get the final_grade = exam_ grade when the lab_grade is NULL. I don't get any error message so there is a problem with the logic.

答案1

得分: 1

你可以使用 COALESCE 或在你的 CASE 语句中进行检查：

英文:

You can either use COALESCE or check in your CASE:

  2. CREATE OR REPLACE FUNCTION public.fn_question_2_2(num integer)
  3. RETURNS void
  4. AS
  5. $$
  6. DECLARE
  7.        pointer record;
  8.        percentage numeric;
  9.        exam_i numeric;
  10.        lab_i numeric;
  11. BEGIN
  12.      FOR pointer IN
  13.                    (SELECT rg.amka, rg.lab_grade, rg.exam_grade,
  14.                            rg.serial_number, rg.register_status, 
  15.                            cr.course_code, cr.lab_hours
  16.                     FROM "Register" rg
  17.                     JOIN (SELECT course_code, lab_hours FROM "Course") cr USING (course_code)
  18.                     WHERE rg.register_status = 'approved' AND rg.serial_number = num)
  19.                     LOOP
  20.                         IF (pointer.exam_grade IS NULL) THEN
  21.                             exam_i = floor((random()*(10-1)+1));
  22.                         ELSE
  23.                             exam_i = pointer.exam_grade;
  24.                         END IF;
  25.                         IF (pointer.lab_grade IS NULL AND  pointer.lab_hours > 0) THEN
  26.                             lab_i = floor((random()*(10-1)+1));
  27.                         ELSE
  28.                             lab_i = pointer.lab_grade;
  29.                         END IF;
  30.                         percentage = (SELECT exam_percentage FROM "CourseRun" 
  31.                                       WHERE course_code = pointer.course_code AND serial_number = pointer.serial_number);
  32.                         UPDATE "Register" r
  33.                         SET lab_grade = lab_i ,exam_grade = exam_i,
  34.                             final_grade = (SELECT
  35.                                                CASE WHEN pointer.lab_hours IS NOT NULL 
  36.                                                THEN (floor(((COALESCE(lab_i, 0)*(100-percentage)) + exam_i * percentage)/100))
  37.                                                ELSE (exam_i)
  38.                                                END)
  39.                         WHERE (final_grade IS NULL)
  40.                         AND r.amka = pointer.amka  
  41.                         AND r.course_code = pointer.course_code
  42.                         AND r.register_status = 'approved';
  43.                         
  44.                     END LOOP;
  45. END;
  46. $$
  48. LANGUAGE 'plpgsql';

or

  2. CREATE OR REPLACE FUNCTION public.fn_question_2_2(num integer)
  3. RETURNS void
  4. AS
  5. $$
  6. DECLARE
  7.        pointer record;
  8.        percentage numeric;
  9.        exam_i numeric;
  10.        lab_i numeric;
  11. BEGIN
  12.      FOR pointer IN
  13.                    (SELECT rg.amka, rg.lab_grade, rg.exam_grade,
  14.                            rg.serial_number, rg.register_status, 
  15.                            cr.course_code, cr.lab_hours
  16.                     FROM "Register" rg
  17.                     JOIN (SELECT course_code, lab_hours FROM "Course") cr USING (course_code)
  18.                     WHERE rg.register_status = 'approved' AND rg.serial_number = num)
  19.                     LOOP
  20.                         IF (pointer.exam_grade IS NULL) THEN
  21.                             exam_i = floor((random()*(10-1)+1));
  22.                         ELSE
  23.                             exam_i = pointer.exam_grade;
  24.                         END IF;
  25.                         IF (pointer.lab_grade IS NULL AND  pointer.lab_hours > 0) THEN
  26.                             lab_i = floor((random()*(10-1)+1));
  27.                         ELSE
  28.                             lab_i = pointer.lab_grade;
  29.                         END IF;
  30.                         percentage = (SELECT exam_percentage FROM "CourseRun" 
  31.                                       WHERE course_code = pointer.course_code AND serial_number = pointer.serial_number);
  32.                         UPDATE "Register" r
  33.                         SET lab_grade = lab_i ,exam_grade = exam_i,
  34.                             final_grade = (SELECT
  35.                                                CASE WHEN pointer.lab_hours IS NOT NULL AND lab_i IS NOT NULL
  36.                                                THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
  37.                                                ELSE (exam_i)
  38.                                                END)
  39.                         WHERE (final_grade IS NULL)
  40.                         AND r.amka = pointer.amka  
  41.                         AND r.course_code = pointer.course_code
  42.                         AND r.register_status = 'approved';
  43.                         
  44.                     END LOOP;
  45. END;
  46. $$
  48. LANGUAGE 'plpgsql';

答案2

得分: 0

你的子查询是我们需要检查问题的地方，因为你抱怨的是错误的数值导致了更新，而不是数值变化的缺失。因此，让我们看看这个子查询：

  1. SELECT
  2. CASE WHEN pointer.lab_hours IS NOT NULL 
  3.      THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
  4.      ELSE (exam_i)
  5. END

NULL 是一个吸收元素，所以，无论你对它执行什么操作，结果都将是 NULL。原因很简单：NULL 是缺少值，或者换句话说，它是未知的。那么，例如

未知 + 2

的结果是什么呢？自然是未知。

因此，如果不太了解你正在测试的数据，以下是可能具有 NULL 值的字段：

  • lab_i
  • percentage
  • exam_i

因此，你可能需要弄清楚默认值应该是什么，并将这样的默认值用作对 COALESCE 的调用的参数，形式为 COALESCE(<你的字段或表达式>, <首选默认值>)。

英文:

Your subquery is the place where we need to check what the problem is, because, since you complained on the wrong value resulting in the update and not a lack of value change. Hence, let's see this subquery:

  1. SELECT
  2. CASE WHEN pointer.lab_hours IS NOT NULL 
  3. THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
  4. ELSE (exam_i)
  5. END

NULL is an absorbing element, so, whatever operation you perform with it, the result will be NULL. The reason is simple: NULL is the lack of value, or, in other words, it is the unknown. So, what's the result of

unkown + 2

for example? Naturally, it's unknown.

So, without knowing too much about the data you are testing, these are the fields that could have a NULL value:

  • lab_i
  • percentage
  • exam_i

So, you will likely need to figure out what the default value should be and use such a default value as a parameter to a call for COALESCE, of the form of COALESCE(&lt;your field or expression&gt;, &lt;the preferred default&gt;)

huangapple
  • 本文由 发表于 2023年5月6日 23:23:58
  • 转载请务必保留本文链接：https://go.coder-hub.com/76189681.html
  • function
  • pgadmin-4
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定