我有一个表,想做一个SQL查询以返回Postgres中匹配的行和不匹配的行。如果SQL也可以用于Informix,那将是很好,但这不是必需的。
create temp table t1 (cust integer,product char(16),qty numeric(16,2));
insert into t1 values(1000,11,100);
insert into t1 values(1000,200);
insert into t1 values(1000,22,300);
insert into t1 values(1001,400);
insert into t1 values(1002,33,500);
insert into t1 values(1003,44,600);
insert into t1 values(1004,55,700);
insert into t1 values(1004,800);
select cust,product,sum(qty)
from t1
where product = '11'
group by 1,2
union all
select cust,null,null
from t1
where product != '11'
and cust not in (select cust from t1 where product = '11')
order by cust;
cust | product | sum
------+------------------+--------
1000 | 11 | 300.00
1001 | |
1002 | |
1003 | |
1004 | |
1004 | |
我希望cust等于指定产品的所有行,但我也希望为没有产品的product和qty返回所有null的null。我有两个问题。列cust应该是唯一的,为cust 1004返回了两个重复的行,如果我在查询中使用distinct,则会在qty字段上给出有关数据类型的错误。第二个问题是,当对较大的数据集(例如10,000行)运行时,此SQL需要很长时间。