mysql自连接

Question

我有一个名为receiving的表，其中包含4列：

id, date, volume, volume_units

体积单位始终存储为"Lbs“或"Gals”的值。

我正在尝试编写一个SQL查询，以获取特定日期范围内以Lbs和Gals表示的卷的总和。类似这样的东西：(这不起作用)

SELECT sum(p1.volume) as lbs, 
p1.volume_units, 
sum(p2.volume) as gals, 
p2.volume_units 
FROM receiving as p1, receiving as p2
where p1.volume_units = 'Lbs'
and p2.volume_units = 'Gals' 
and p1.date between "2012-01-01" and "2012-03-07" 
and p2.date between "2012-01-01" and "2012-03-07" 

当我单独运行这些查询时，结果相差很远。我知道这里的连接是错误的，但我不知道我做错了什么来修复它。

Answer 1

  SELECT SUM(volume) AS total_sum,
         volume_units
    FROM receiving
   WHERE `date` BETWEEN '2012-01-01'
                    AND '2012-03-07'
GROUP BY volume_units

Answer 2

您可以通过在SUM中使用IF(condition,then,else)在一个查询中实现这一点

SELECT SUM(IF(volume_units="Lbs",volume,0)) as lbs,
       SUM(IF(volume_units="Gals",volume,0)) as gals,
FROM receiving
WHERE `date` between "2012-01-01" and "2012-03-07" 

这只会在单位正确的情况下添加volume。

Answer 3

此查询将显示每个ID的总计。

SELECT  s.`id`, 
        CONCAT(s.TotalLbsVolume, ' ', 'lbs') as TotalLBS,
        CONCAT(s.TotalGalVolume, ' ', 'gals') as TotalGAL
FROM
    (
        SELECT  `id`, SUM(`volume`) as TotalLbsVolume
        FROM    Receiving a INNER JOIN
                    (
                        SELECT  `id`, SUM(`volume`) as TotalGalVolume
                        FROM    Receiving
                        WHERE   (volume_units = 'Gals') AND
                                (`date` between '2012-01-01' and '2012-03-07')
                        GROUP BY `id`
                    ) b ON a.`id` = b.`id`
        WHERE   (volume_units = 'Lbs') AND
                (`date` between '2012-01-01' and '2012-03-07')
        GROUP BY `id`
    ) s