如何根据某些条件从UNION获得唯一
本文关键字：UNION 唯一 条件 何根 | 更新日期: 2023-09-27 17:57:01
如果我在多个查询（例如 4 个）之间建立联合，我得到以下内容
结果：
id name num
13 task1 0
13 task1 7102
如果联合语句中有多个具有相同 id 的记录，如何仅获取 num 大于 0 的唯一值。
foreach
SELECT DISTINCT a.task_code,
a.task_name ,
0 AS cand_num INTO ll_task_code ,
ls_task_name,
ll_cand_num
FROM rmtask a,
rmtaskstate b,
rmstateuser c
WHERE (a.task_code = b.task_code)
AND (b.state_code = c.state_code)
AND (c.emp_num = al_emp_num)
AND (al_new_flag = 0
OR (al_new_flag = 1
AND b.new_flag = 1))
UNION --candidate
SELECT DISTINCT a.task_code,
a.task_name ,
cand.emp_num
FROM rmtask a,
rmtaskstate b,
rmstateuser c ,
rmcandidate cand
WHERE (a.task_code = b.task_code)
AND (b.state_code = c.state_code)
AND (c.emp_num = cand.emp_num)
AND (al_new_flag = 0
OR (al_new_flag = 1
AND b.new_flag = 1))
AND (cand.task_code = b.task_code)
AND (cand.emp_num_candidate = al_emp_num)
AND (cand.from_date <= DATE(CURRENT))
AND (cand.to_date >= DATE(CURRENT)
OR cand.to_date IS NULL)
UNION
SELECT DISTINCT a.task_code,
a.task_name ,
0 AS cand_num
FROM rmtask a,
rmtaskstate b,
rmstategroup c
WHERE (a.task_code = b.task_code) )
UNION --candidate
SELECT DISTINCT a.task_code,
a.task_name ,
cand.emp_num
FROM rmtask a,
rmtaskstate b,
rmstategroup c ,
rmcandidate cand
WHERE (a.task_code = b.task_code)
AND (b.state_code= c.state_code)
AND (al_new_flag = 0
OR (al_new_flag = 1
AND b.new_flag = 1))
AND (((c.group_type = 0))
OR ((c.group_type = 1)
AND (c.group_code =
(SELECT x.degree_code
FROM hr_l x
WHERE x.emp_num = cand.emp_num
AND x.degree_date =
(SELECT max(xx.degree_date)
FROM hm xx
WHERE xx.emp_num = x.emp_num))))
OR ((c.group_type = 2)
AND (c.group_code =
(SELECT y.title_code
FROM ht y
WHERE y.emp_num = cand.emp_num
AND y.title_date =
(SELECT max(yy.title_date)
FROM ht yy
WHERE yy.emp_num = y.emp_num))))
OR ((c.group_type = 3)
AND (1 = r_boss(cand.emp_num)))
OR ((c.group_type = 4)
AND (0 <
(SELECT count(*)
FROM ht x
WHERE x.emp_num = cand.emp_num
AND x.perm_flag = 1)))
OR ((c.group_type = 5)
AND (0 <
(SELECT count(*)
FROM hm x
WHERE x.emp_num = cand.emp_num
AND x.vac_flag = 1)))
OR ((c.group_type = 6)
AND (0 <
(SELECT count(*)
FROM hm x
WHERE x.emp_num = cand.emp_num
AND x.mission_flag = 1))))
AND (cand.task_code = b.task_code)
AND (cand.emp_num_candidate = al_emp_num)
AND (cand.from_date <= DATE(CURRENT))
AND (cand.to_date >= DATE(CURRENT)
OR cand.to_date IS NULL) RETURN ll_task_code ,
ls_task_name,
ll_cand_num WITH resume ;
end foreach;
- 在 FROM 子句中使用 JOIN 表示法而不是逗号列表。
- 我们不需要看到你所有的查询来给出答案;工会的两个部分就足够了。 您
- 没有提到您正在使用的 Informix 版本，这可能会有所作为。
- 如果您的示例输出与您编写的 SQL 相关联，则会更容易。 您的示例输出使用
id、name、num，但 SQL 似乎使用task_code、task_name和cand_num。 - 您似乎需要给定任务代码和任务名称的最大候选编号。
因此，像这样编写查询：
SELECT u.task_code, u.task_name, MAX(u.cand_num)
FROM (SELECT DISTINCT a.task_code, a.task_name, 0 AS cand_num
FROM rmtask a
JOIN rmtaskstate b ON a.task_code = b.task_code
JOIN rmstateuser c ON b.state_code = c.state_code
WHERE (c.emp_num = al_emp_num)
AND (al_new_flag = 0 OR (al_new_flag = 1 AND b.new_flag = 1))
UNION
SELECT DISTINCT a.task_code, a.task_name, cand.emp_num
FROM rmtask a
JOIN rmtaskstate b ON a.task_code = b.task_code
JOIN rmstateuser c ON b.state_code = c.state_code
JOIN rmcandidate cand ON c.emp_num = cand.emp_num
WHERE (al_new_flag = 0 OR (al_new_flag = 1 AND b.new_flag = 1))
AND (cand.task_code = b.task_code)
AND (cand.emp_num_candidate = al_emp_num)
AND (cand.from_date <= DATE(CURRENT))
AND (cand.to_date >= DATE(CURRENT) OR cand.to_date IS NULL)
) AS u
GROUP BY u.task_code, u.task_name;
显然，您可以在子查询中添加其他替代 UNION 分支。
如果只有一行包含 num != 0 ，或者您只需要一个结果，则将现有查询放在子查询中并使用 GROUP BY 和聚合：
select id,name,MAX(num) as num
from
(
select id,name,num from abc
union
select id,name,num from def
union
select id,name,num from ghi
) as t
group by id,name