如何在獲取記錄數和最小/最大值的 MySQL 查詢中 GROUP_CONCAT DISTINCT 值？

我正在執行 MySQL5.0.88 (Coldfusion8)

我有一個產品搜尋，我正在查詢number-of-results它以及min/max prices/rebates整個產品表。我還想包含一個字元串distinct sizes/colors，這樣我就可以在顯示結果的同時更新我的搜尋條件。

但是，我的 GROUP_CONCAT 不會返回所有預期值。我不知道我錯過了什麼，但它似乎只返回不同的min/max尺寸/顏色，而不是all在整個記錄集中返回不同的尺寸/顏色。

我的產品表：

CREATE TABLE dummy (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`iln` VARCHAR(13) NULL DEFAULT NULL,
`ean` VARCHAR(35) NULL DEFAULT NULL,
`artikelnummer` VARCHAR(35) NULL DEFAULT NULL,
`groesse` VARCHAR(10) NULL DEFAULT NULL,
`farbe` VARCHAR(35) NULL DEFAULT NULL,
`farbnummer` VARCHAR(10) NULL DEFAULT NULL,
`preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_aktuell` DECIMAL(12,2) NULL DEFAULT NULL,
`firma` VARCHAR(35) NULL DEFAULT NULL,
`marke` VARCHAR(35) NULL DEFAULT NULL,
`nos` VARCHAR(4) NULL DEFAULT NULL,
`nos_anzeige` VARCHAR(4) NULL DEFAULT NULL,
`aktiv` VARCHAR(4) NULL DEFAULT NULL,
`modus` VARCHAR(4) NULL DEFAULT NULL,
`bestand` DECIMAL(10,0) NULL DEFAULT '0'
   )

產品由 EAN 列出，例如尺寸 S、M、L、XL 的襯衫將有 4 個條目，如下所示：

 style      ean           size       price      qty
 123      111111111111    S          9.99       12
 123      111111111112    M          9.99        1
 123      111111111113    L          9.99       23
 123      111111111114    XL         9.99        0

這是我的查詢：

SELECT   COUNT(recordcount) AS total_records
       , MIN(min_price_ek) AS ek_min
       , MAX(max_price_ek) AS ek_max
       , MIN(min_price_vk) AS vk_min
       , MAX(max_price_vk) AS vk_max
       , MAX(max_reb) AS rb_max
       , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ), ',', 10  ) AS sz_rng
       , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT colorRange ), ',', 16  ) AS cl_rng 

   FROM (SELECT  a.id AS recordcount
               , a.nos
               , a.nos_anzeige
               , MAX(IFNULL(p.ek, a.preis_ek)) AS max_price_ek
               , MIN(IFNULL(p.ek, a.preis_ek)) AS min_price_ek
               , MAX(IFNULL(p.vk, a.preis_vk)) AS max_price_vk
               , MIN(IFNULL(p.vk, a.preis_vk)) AS min_price_vk
               , ROUND( MAX(  ( IFNULL(p.ek, a.preis_ek) - IFNULL(p.vk ,a.preis_aktuell) ) /  IFNULL(p.ek, a.preis_ek)  ),2) AS max_reb
               , a.groesse AS sizeRange
               , zu.systemfarbe AS colorRange 

               FROM artikelstammdaten a

               # currency join
               LEFT JOIN preislisten p ON 
                   p.iln = a.iln 
               AND p.ean = a.ean 
               AND ( (  p.preisliste = "Test" AND p.iln = "2222222222222" ) OR (1=0) )

               # base color join
               LEFT JOIN farbenzuordnung zu ON 
                   a.farbe = zu.farbe 

               WHERE a.aktiv = "ja"
               # include currency if applicable
               AND ( IF( a.iln IN ( 2222222222222), p.onlinepreis IS NOT NULL,1 ) )

               AND a.artikelnummer LIKE "%style_number%"


           GROUP BY a.iln, a.artikelnummer
           HAVING (( sum(a.bestand) != 0 ) OR (a.nos = "ja" AND a.nos_anzeige = "ja" ))
           ) AS temp 

我需要按賣家 ( a.iln) 和風格 ( a.artikelnummer) 分組。該having子句僅選擇具有庫存 ( sum(a.bestand) != 0) 或永不缺貨的產品（a.nos在產品和a.nos_anzeige功能上設置）。

我的問題是選擇在這些行中完成的不同尺寸/顏色：

 , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ), ',', 10  ) AS sz_rng
 , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT colorRange ), ',', 10  ) AS cl_rng
  ....
 , a.groesse AS sizeRange
 , zu.systemfarbe AS colorRange  

這不能正常工作。上面的文章只會返回s,xlvss,m,l,xl我不確定為什麼。我使用SUBSTRING_INDEX僅選擇前 10 種尺寸/顏色（前 10 種會更好），但我不確定這是否是問題所在。

問題：

語法中是否有問題導致並非所有不同的尺寸/顏色都返回？有沒有辦法只返回

好的。在玩了一段時間並記住@Shlomi Noach 評論之後，我想出了以下內容：

• 也按a.groesse和分組zu.systemfarbe
• 這會引發recordcount（似乎我現在不再按樣式編號（a.artikelnummer）
• 在子查詢中刪除COUNT(recordcount) AS total_records並使用COUNT(DISTINCT styles) AS total_recordswith a.artikelnummer AS styles。

所以我的查詢現在看起來像這樣：

SELECT 
       COUNT(DISTINCT styles) AS total_records
     , MIN(min_price_ek) AS ek_min
     , MAX(max_price_ek) AS ek_max
     , MIN(min_price_vk) AS vk_min
     , MAX(max_price_vk) AS vk_max
     , MAX(max_reb) AS rb_max
     , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ), ',', 10  ) AS sz_rng
     , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT colorRange ), ',', 16  ) AS cl_rng

     FROM (SELECT  
            a.artikelnummer AS styles
          , a.nos
          , a.nos_anzeige
          , MAX(IFNULL(p.ek, a.preis_ek)) AS max_price_ek
          , MIN(IFNULL(p.ek, a.preis_ek)) AS min_price_ek
          , MAX(IFNULL(p.vk, a.preis_vk)) AS max_price_vk
          , MIN(IFNULL(p.vk, a.preis_vk)) AS min_price_vk
          , ROUND( MAX(  ( IFNULL(p.ek, a.preis_ek) - IFNULL(p.vk ,a.preis_aktuell) ) /  IFNULL(p.ek, a.preis_ek)  ),2) AS max_reb
          , a.groesse AS sizeRange
          , zu.systemfarbe AS colorRange

          ...
           GROUP BY  a.iln, a.artikelnummera.groesse, zu.systemfarbe
           HAVING (( sum(a.bestand) != 0 ) OR (a.nos = "ja" AND a.nos_anzeige = "ja" ))
           ) AS temp

似乎工作和查詢處理時間也大致相同。如果這可以通過任何其他/更簡單的方式完成，我願意接受改進建議。

謝謝！

更新 現在我看到了你的錯誤。內部查詢使用聚合，並取出sizeRange不是您聚合的列。因此，您只會獲得該列的“樣本”。嚴格來說，您的查詢不是有效的 SQL，但 MySQL 允許它放寬sql_mode.

所以你的查詢本質上是錯誤的。會看看我是否可以幫助解決它。

原始答案

我的猜測是檢查group_concat_max_len.

預設情況下只是1024，儘管您通常並不真正想要限制。

問題可能是您只得到部分結果，其中，偶然或按評估順序，“S”和“XL”佔據前 1024 個字元或更多。我看不出“M”或“L”為什麼不存在的原因——它們GROUP_CONCAT沒有做這樣的區分。

所以，試試：

SET group_concat_max_len := 1000000;

並再次執行您的查詢。如果這可行，請確保在您的 MySQL 配置文件中設置所述參數。

您可能會發現我的相關文章很有用。

引用自：https://dba.stackexchange.com/questions/23431