如何使用SQL代码提取数据,如果在同一天,7天的间隔或14天的间隔以不同的价格从相同/不同的供应商那里采购材料?我累了,但无法为此做代码。我是SQL的新手。
数据表 - 项目日期供应商材料数量NetPrice
以下查询检索所有记录,其在14天内以其他价格在14天内以其他价格存在。更改14或0或您想检查的任何其他值。
select *
from mytable
where exists
(
select *
from mytable other
where other.material = mytable.material
and other.netprice <> mytable.netprice
and abs(datediff(other.date, mytable.date)) <= 14 -- or 7 or 0
)
order by material, date, netprice;
MariaDB [sandbox]> DROP TABLE IF EXISTS T;
Query OK, 0 rows affected (0.11 sec)
MariaDB [sandbox]> CREATE TABLE T( Item INT ,Dt DATE ,Vendor INT, PRICE INT);
Query OK, 0 rows affected (0.16 sec)
MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO T VALUES
-> (1,'2017-06-12',1,10),(1,'2017-06-25',1,20),
-> (1,'2017-06-26',1,30),(1,'2017-06-26',1,40),
-> (2,'2017-06-26',1,10),(2,'2017-06-26',1,40),
-> (3,'2017-05-26',1,10),(3,'2017-05-26',1,40),
-> (4,'2017-06-20',1,10),(4,'2017-06-20',1,40),
-> (5,'2017-06-12',1,10),(5,'2017-06-12',1,40);
Query OK, 12 rows affected (0.07 sec)
Records: 12 Duplicates: 0 Warnings: 0
MariaDB [sandbox]>
MariaDB [sandbox]> select s.item,
-> max(case when src = 1 then s.prices else 0 end) as PricesToday,
-> max(case when src = 2 then s.prices else 0 end) as Prices7,
-> max(case when src = 3 then s.prices else 0 end) as Prices14
-> from
-> (
-> SELECT 1 as src,item,COUNT(DISTINCT PRICE) Prices
-> FROM T
-> where dt = date(now())
-> GROUP BY ITEM HAVING COUNT(DISTINCT PRICE) > 1
-> union
-> SELECT 2,item,COUNT(DISTINCT PRICE) Prices
-> FROM T
-> where datediff(date(now()),dt) <= 7
-> GROUP BY ITEM HAVING COUNT(DISTINCT PRICE) > 1
-> union
-> SELECT 3,item,COUNT(DISTINCT PRICE) Prices
-> FROM T
-> where datediff(date(now()),dt) <= 14
-> GROUP BY ITEM HAVING COUNT(DISTINCT PRICE) > 1
-> ) s
-> group by s.item;
+------+-------------+---------+----------+
| item | PricesToday | Prices7 | Prices14 |
+------+-------------+---------+----------+
| 1 | 2 | 3 | 4 |
| 2 | 2 | 2 | 2 |
| 4 | 0 | 2 | 2 |
| 5 | 0 | 0 | 2 |
+------+-------------+---------+----------+
- 创建触发器
- 然后您要将输出保存到另一个表中,因此创建该新表格。
-
类似的东西。
定界线$$ 在yous_good_old_table上更新后,创建触发check_price 每行 开始 如果new.price&lt;> old.price,则
插入your_new_better_table(newPrice( 值(new.newprice(; 万一; 结尾; $$
定界符;
注意:编辑器可以格式化代码吗?我无法使用Ctrl K