我可以在MySQL中使用if语句吗？ [英] Can i use if statement in mysql?
问题描述
我正在尝试创建一个查询，该查询将生成其使用总量（取决于价格）。我不知道这是否可以在查询中找到，所以就在这里。
I am trying to create a query that will generate their total use of usage that depends on prices. I don't know if this is possible in just query, so here it is.
主表//该表适用于所有人，因此这是连续的，
Main table // This table is for all the people, so this is continuos, this can be added, the ID column is unique.
id price date_created
25 8.5 2019-08-16
26 11.5 2019-08-01
电表//此表是基于main_id，只要main_id在主表中存在，则该使用情况表每天都会具有主表中存在的所有main_id。
electricity table // This table is continuos base on the main_id, as long as the main_id is existence in the Main table every day this usage table will have its everyday value for all the main_id that is existing in the main table. this is like the electricity generated for their everyday use.
id main_id current_usage date_generated
1 25 10 2019-08-16
2 25 10 2019-08-17
3 25 10 2019-08-18
4 25 10 2019-08-19
5 25 10 2019-08-20
6 25 10 2019-08-21
7 26 20 2019-08-01
8 26 5 2019-08-02
9 26 5 2019-08-03
10 26 10 2019-08-04
我的查询
SELECT main.id
, main.price
, SUM(electricity.current_usage)*main.price total_generated
, electricity.date_generated
FROM main AS main
INNER JOIN electricity AS electricity
ON main.id = electricity.main_id
GROUP
BY MONTH(electricity.date_generated);
在这里，我没有问题，因为我想到达这里只是一个就是一个。
Here I don't have a problem Because what I want to get here is just One is to One.
所以输出将是
id price total_genereated
25 8.5 510
26 11.5 460
这是我的问题所在，这是我被卡住的地方
here is what my problem is, This is where I got stuck
有一个 price_history 的表，该表的目的是使价格可以随时在主表中更改该main_id。
there is a table of price_history , this table has the purpose of, that the price can change anytime for that main_id in the Main table.
id main_id changed_price price_date_changed
1 25 15 2019-08-18
2 26 20 2019-08-03
而不是仅获得两行。由于价格历史记录，我需要在这里显示4行。价格已更改。
instead of getting just two rows only. I need to have 4 rows here because of the price history. the price has changed.
样本输出
id price total_genereated date_range
25 8.5 170 2019-08-16 - 2019-08-17
25 15 600 2019-08-18 - 2019-08-21
26 11.5 287 2019-08-01 - 2019-08-02
26 20 300 2019-08-03 - 2019-08-04
我不知道我的想法是否真的可能仅通过在Query上执行就可以了，我真的陷入了这个问题。我在想的是在MYSQL中使用IF语句，还是在查询中这样做很复杂？
I don't know if my idea is really possible just by Only doing it on Query, i am really stuck with this problem. What I am thinking is to use IF statement here in MYSQL or this is just way to complicated to do in Query?
推荐答案
SQL DEMO
首先，您需要将所有价格放在一起：
First you need to put all the prices together:
SELECT id as main_id, price, date_created
FROM main
UNION
SELECT main_id, changed_price, price_date_changed
FROM price_history
ORDER BY main_id, date_created;
输出
| main_id | price | date_created |
|---------|-------|--------------|
| 25 | 9 | 2019-08-16 |
| 25 | 15 | 2019-08-18 |
| 26 | 12 | 2019-08-01 |
| 26 | 20 | 2019-08-03 |
现在使用相关子查询，您可以找到最新价格：
Now using a correlated subquery you find the latest price:
SELECT id, main_id, current_usage, date_generated,
( SELECT price
FROM ( SELECT id as main_id, price, date_created
FROM main
UNION
SELECT main_id, changed_price, price_date_changed
FROM price_history
) prices
WHERE prices.main_id = electricity.main_id
AND prices.date_created <= electricity.date_generated
ORDER BY prices.date_created DESC
LIMIT 1 ) as price
FROM electricity
ORDER BY main_id, date_generated;
输出
| id | main_id | current_usage | date_generated | price |
|----|---------|---------------|----------------|-------|
| 1 | 25 | 10 | 2019-08-16 | 9 |
| 2 | 25 | 10 | 2019-08-17 | 9 |
| 3 | 25 | 10 | 2019-08-18 | 15 |
| 4 | 25 | 10 | 2019-08-19 | 15 |
| 5 | 25 | 10 | 2019-08-20 | 15 |
| 6 | 25 | 10 | 2019-08-21 | 15 |
| 7 | 26 | 20 | 2019-08-01 | 12 |
| 8 | 26 | 5 | 2019-08-02 | 12 |
| 9 | 26 | 5 | 2019-08-03 | 20 |
| 10 | 26 | 10 | 2019-08-04 | 20 |
这篇关于我可以在MySQL中使用if语句吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
