对于下表,我想得到一个表,它显示每个商店中销售最多的产品,相同的产品可以是多个商店中销售最多的产品。
这是我尝试过的查询:
SELECT StoreID, ProductID, MAX(Quantity), TotalPrice FROM MyTable GROUP BY StoreID LIMIT 0, 99999;我得到的答案是:
|StoreID|ProductID|MAX(Quantity)|TotalPrice
'1', '1', '566', '288.0000'
'2', '1', '782', '648.0000'
'3', '1', '473', '172.8000'问题是:我检查过了,最大数量是正确的
但出于某种原因,它显示每个产品的ID '1‘,即使它不是任何商店中销量最多的产品。
对于此表(称为“MyTable”):
|StoreID|TotalPrice|ProductID|Quantity
'1', '288.0000', '1', '167'
'1', '760.0000', '2', '417'
'1', '400.0000', '3', '70'
'1', '352.0000', '4', '218'
'1', '1105.0000', '5', '117'
'1', '600.0000', '6', '156'
'1', '300.0000', '7', '193'
'1', '960.0000', '8', '84'
'1', '1552.0000', '9', '36'
'1', '558.0000', '10', '115'
'1', '201.6000', '11', '218'
'1', '152.0000', '12', '8'
'1', '9.6000', '13', '187'
'1', '167.4000', '14', '47'
'1', '62.0000', '15', '42'
'1', '417.0000', '16', '451'
'1', '936.0000', '17', '345'
'1', '450.0000', '18', '112'
'1', '7.3000', '19', '68'
'1', '1701.0000', '20', '66'
'1', '80.0000', '21', '337'
'1', '100.8000', '22', '58'
'1', '151.2000', '23', '275'
'1', '43.2000', '24', '431'
'1', '44.8000', '25', '171'
'1', '1245.0000', '26', '297'
'1', '526.5000', '27', '185'
'1', '1528.8000', '28', '127'
'1', '1485.0000', '29', '61'
'1', '124.2000', '30', '158'
'1', '600.0000', '31', '566'
'1', '153.6000', '32', '91'
'1', '120.0000', '33', '294'
'1', '112.0000', '34', '62'
'1', '57.6000', '35', '249'
'1', '456.0000', '36', '200'
'1', '208.0000', '37', '18'
'1', '4216.0000', '38', '86'
'1', '57.6000', '39', '192'
'1', '735.0000', '40', '431'
'1', '123.2000', '41', '220'
'1', '22.4000', '42', '183'
'1', '920.0000', '43', '141'
'1', '325.5000', '44', '281'
'1', '114.0000', '45', '360'
'1', '288.0000', '46', '249'
'1', '418.0000', '47', '156'
'1', '191.2500', '48', '45'
'1', '240.0000', '49', '45'
'1', '406.2500', '50', '25'
'1', '1696.0000', '51', '271'
'1', '112.0000', '52', '146'
'1', '1048.0000', '53', '202'
'1', '59.0000', '54', '180'
'1', '384.0000', '55', '147'
'1', '2128.0000', '56', '428'
'1', '234.0000', '57', '146'
'1', '318.0000', '58', '201'
'1', '3080.0000', '59', '504'
'1', '544.0000', '60', '433'
'1', '364.8000', '61', '221'
'1', '591.0000', '62', '247'
'1', '2808.0000', '63', '187'
'1', '1330.0000', '64', '194'
'1', '336.0000', '65', '250'
'1', '816.0000', '66', '110'
'1', '56.0000', '67', '105'
'1', '30.0000', '68', '245'
'1', '432.0000', '69', '305'
'1', '252.0000', '70', '254'
'1', '344.0000', '71', '326'
'1', '556.0000', '72', '400'
'1', '240.0000', '73', '143'
'1', '400.0000', '74', '70'
'1', '186.0000', '75', '490'
'1', '216.0000', '76', '348'
'1', '104.0000', '77', '255'
'2', '648.0000', '1', '317'
'2', '608.0000', '2', '453'
'2', '160.0000', '3', '128'
'2', '422.4000', '4', '109'
'2', '85.4000', '5', '124'
'2', '150.0000', '6', '77'
'2', '240.0000', '7', '332'
'2', '2240.0000', '8', '222'
'2', '4850.0000', '9', '53'
'2', '396.8000', '10', '435'
'2', '403.2000', '11', '293'
'2', '570.0000', '12', '107'
'2', '96.0000', '13', '477'
'2', '279.0000', '14', '224'
'2', '155.0000', '15', '60'
'2', '139.0000', '16', '294'
'2', '468.0000', '17', '416'
'2', '2000.0000', '18', '218'
'2', '109.5000', '19', '422'
'2', '2592.0000', '20', '219'
'2', '160.0000', '21', '286'
'2', '201.6000', '22', '204'
'2', '180.0000', '23', '187'
'2', '54.0000', '24', '363'
'2', '134.4000', '25', '120'
'2', '249.0000', '26', '164'
'2', '1755.0000', '27', '80'
'2', '1019.2000', '28', '281'
'2', '1980.0000', '29', '458'
'2', '165.6000', '30', '175'
'2', '200.0000', '31', '533'
'2', '1024.0000', '32', '176'
'2', '50.0000', '33', '233'
'2', '156.8000', '34', '158'
'2', '288.0000', '35', '348'
'2', '608.0000', '36', '359'
'2', '468.0000', '37', '18'
'2', '4216.0000', '38', '292'
'2', '604.8000', '39', '317'
'2', '588.0000', '40', '518'
'2', '77.0000', '41', '301'
'2', '112.0000', '42', '223'
'2', '552.0000', '43', '298'
'2', '248.0000', '44', '136'
'2', '380.0000', '45', '40'
'2', '48.0000', '46', '230'
'2', '121.6000', '47', '197'
'2', '153.0000', '48', '15'
'2', '640.0000', '49', '344'
'2', '195.0000', '50', '101'
'2', '1484.0000', '51', '328'
'2', '44.8000', '52', '198'
'2', '393.0000', '53', '320'
'2', '59.0000', '54', '295'
'2', '403.2000', '55', '283'
'2', '121.6000', '56', '486'
'2', '249.6000', '57', '202'
'2', '318.0000', '58', '102'
'2', '660.0000', '59', '630'
'2', '1088.0000', '60', '782'
'2', '2052.0000', '61', '177'
'2', '591.0000', '62', '399'
'2', '280.8000', '63', '223'
'2', '931.0000', '64', '397'
'2', '252.0000', '65', '229'
'2', '408.0000', '66', '95'
'2', '210.0000', '67', '55'
'2', '200.0000', '68', '298'
'2', '518.4000', '69', '232'
'2', '240.0000', '70', '299'
'2', '34.4000', '71', '258'
'2', '667.2000', '72', '200'
'2', '300.0000', '73', '85'
'2', '168.0000', '74', '92'
'2', '37.2000', '75', '542'
'2', '86.4000', '76', '367'
'2', '124.8000', '77', '236'
'3', '172.8000', '1', '344'
'3', '304.0000', '2', '187'
'3', '240.0000', '3', '130'
'3', '1100.0000', '4', '126'
'3', '204.0000', '5', '57'
'3', '120.0000', '6', '68'
'3', '360.0000', '7', '238'
'3', '400.0000', '8', '66'
'3', '582.0000', '9', '6'
'3', '595.2000', '10', '192'
'3', '168.0000', '11', '195'
'3', '364.8000', '12', '229'
'3', '48.0000', '13', '227'
'3', '167.4000', '14', '133'
'3', '248.0000', '15', '20'
'3', '486.5000', '16', '413'
'3', '249.6000', '17', '217'
'3', '600.0000', '18', '209'
'3', '131.4000', '19', '233'
'3', '1814.4000', '20', '28'
'3', '80.0000', '21', '393'
'3', '840.0000', '22', '86'
'3', '288.0000', '23', '118'
'3', '100.8000', '24', '331'
'3', '112.0000', '25', '27'
'3', '597.6000', '26', '292'
'3', '877.5000', '27', '100'
'3', '728.0000', '28', '232'
'3', '990.0000', '29', '227'
'3', '1242.0000', '30', '279'
'3', '150.0000', '31', '298'
'3', '320.0000', '32', '30'
'3', '40.0000', '33', '228'
'3', '224.0000', '34', '286'
'3', '1440.0000', '35', '286'
'3', '380.0000', '36', '246'
'3', '20.8000', '37', '89'
'3', '10540.0000', '38', '245'
'3', '86.4000', '39', '284'
'3', '882.0000', '40', '154'
'3', '192.5000', '41', '460'
'3', '98.0000', '42', '291'
'3', '1472.0000', '43', '141'
'3', '620.0000', '44', '184'
'3', '228.0000', '45', '108'
'3', '144.0000', '46', '69'
'3', '114.0000', '47', '132'
'3', '714.0000', '48', '78'
'3', '560.0000', '49', '131'
'3', '195.0000', '50', '109'
'3', '127.2000', '51', '287'
'3', '112.0000', '52', '156'
'3', '262.0000', '53', '200'
'3', '29.5000', '54', '280'
'3', '2304.0000', '55', '473'
'3', '60.8000', '56', '349'
'3', '312.0000', '57', '86'
'3', '848.0000', '58', '231'
'3', '1320.0000', '59', '362'
'3', '952.0000', '60', '362'
'3', '712.5000', '61', '205'
'3', '472.8000', '62', '437'
'3', '175.5000', '63', '35'
'3', '239.4000', '64', '149'
'3', '672.0000', '65', '266'
'3', '136.0000', '66', '34'
'3', '336.0000', '67', '24'
'3', '100.0000', '68', '256'
'3', '28.8000', '69', '177'
'3', '60.0000', '70', '264'
'3', '516.0000', '71', '473'
'3', '174.0000', '72', '206'
'3', '525.0000', '73', '65'
'3', '288.0000', '74', '135'
'3', '37.2000', '75', '123'
'3', '475.2000', '76', '266'
'3', '156.0000', '77', '300'发布于 2017-03-21 17:59:13
尝试如下所示:
SELECT *
FROM MyTable WHERE (storeID,Quantity) IN
( SELECT storeID, MAX(Quantity)
FROM MyTable
GROUP BY storeID
)发布于 2017-03-21 18:09:39
您编写的查询是不明确的。ProductID不是最大数量的产品。考虑一下这个查询:
SELECT StoreID, ProductID, MAX(Quantity), MIN(Quantity), TotalPrice
FROM MyTable
GROUP BY StoreID LIMIT 0, 99999;您期望这个查询返回哪个ProductID?最大数量的产品还是最小数量的产品?
或者这个:
SELECT StoreID, ProductID, AVG(Quantity), TotalPrice
FROM MyTable
GROUP BY StoreID LIMIT 0, 99999;也许没有一个产品的数量正好是平均数量。
这种类型的SQL查询在技术上是非法的,在MySQL以外的大多数数据库中都是一个错误。MySQL允许它,并且信任您在编写这样一个模糊的查询时知道。
提示:选择列表中的每一列必须是:
任何其他列都不会返回可靠值。
您在请求一个被称为最大n次群问题的查询。这个问题经常在堆栈溢出时出现,并且已经被多次回答了。以下是我过去的几个答案,得到了很多人的支持:
发布于 2017-03-23 19:37:33
它匹配,因为您是按StoreID分组的。MAX是一个函数组,因此它返回每个StoreID的最大(数量)。
对于ProductID和TotalPrice,查询只是返回每个存储区第一行的值,因为没有什么可以告诉它从Quantity=MAX(Quantity)所在的行读取值。
另一方面,U已知的查询首先为每个商店找到最大值(数量),然后执行第二个查询,从Quantity=MAX(Quantity)行返回所有字段
https://stackoverflow.com/questions/42934343
复制相似问题