在SQL中计算支出差异小于一定金额的商店中的产品对

Question

我有一个表格，里面有每个产品的序列号，是否有库存(1-有库存，0-无库存(，产品的收入水平和商店中产品的支出水平。我想写一个查询，统计所有客户对(不重复同一对(，它们之间的费用差异小于1000新谢克尔，并且两者都有库存或都缺货。显示所有配对的平均收入差距(大约(，有多少配对有存货，有多少没有存货。

样品表：

收益4833026010>

串行	Is_in_stock		支出
1	1	227627	57661
2	0	20686
3	0	861
4	1	222798	37771
5	0	24606	8905
6	1	48311	6433
7	0	29929	6278
8	0	24254	8590

我们可以用两个查询来实现这一点，而无需过程或用户定义的函数

CREATE TABLE products(serial INT, Instock INT, Revenu INT, Expenses INT);
INSERT INTO products VALUES
(1,1,27627,57661),
(2,0,48330,20686),
(3,0,26010,861  ),
(4,1,22798,37771),
(5,0,24606,8905 ),
(6,1,48311,6433 ),
(7,0,29929,6278 ),
(8,0,24254,8590 );

✓✓

SELECT a.serial,b.serial from 
products a
join products b
on abs(a.expenses-b.expenses)<1000
where a.serial<b.serial
and a.instock=b.instock

-----：|-----：5|8

<blockquote\

select count(a.expenses) 'number of pairs',
avg(abs(a.expenses-b.expenses)) 'average difference',
sum(case when a.instock=1 and b.instock=1 then 1 else 0 end) pairsInstock,
sum(case when a.instock=0 and b.instock=0 then 1 else 0 end) pairsneitherStock,
sum(case when (a.instock+b.instock)=1 then 1 else 0 end ) oneInStock
from products a
cross join products b
where a.serial < b.serial;

>

对数|平均差|pairsInstock|pairsneitherStock|oneInStock--------------：|----------------：|----------------:|----------------:|----------------28 | 21362.1071 | 3 | 10 | 15

db<gt；小提琴这里

我已经在存储过程中解决了它。

1. 从变量定义开始。

2. 游标迭代排序列表的结果，并根据您对的定义检查以下条件是否为TRUE。

   prev_exp - curr_Expenses_from_the_product < 1000 AND prev_in_stock - curr_Is_in_stock = 0

3. 如果TRUE计数器增加1。

4. 最后，我关闭光标并返回计数器值。

*您可以向过程添加更多的逻辑并返回更多的列。

**此过程的用法只是按存储过程的名称调用存储过程。

表创建：

CREATE TABLE A(serial INT(11), Is_in_stock INT(11), Revenu_from_the_product INT(11), Expenses_from_the_product INT(11));

数据插入：

INSERT INTO A (serial,Is_in_stock,Revenu_from_the_product,Expenses_from_the_product) VALUES
(1,1,27627,57661),
(2,0,48330,20686),
(3,0,26010,861  ),
(4,1,22798,37771),
(5,0,24606,8905 ),
(6,1,48311,6433 ),
(7,0,29929,6278 ),
(8,0,24254,8590 );

查询：

BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE prev_exp int(11) DEFAULT 0;
DECLARE prev_in_stock int(11) DEFAULT 0;
DECLARE curr_Is_in_stock int(11) DEFAULT 0;
DECLARE curr_Expenses_from_the_product int(11) DEFAULT 0;
DECLARE duplications_counter int(11) DEFAULT 0;

-- declare cursor for relevant fields
DEClARE curs
CURSOR FOR 
SELECT A.Is_in_stock,A.Expenses_from_the_product FROM A ORDER BY A.Expenses_from_the_product DESC;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;
OPEN curs;
getRow: LOOP
FETCH curs INTO curr_Is_in_stock,curr_Expenses_from_the_product;
IF finished = 1 THEN 
LEAVE getRow;
END IF;
IF prev_exp - curr_Expenses_from_the_product < 1000 AND prev_in_stock - curr_Is_in_stock = 0 THEN
SET duplications_counter = duplications_counter+1;
END IF;
END LOOP getRow;
CLOSE curs;
-- return the counter
SELECT duplications_counter;
END

结果：

计数器：5