比较行并返回
问题描述:
我有一个表(tbl_customer)比较行并返回
id | name | birthday | address | gender
-------------------------------------------
1 | JOSEPH | 19920413 | NEW YORK | M
2 | JAKE | 19920413 | LONDON | M
3 | JOHN | 19920413 | GERMANY | M
然后我需要一个查询,将比较表格中的所有记录,然后返回列,与所有的记录(PostgreSQL系统)匹配列,它是相同的所有records..for上述结果的例子应该是:
birthday | gender
-------------------
19920413 | M
19920413 | M
19920413 | M
或好得多,如果结果是这个样子..
column_name | value
--------------------------
birthday | 19920413
gender | M
个
谢谢:)
答
create function foo(out f_name text, out f_value text) returns setof record language plpgsql immutable as $$
declare
h hstore;
r hstore := null;
n text[];
begin
for h in select hstore(t.*) from tbl_customer as t loop
if r is null then
r := h;
else
/* -- To ignore NULLs so the null values does not affects to the result
select array_agg(key) into n from each(r) where value is null;
r := r || coalesce(slice(h, n), '');
select array_agg(key) into n from each(h) where value is null;
h := h || coalesce(slice(r, n), '');
*/ -- I believe that there is much more elegant solution is possible
r := r - akeys(r - h);
exit when r = '';
end if;
end loop;
raise info '%', r;
return query select * from each(r);
end $$;
select * from foo();
INFO: "gender"=>"M", "birthday"=>"19920413"
╔══════════╤══════════╗
║ f_name │ f_value ║
╠══════════╪══════════╣
║ gender │ M ║
║ birthday │ 19920413 ║
╚══════════╧══════════╝
答
静态代码解决方案
select (array ['id','name','birthday','address','gender'])[pos] as column_name
,min(val) as value
from t cross join
unnest(array [id::varchar(10),name,birthday::varchar(10),address,gender]) with ordinality u(val,pos)
group by pos
having ( count (distinct val) = 1
and count(*) = count (val)
)
or count (val) = 0
什么代码是不能工作或者是什么代码,你一直在测试为? –
@HanselF。我还没有开始任何代码.. – john1717
你能更准确地知道你想要什么吗?例如。你是否想要返回一个列名(及其不同的值),当且仅当该列在表中只有一个不同的值时?你想如何处理NULL? – verbatross