如何在Postgresql中删除空值并将其设置为10行？

本文介绍了如何在Postgresql中删除空值并将其设置为10行？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我是Postgresql的新手。我有一个名为 sales的表。I am new to Postgresql. I have a table called 'sales'.create table sales ( cust varchar(20), prod varchar(20), day integer, month integer, year integer, state char(2), quant integer )insert into sales values ('Bloom', 'Pepsi', 2, 12, 2001, 'NY', 4232);insert into sales values ('Knuth', 'Bread', 23, 5, 2005, 'PA', 4167);insert into sales values ('Emily', 'Pepsi', 22, 1, 2006, 'CT', 4404);insert into sales values ('Emily', 'Fruits', 11, 1, 2000, 'NJ', 4369);insert into sales values ('Helen', 'Milk', 7, 11, 2006, 'CT', 210);insert into sales values ('Emily', 'Soap', 2, 4, 2002, 'CT', 2549);类似这样的东西： something like this:现在，我想找到最有利的月份（当时产品的大部分金额为售出）和每种产品的最差月（当售出的产品数量最少时）。Now I want to find the "most favorable" month (when most amount of the product wassold) and the "least favorable" month (when the least amount of the product was sold) for each product.结果应如下所示： The result should be like this:我输入SELECT prod product, MAX(CASE WHEN rn2 = 1 THEN month END) MOST_FAV_MO, MAX(CASE WHEN rn1 = 1 THEN month END) LEAST_FAV_MOFROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant ) rn1, ROW_NUMBER() OVER(PARTITION BY prod ORDER BY quant DESC) rn2 FROM sales) xWHERE rn1 = 1 or rn2 = 1GROUP BY prod,quant;然后每种产品的值为空，总共有20行： Then there are null values for each product and there are 20 rows in total:那么如何删除空值在这些行中，使行总数达到10（总共有10种不同的产品）？So how can I remove the null values in these rows and make the total number of rows to 10 (There are 10 distinct products in total)???推荐答案 I应该说 GROUP BY 子句应该是GROUP BY prod否则，每个不同的量会得到一行，这不是您想要的。Otherwise you get one line per different quant, which is not what you want. 这篇关于如何在Postgresql中删除空值并将其设置为10行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！