组\u concat筛选器价格

pdtvr36n  于 2021-06-24  发布在  Mysql
关注(0)|答案(0)|浏览(196)

我在为下面的案子寻找解决办法。假设给出了以下数据结构:

CREATE TABLE IF NOT EXISTS `customer_prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer` int(11) unsigned NOT NULL,
  `price` decimal(8,2) unsigned DEFAULT NULL,
  `date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_id_period` (`customer`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

INSERT INTO `customer_prices` (`id`, `customer`, `price`, `date`) VALUES
    (131739296, 1, 12.56, '2018-01-01'),
    (131739297, 1, 18.33, '2018-01-02'),
    (131739301, 1, 15.66, '2018-01-03'),
    (131739302, 1, 35.40, '2018-01-04'),
    (131739303, 1, 16.96, '2018-01-05'),
    (131739304, 1, 11.90, '2018-01-06'),
    (131739305, 1, 17.31, '2018-01-07'),
    (131739306, 1, 15.75, '2018-01-08');

执行以下查询

SELECT 
AVG(price) + AVG(price) / 100 * 10 AS maxprice,
AVG(price) - AVG(price) / 100 * 10 AS minprice,
GROUP_CONCAT(price)
FROM customer_prices 
WHERE date > '2018-01-01' AND date < '2018-01-07'

将产生一份价格清单

18.33, 15.66, 35.40, 16.96, 11.90

我想要的是把这个标价限制在minprice和maxprice之间。
我本来想这样做的,但给了我一个错误。

SELECT 
GROUP_CONCAT(price)
FROM customer_prices 
WHERE date > '2018-01-01' AND date < '2018-01-07'
AND price > (AVG(price) - AVG(price) / 100 * 10)
AND price < (AVG(price) + AVG(price) / 100 * 10)

有什么解决办法吗?谢谢!

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题