即使数据不存在，也可以选择查询以获取所有 12 个月的结果 [英] Select query to results all 12 months even if data not exist

查看：62
本文介绍了即使数据不存在，也可以选择查询以获取所有 12 个月的结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如果数据不存在，我正在尝试提取值为零的所有月份名称.

这是我目前取得的成绩

invoice_order 表

order_id |用户 ID |订单日期 |order_total_amount1 |1 |01-01-2021 |100002 |1 |02-02-2021 |20000

SQL 查询将选择第一行 (10000) 的 order_total_amount，该行将存储在 1 月份.但是对于二月份，它显示 order_total_amount 为零

只有在没有数据存在时才需要显示零值

$monthly_sale = "SELECTSUM(IF(month = 'Jan', total, 0)) AS 'Jan',SUM(IF(month = 'Feb', total, 0)) AS 'Feb',SUM(IF(month = 'Mar', total, 0)) AS 'Mar',SUM(IF(month = 'Apr', total, 0)) AS 'Apr',SUM(IF(month = 'May', total, 0)) AS 'May',SUM(IF(month = 'Jun', total, 0)) AS 'Jun',SUM(IF(month = 'Jul', total, 0)) AS 'Jul',SUM(IF(month = 'Aug', total, 0)) AS 'Aug',SUM(IF(month = 'Sep', total, 0)) AS 'Sep',SUM(IF(month = 'Oct', total, 0)) AS 'Oct',SUM(IF(month = 'Nov', total, 0)) AS 'Nov',SUM(IF(month = 'Dec', total, 0)) AS 'Dec'从 (SELECT DATE_FORMAT(order_date,'%M') AS 月份，SUM(order_total_amount) 作为总数FROM invoice_orderWHERE user_id='$user_id' group by year(order_date),month(order_date) order by year(order_date),month(order_date)) 作为销售";

上述查询将返回所有月份名称以及零值(如果数据不存在).但它只显示第一行的 order_total_amount.为了更简单，它只提取了一个月的订单量

解决方案

您有一个类型需要 date_FORMAt &b 用于您的查询

但是您的代码易受sql注入的攻击，因此不要插入$user_id，而是使用带有参数的准备好的语句，请参阅如何防止 PHP 中的 SQL 注入?

<块引用>

CREATE TABLE invoice_order (user_id int,order_date date, order_total_amount DECIMAL(10,2))

<前>✓

<块引用>

INSERT INTO invoice_order VALUES(1,'2020-01-01',10.1),(1,'2020-02-01',10.1),(1,'2020-03-01',10.1),(1,'2020-01-04',10.1),(1,'2020-05-01',10.1),(1,'2020-06-01',10.1),(1,'2020-07-01',10.1),(1,'2020-08-01',10.1),(1,'2020-09-01',10.1),(1,'2020-10-01',10.1),(1,'2020-11-01',10.1),(1,'2020-12-01',10.1),(1,'2021-01-01',10.1),(1,'2021-02-01',10.1),(1,'2021-03-01',10.1)

<块引用>

SELECTMIN(DATE_FORMAT(order_date, '%b')) AS 月，SUM(order_total_amount) AS 总计从发票_订单在哪里用户 ID = '1'GROUP BY MONTH(order_date)按月订购(订单日期)

<前>月 |全部的:---- |----:简 |30.30二月 |20.20三月 |20.20五月 |10.10君|10.10七月 |10.10八月 |10.10九月 |10.10十月 |10.10十一月 |10.10十二月 |10.10

<块引用>

SELECTSUM(IF(month = 'Jan', total, 0)) AS 'Jan',SUM(IF(month = 'Feb', total, 0)) AS 'Feb',SUM(IF(month = 'Mar', total, 0)) AS 'Mar',SUM(IF(month = 'Apr', total, 0)) AS 'Apr',SUM(IF(month = 'May', total, 0)) AS 'May',SUM(IF(month = 'Jun', total, 0)) AS 'Jun',SUM(IF(month = 'Jul', total, 0)) AS 'Jul',SUM(IF(month = 'Aug', total, 0)) AS 'Aug',SUM(IF(month = 'Sep', total, 0)) AS 'Sep',SUM(IF(month = 'Oct', total, 0)) AS 'Oct',SUM(IF(month = 'Nov', total, 0)) AS 'Nov',SUM(IF(month = 'Dec', total, 0)) AS 'Dec'从(选择MIN(DATE_FORMAT(order_date, '%b')) AS 月，SUM(order_total_amount) AS 总计从发票_订单在哪里用户 ID = '1'GROUP BY YEAR(order_date) , MONTH(order_date)ORDER BY YEAR(order_date) , MONTH(order_date)) AS 销售

<块引用><前>简 |二月 |三月 |四月 |五月 |君|七月 |八月 |九月 |十月 |十一月 |十二月----: |----: |----: |---: |----: |----: |----: |----: |----: |----: |----: |----:30.30 |20.20 |20.20 |0.00 |10.10 |10.10 |10.10 |10.10 |10.10 |10.10 |10.10 |10.10

db<>fiddle 这里

I'm trying to fetch out all month names with the value of zero if data does not exist.

Here is what I achieved so far

invoice_order table

order_id | user_id    | order_date | order_total_amount
1        | 1          | 01-01-2021 | 10000
2        | 1          | 02-02-2021 | 20000

The SQL query would pick the order_total_amount of the first row (10000) which would store in January month. but for February, it is showing order_total_amount zero

value zero needs to show up only when no data exists

$monthly_sale = "SELECT 
    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
    SUM(IF(month = 'May', total, 0)) AS 'May',
    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
    SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
    FROM (
SELECT DATE_FORMAT(order_date,'%M') AS month, SUM(order_total_amount) as total
FROM invoice_order
WHERE user_id='$user_id' group by year(order_date),month(order_date) order by year(order_date),month(order_date)) as sale";

the above query would return all month names along with zero value (if data doesn't exist). but it showing order_total_amount of only the first row. To make it more simplified, it picking up the order amount of only one month

解决方案

You have a type you need date_FORMAt &b for your query

But your code is vulnerable to sql injection so instead of inserting the $user_id use prepared statements with parameters see How can I prevent SQL injection in PHP?

CREATE TABLE invoice_order (user_id int,order_date date, order_total_amount DECIMAL(10,2))


✓


INSERT INTO invoice_order VALUES 
(1,'2020-01-01',10.1),(1,'2020-02-01',10.1),(1,'2020-03-01',10.1),
(1,'2020-01-04',10.1),(1,'2020-05-01',10.1),(1,'2020-06-01',10.1),
(1,'2020-07-01',10.1),(1,'2020-08-01',10.1),(1,'2020-09-01',10.1),
(1,'2020-10-01',10.1),(1,'2020-11-01',10.1),(1,'2020-12-01',10.1),
(1,'2021-01-01',10.1),(1,'2021-02-01',10.1),(1,'2021-03-01',10.1)


SELECT 
        MIN(DATE_FORMAT(order_date, '%b')) AS month,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id = '1'
    GROUP BY  MONTH(order_date)
    ORDER BY MONTH(order_date)


month | total
:---- | ----:
Jan   | 30.30
Feb   | 20.20
Mar   | 20.20
May   | 10.10
Jun   | 10.10
Jul   | 10.10
Aug   | 10.10
Sep   | 10.10
Oct   | 10.10
Nov   | 10.10
Dec   | 10.10


SELECT 
    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
    SUM(IF(month = 'May', total, 0)) AS 'May',
    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
    SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM
    (SELECT 
        MIN(DATE_FORMAT(order_date, '%b')) AS month,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id = '1'
    GROUP BY YEAR(order_date) , MONTH(order_date)
    ORDER BY YEAR(order_date) , MONTH(order_date)) AS sale


  Jan |   Feb |   Mar |  Apr |   May |   Jun |   Jul |   Aug |   Sep |   Oct |   Nov |   Dec
----: | ----: | ----: | ---: | ----: | ----: | ----: | ----: | ----: | ----: | ----: | ----:
30.30 | 20.20 | 20.20 | 0.00 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10

db<>fiddle here

这篇关于即使数据不存在，也可以选择查询以获取所有 12 个月的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆