时间戳

首页 > 解决方案 > 两个 SQL 表，但在一个表中显示数据

mysql - 两个 SQL 表，但在一个表中显示数据

问题描述

以下两个表格应合并为 1 并仅显示 Market、Date_buy 计数和 Date_sell 计数：

SELECT Market, macdb.Date_buy 
FROM Buy_orders_macd macdb 
JOIN SECTOR sec ON sec.Ticker = macdb.Name_buy 
WHERE macdb.Date_buy = '2020-02-18' 


SELECT Market, macds.Date_sell 
FROM Sell_orders_macd macds 
JOIN SECTOR sec ON sec.Ticker = macds.Name_sell 
WHERE macds.Date_sell = '2020-02-18'

此处的示例输出为：

Market    Count of Date_buy    Count of Date_sell
Market_1       3                     4
Market_2       2                     2

我该怎么办？我尝试了 UNION ALL，但不是这样。我拥有的其他一些数据是公司名称

标签： mysqlsql

解决方案

将这两个查询与 结合起来UNION，并使用SUM()来计算每个查询的行数，按市场分组。

SELECT Market, SUM(buy) AS `Count of Date_buy`, SUM(sell) AS `Count of Date_sell`
FROM (
    SELECT Market, 1 AS buy, 0 AS sell
    FROM Buy_orders_macd macdb 
    JOIN SECTOR sec ON sec.Ticker = macdb.Name_buy 
    WHERE macdb.Date_buy = '2020-02-18'
    UNION ALL
    SELECT Market, 0 AS buy, 1 AS sell
    FROM Sell_orders_macd macds
    JOIN SECTOR sec ON sec.Ticker = macds.Name_sell
    WHERE macds.Date_sell = '2020-02-18'
) AS x
GROUP BY Market

推荐阅读