mysql - 重写条件总和和计数查询/日期范围比较的更好方法

Question

我不确定我的尝试是否正确。似乎它有很多重复的东西。

以下示例在本月/上个月运行，但通常我不希望能够设置我的子句供以后使用，例如:昨天与今天。它是一个简单的比较查询。为了让我们更轻松，我们在本月/上个月进行操作。

我的数据:

CREATE TABLE `incomes` (
  `income_id` bigint(20) NOT NULL,
  `area_id` bigint(20) NOT NULL,
  `client_id` bigint(20) NOT NULL,
  `added` bigint(20) NOT NULL,
  `gross` decimal(10,2) NOT NULL,
  `net` decimal(10,2) NOT NULL,
  `number` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `d_date` date NOT NULL,
  `added_system` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `vat_total` decimal(11,2) NOT NULL,
  `sales_date` date NOT NULL,
  `due_date` date NOT NULL,
  `days` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `incomes` (`income_id`, `area_id`, `client_id`, `added`, `gross`, `net`, `number`, `d_date`, `added_system`, `notes`, `vat_total`, `sales_date`, `due_date`, `days`) VALUES
(48, 1, 189, 3, '172.20', '140.00', '1/KOM/10/17', '2017-10-03', '2017-10-03 16:13:21', '', '32.20', '2017-10-03', '2017-11-02', 30),
(49, 1, 189, 3, '422.44', '422.44', '2/KOM/10/17', '2017-10-03', '2017-10-03 16:15:35', 'M', '0.00', '2017-10-03', '2017-11-02', 30),
(50, 3, 216, 3, '543.50', '441.87', '22/KOM/09/17', '2017-09-29', '2017-10-04 13:02:23', '', '101.63', '2017-09-29', '2017-10-18', 14),
(51, 1, 4, 3, '625.00', '625.00', '3/KOM/10/17', '2017-10-09', '2017-10-09 16:38:27', 'D 2', '0.00', '2017-10-09', '2017-11-08', 30),
(52, 3, 441, 3, '7700.00', '7700.00', '4/KOM/10/17', '2017-10-10', '2017-10-10 17:40:51', 'B17', '0.00', '2017-10-06', '2017-10-24', 14),
(53, 2, 189, 3, '553.50', '450.00', '5/KOM/10/17', '2017-10-11', '2017-10-11 17:42:50', 'BiCHER', '103.50', '2017-10-11', '2017-11-10', 30),
(54, 3, 3, 3, '3286.06', '2671.60', '6/KOM/10/17', '2017-10-17', '2017-10-17 10:50:16', 'Int', '614.46', '2017-10-17', '2017-11-16', 30),
(55, 3, 3, 3, '5388.50', '4380.90', '7/KOM/10/17', '2017-10-17', '2017-10-17 10:51:13', 'Inska', '1007.60', '2017-10-17', '2017-11-16', 30),
(56, 3, 3, 3, '1205.40', '980.00', '8/KOM/10/17', '2017-10-17', '2017-10-17 10:52:20', 'Insa', '225.40', '2017-10-17', '2017-11-16', 30),
(57, 3, 3, 3, '1033.20', '840.00', '9/KOM/10/17', '2017-10-17', '2017-10-17 10:53:10', 'Inka', '193.20', '2017-10-17', '2017-11-16', 30),
(58, 2, 437, 3, '64.80', '60.00', '10/KOM/10/17', '2017-10-17', '2017-10-17 13:29:00', 'Nume9', '4.80', '2017-10-17', '2017-11-16', 30),
(59, 2, 406, 3, '193.21', '178.90', '11/KOM/10/17', '2017-10-17', '2017-10-17 14:23:34', '', '14.31', '2017-10-17', '2017-11-16', 30),
(60, 3, 441, 3, '3575.00', '3575.00', '12/KOM/10/17', '2017-10-23', '2017-10-23 10:43:36', 'Wyk10.', '0.00', '2017-10-23', '2017-11-06', 14),
(61, 3, 4, 3, '2000.00', '2000.00', '13/KOM/10/17', '2017-10-24', '2017-10-24 15:32:23', 'Dot./16', '0.00', '2017-10-24', '2017-11-23', 30),
(62, 3, 147, 3, '8000.00', '8000.00', '14/KOM/10/17', '2017-10-24', '2017-10-24 18:29:19', 'Dota 16', '0.00', '2017-10-24', '2017-10-31', 7),
(63, 1, 189, 3, '1395.00', '1395.00', '15/KOM/10/17', '2017-10-25', '2017-10-25 13:43:50', 'Pio&M', '0.00', '2017-10-25', '2017-11-24', 30),
(64, 4, 590, 3, '775.43', '775.43', '18/KOM/08/17', '2017-08-31', '2017-10-27 12:55:31', '', '0.00', '2017-08-31', '2017-11-10', 14),
(65, 4, 590, 3, '775.43', '775.43', '23/KOM/09/17', '2017-09-29', '2017-10-27 12:56:40', '', '0.00', '2017-09-29', '2017-11-10', 14),
(66, 1, 442, 3, '282.93', '232.46', '16/KOM/10/17', '2017-10-31', '2017-10-31 12:27:55', 'Uw 6', '50.47', '2017-10-31', '2017-11-30', 30),
(68, 1, 189, 3, '399.75', '325.00', '17/KOM/10/17', '2017-10-31', '2017-10-31 12:37:26', 'Wrora', '74.75', '2017-10-31', '2017-11-30', 30),
(69, 1, 413, 3, '469.62', '434.84', '18/KOM/10/17', '2017-10-31', '2017-10-31 12:41:07', 'KsaC', '34.78', '2017-10-31', '2017-11-14', 14),
(70, 2, 111, 3, '368.87', '299.90', '19/KOM/10/17', '2017-10-31', '2017-10-31 12:46:50', '', '68.97', '2017-10-31', '2017-11-30', 30),
(71, 3, 441, 3, '2178.00', '2178.00', '1/KOM/11/17', '2017-11-02', '2017-11-02 15:37:04', '16.10-20.10.2017', '0.00', '2017-11-02', '2017-11-16', 14),
(72, 3, 441, 3, '8800.00', '8800.00', '2/KOM/11/17', '2017-11-02', '2017-11-02 15:40:11', '23.10 - 27.11.2017', '0.00', '2017-11-02', '2017-11-16', 14),
(73, 1, 413, 3, '218.19', '202.03', '20/KOM/10/17', '2017-10-31', '2017-11-06 15:55:48', 'Ksa10', '16.16', '2017-10-31', '2017-11-20', 14),
(74, 1, 132, 3, '870.47', '707.70', '21/KOM/10/17', '2017-10-31', '2017-11-06 16:22:05', '', '162.77', '2017-10-31', '2017-11-14', 14),
(75, 1, 608, 3, '413.28', '336.00', '22/KOM/10/17', '2017-10-31', '2017-11-07 13:11:58', 'Łód', '77.28', '2017-10-31', '2017-11-14', 14),
(77, 1, 146, 3, '49.20', '40.00', '23/KOM/10/17', '2017-10-31', '2017-11-07 13:26:42', 'Łź 4', '9.20', '2017-10-31', '2017-11-21', 14),
(78, 1, 590, 3, '775.43', '775.43', '24/KOM/10/17', '2017-10-31', '2017-11-07 13:31:24', '', '0.00', '2017-10-31', '2017-11-14', 14),
(79, 2, 111, 3, '2460.00', '2000.00', '25/KOM/10/17', '2017-10-31', '2017-11-07 13:39:09', '', '460.00', '2017-10-31', '2017-11-21', 14),
(81, 2, 323, 3, '3095.24', '2865.97', '26/KOM/10/17', '2017-10-31', '2017-11-07 13:41:32', '', '229.27', '2017-10-31', '2017-11-21', 14),
(82, 2, 323, 3, '1103.98', '1022.22', '27/KOM/10/17', '2017-10-31', '2017-11-07 13:54:51', '', '81.76', '2017-10-31', '2017-11-21', 14),
(83, 2, 216, 3, '2827.40', '2298.70', '28/KOM/10/17', '2017-11-07', '2017-11-07 14:16:09', '', '528.70', '2017-10-31', '2017-11-21', 14),
(84, 2, 216, 3, '4737.11', '3851.31', '29/KOM/10/17', '2017-11-07', '2017-11-07 14:18:23', '', '885.80', '2017-10-31', '2017-11-21', 14),
(85, 2, 216, 3, '1966.05', '1598.42', '30/KOM/10/17', '2017-11-07', '2017-11-07 14:36:30', '', '367.63', '2017-10-31', '2017-11-21', 14),
(86, 2, 189, 3, '615.00', '500.00', '3/KOM/11/17', '2017-11-08', '2017-11-08 10:56:24', 'Aer', '115.00', '2017-11-08', '2017-12-08', 30);

我的查询

 SELECT
  Sum(CASE
    WHEN incomes.sales_date >= Date_Format(Now(), '%Y-%m-01') AND incomes.sales_date <= Last_Day(Now())
    THEN incomes.net
    ELSE 0
  END) this_month_net,
   SUM(CASE
    WHEN incomes.sales_date >= Date_Format(Now(), '%Y-%m-01') AND incomes.sales_date <= Last_Day(Now())
    THEN 1
    ELSE 0
  END) this_month_count,
    Sum(CASE
    WHEN incomes.sales_date >= Date_Format(Now(), '%Y-%m-01') AND incomes.sales_date <= Last_Day(Now())
    THEN incomes.gross
    ELSE 0
  END) this_month_gross,
  Sum(CASE
    WHEN incomes.sales_date >= Date_Format(Now() - INTERVAL 1 MONTH, '%Y-%m-01') AND incomes.sales_date <=
      Last_Day(Now() - INTERVAL 1 MONTH)
    THEN incomes.net
    ELSE 0
  END) last_month_net,
  Sum(CASE
    WHEN sales_date >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01') AND sales_date <= LAST_DAY(NOW() - INTERVAL 1 MONTH)
    THEN incomes.gross
    ELSE 0
  END) last_month_gross,
  SUM(CASE
    WHEN sales_date >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01') AND sales_date <= LAST_DAY(NOW() - INTERVAL 1 MONTH)
    THEN 1
    ELSE 0
  END) last_month_count,
  incomes.area_id
FROM
  incomes

  group by incomes.area_id

查询逻辑

创建聚合:

本月 - date_range1 :包括 Netty 、总额、计数和area_id

上个月 - date_range2，包括 Netty 、总额、计数和 area_id

当前结果 - 它可以保持不变

   +----------------+------------------+------------------+----------------+------------------+------------------+---------+
| this_month_net | this_month_count | this_month_gross | last_month_net | last_month_gross | last_month_count | area_id |
+----------------+------------------+------------------+----------------+------------------+------------------+---------+
|              0 |                0 |                0 |         5635.9 |          6093.51 |               12 |       1 |
|            500 |                1 |              615 |       14625.42 |         17370.16 |               10 |       2 |
|          10978 |                2 |            10978 |        30147.5 |         32188.16 |                8 |       3 |
|              0 |                0 |                0 |              0 |                0 |                0 |       4 |
+----------------+------------------+------------------+----------------+------------------+------------------+---------+

SQL Fiddle Playground :http://sqlfiddle.com/#!9/482a7

Answer 1

您可以使用此查询获得相同的结果:

SELECT
   Sum(is_current * przychody.netto)    this_month_net,
   Sum(is_current)                      this_month_count,
   Sum(is_current * przychody.wartosc)  this_month_gross,
   Sum(is_previous * przychody.netto)   last_month_net,
   Sum(is_previous * przychody.wartosc) last_month_gross,
   Sum(is_previous)                     last_month_count,
   przychody.id_rejonu as area_id
FROM
  (  SELECT *, 
            Extract(YEAR_MONTH from przychody.sprzedano)
                 = Extract(YEAR_MONTH from Now()) is_current, 
            Extract(YEAR_MONTH from AddDate(przychody.sprzedano, interval 1 month))
                 = Extract(YEAR_MONTH from Now()) is_previous
     FROM przychody
   ) AS przychody
GROUP BY przychody.id_rejonu

关于 SqlFiddle

当您需要当前/上一个选择是今天/昨天时，您可以将内部查询更改为:

     SELECT *, 
            To_Days(przychody.sprzedano) = To_Days(Now()) is_current, 
            To_Days(przychody.sprzedano) + 1 = To_Days(Now()) is_previous
     FROM przychody

因此，对于当前/先前的不同定义，您应该找到正确的 bool 表达式来将 sprzedano 识别为“当前”或“先前”。