搜索
搜索

结合使用UNION ALL和子查询和变量的MySQL错误

paul724 发表于 Dev
32
paul724

我有一个表currency_lkp和一个表associate_data，associate_data有以下列-货币，发票总额和状态（未付款，已付款或等待中），我可以成功地将表与该查询的上半部分链接（请参阅MySQL小提琴），并提供总计每种货币。

我还可以为转换为USD的每种状态提供正确的总计（请参见查询的下半部分，该行产生一行

当我在两半之间使用UNION ALL时，会产生结果，但中间三列的数字非常奇怪，但在第四列和该列的最后一行中却是正确的结果。

我认为查询还可以，但如果可以在子查询中使用变量，则可以更整洁。任何关于出问题或如何改善优雅的想法将不胜感激-谢谢

要创建示例数据库：（在此下方的MySQL查询）

    CREATE TABLE IF NOT EXISTS `currency_lkp` (
  `currency_id` int(11) NOT NULL AUTO_INCREMENT,
  `currency` varchar(10) NOT NULL,
  `currency_label` varchar(100) NOT NULL,
  `currency_description` varchar(100) NOT NULL,
  `exchange_rate_USD` float NOT NULL,
  PRIMARY KEY (`currency_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `currency_lkp` (`currency_id`, `currency`, 
`currency_label`, `currency_description`, `exchange_rate_USD`)
VALUES
(1, 'GBP', '£', 'Great British Pound (Sterling)', 0.75),
(2, 'Euro', '€', 'Euro currency for the Euro Zone', 0.85),
(3, 'USD', '$', 'United States Dollars', 1),
(4, 'CLP', '$', 'Chilean Pesos', 639.85),
(5, 'INR', '&#8377', 'Indian Rupees', 64.8);

CREATE TABLE IF NOT EXISTS `associate_data` (`associate_data_id`
int(11) NOT NULL AUTO_INCREMENT,
`currency` varchar(10) NOT NULL, 
`status` varchar(20) NOT NULL, 
`invoice_total` float,
 PRIMARY KEY (`associate_data_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `associate_data` (`associate_data_id`, `currency`, 
`status`, `invoice_total`)
 VALUES
(1, 'GBP', 'Paid', 200),
(2, 'GBP', 'Unpaid', 300),
(3, 'Euro', 'Paid', 1000),
(4, 'Euro', 'Unpaid', 2000),
(5, 'USD', 'Paid', 1200),
(6, 'USD', 'Unpaid', 1400),
(7, 'GBP', 'WaitingInvoice', 400),
(8, 'Euro', 'WaitingInvoice', 1300),
(9, 'USD', 'WaitingInvoice', 2300),
(10, 'CLP', 'Paid', 100200),
(11, 'CLP', 'Unpaid', 110400),
(12, 'CLP', 'WaitingInvoice', 11200);

MySQL Query in full:
`
(
SELECT cl.currency,
(SELECT SUM(invoice_total) FROM associate_data ad2
WHERE ad2.currency = cl.currency  AND ad2.status = 'Paid') AS Paid,
(SELECT SUM(invoice_total) FROM associate_data ad3
WHERE ad3.currency = cl.currency  AND ad3.status = 'Unpaid') AS Unpaid,
(SELECT SUM(invoice_total) FROM associate_data ad4
WHERE ad4.currency = cl.currency  AND ad4.status = 'WaitingInvoice') AS WaitingInvoice,
(SELECT IFNULL(Unpaid,0) + IFNULL(WaitingInvoice, 0)) AS TotalToPay
FROM currency_lkp cl
)

UNION ALL

(
SELECT 'Total in USD',

FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad5
WHERE ad5.currency = clu.currency AND (ad5.status = 'Paid'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDPaid,

FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDUnpaid,

FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDWaitingInvoice,

FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice' OR ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDToPay
FROM currency_lkp clu
)`
Used_By_Already

您可以使用“条件聚合”（聚合函数中的一个条件表达式）从根本上简化查询（并提高效率），如下所示：

select
      cu.currency
    , sum(case when ad.status = 'Paid' then invoice_total end) as Paid
    , sum(case when ad.status = 'Unpaid' then invoice_total end) as Unpaid
    , sum(case when ad.status = 'WaitingInvoice' then invoice_total end) as WaitingInvoice_USD
    , FORMAT(sum(case when ad.status = 'Paid' then invoice_total/cu.exchange_rate_USD end),0) as Paid_USD
    , FORMAT(sum(case when ad.status = 'Unpaid' then invoice_total/cu.exchange_rate_USD end),0) as Unpaid_USD
    , FORMAT(sum(case when ad.status = 'WaitingInvoice' then invoice_total/cu.exchange_rate_USD end),0) as WaitingInvoice_USD  
FROM associate_data ad
inner join currency_lkp cu ON ad.currency = cu.currency
group by
      cu.currency
union all
select
      'Total'
    , sum(case when ad.status = 'Paid' then invoice_total end) as Paid
    , sum(case when ad.status = 'Unpaid' then invoice_total end) as Unpaid
    , sum(case when ad.status = 'WaitingInvoice' then invoice_total end) as WaitingInvoice_USD
    , FORMAT(sum(case when ad.status = 'Paid' then invoice_total/cu.exchange_rate_USD end),0) as Paid_USD
    , FORMAT(sum(case when ad.status = 'Unpaid' then invoice_total/cu.exchange_rate_USD end),0) as Unpaid_USD
    , FORMAT(sum(case when ad.status = 'WaitingInvoice' then invoice_total/cu.exchange_rate_USD end),0) as WaitingInvoice_USD  
FROM associate_data ad
inner join currency_lkp cu ON ad.currency = cu.currency
;

该查询产生以下结果：

| currency |   Paid | Unpaid | WaitingInvoice_USD | Paid_USD | Unpaid_USD | WaitingInvoice_USD |
|----------|--------|--------|--------------------|----------|------------|--------------------|
|      CLP | 100200 | 110400 |              11200 |      157 |        173 |                 18 |
|     Euro |   1000 |   2000 |               1300 |    1,176 |      2,353 |              1,529 |
|      GBP |    200 |    300 |                400 |      267 |        400 |                533 |
|      USD |   1200 |   1400 |               2300 |    1,200 |      1,400 |              2,300 |
|    Total | 102600 | 114100 |              15200 |    2,800 |      4,325 |              4,380 |

观看此演示：http : //sqlfiddle.com/#!9/ b4b5bb/ 36

在先前查询的某些列中出现了gobbledygook的原因是试图在同一列中合并数字和格式化的（文本）数据。除掉format( ,0)

参见：http : //sqlfiddle.com/#!9/b4b5bb/37

(
SELECT cl.currency,
(SELECT SUM(invoice_total) FROM associate_data ad2
WHERE ad2.currency = cl.currency  AND ad2.status = 'Paid') AS Paid,
(SELECT SUM(invoice_total) FROM associate_data ad3
WHERE ad3.currency = cl.currency  AND ad3.status = 'Unpaid') AS Unpaid,
(SELECT SUM(invoice_total) FROM associate_data ad4
WHERE ad4.currency = cl.currency  AND ad4.status = 'WaitingInvoice') AS WaitingInvoice,
(SELECT IFNULL(Unpaid,0) + IFNULL(WaitingInvoice, 0)) AS TotalToPay
FROM currency_lkp cl
)
UNION ALL
(
SELECT 'Total in USD',


SUM(IFNULL(
(
  SELECT SUM(invoice_total) FROM associate_data ad5
WHERE ad5.currency = clu.currency AND (ad5.status = 'Paid')
),0.1)/clu.exchange_rate_USD) 
  AS TotalUSDPaid,


SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
 AS TotalUSDUnpaid,


SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice'))
,0.1)/clu.exchange_rate_USD)
 AS TotalUSDWaitingInvoice,


SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice' OR ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
 AS TotalUSDToPay
FROM currency_lkp clu
)

该查询的结果是：

|     currency |               Paid |           Unpaid |    WaitingInvoice |        TotalToPay |
|--------------|--------------------|------------------|-------------------|-------------------|
|          GBP |                200 |              300 |               400 |               700 |
|         Euro |               1000 |             2000 |              1300 |              3300 |
|          USD |               1200 |             1400 |              2300 |              3700 |
|          CLP |             100200 |           110400 |             11200 |            121600 |
|          INR |             (null) |           (null) |            (null) |                 0 |
| Total in USD | 2799.7379740260294 | 4325.48309943104 | 4380.250701542091 | 8705.732257763328 |

注意：我没有尝试更正任何查询算法，但显然其中之一是错误的

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

MySQL-使用子查询进行聚合和GROUP BY

使用UNION / UNION ALL和Group By的MySQL查询错误

结合使用^和变量

结合Mysql和Sphinx查询？

使用IF语句和子查询的mysql查询中的错误

将UNION ALL与GROUP BY结合

如何使用MySQL子查询和GROUP BY

结合UNION和EXCEPT语句时，sqlite中的括号

在NOT IN MySQL中使用UNION子查询

将mypy Union和嵌套的TypedDict结合使用会导致mypy错误：返回值不兼容

使用UNION和GROUP BY的AS

与WITH结合使用独立查询

如何将select *与union结合使用？MySQL的

结合使用MYSQL GROUP_CONCAT和子查询

mysql查询与Join和union all

MySQL Join和EXISTS结合使用

将MySQL IN（）与PHP和PDO结合使用

结合使用GROUP_BY和查询构建器和mySQL> = 5.7

SQL子查询与UNION ALL查询结合

结合MySQL表与查询UNION或JOIN？

使用案例和子查询MySql更新多个表

MySQL - 在 join 和 from 上使用子查询

如何在mysql中使用join和union进行子查询

使用 UNION 和 DISTINCT 优化 MySQL 选择查询

MySQL：如何使用子查询和连接选择 min()

对 JOIN 和 UNION 以及子查询使用相同的“WHERE”子句

Mysql：在使用 union all 时使用 order by 和 limit 到单独的查询中

使用 xmllagg 的子查询错误和太多值

将 SUM 与 UNION 结合使用

TOP 榜单

  1. 1

    UITableView的项目向下滚动后更改颜色，然后快速备份

  2. 2

    Linux的官方Adobe Flash存储库是否已过时？

  3. 3

    用日期数据透视表和日期顺序查询

  4. 4

    应用发明者仅从列表中选择一个随机项一次

  5. 5

    Mac OS X更新后的GRUB 2问题

  6. 6

    验证REST API参数

  7. 7

    Java Eclipse中的错误13，如何解决？

  8. 8

    带有错误“ where”条件的查询如何返回结果？

  9. 9

    ggplot：对齐多个分面图-所有大小不同的分面

  10. 10

    尝试反复更改屏幕上按钮的位置 - kotlin android studio

  11. 11

    如何从视图一次更新多行（ASP.NET - Core）

  12. 12

    计算数据帧中每行的NA

  13. 13

    蓝屏死机没有修复解决方案

  14. 14

    在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

  15. 15

    离子动态工具栏背景色

  16. 16

    VB.net将2条特定行导出到DataGridView

  17. 17

    通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

  18. 18

    在Windows 7中无法删除文件（2）

  19. 19

    python中的boto3文件上传

  20. 20

    当我尝试下载 StanfordNLP en 模型时，出现错误

  21. 21

    Node.js中未捕获的异常错误，发生调用

热门标签

归档