我可以在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屋!