如何选择MySQL子查询中的所有不同值为group_concat？

I'm running MySQL 5.0.88 and have a search which I'm running a presearch to determine number of records,. min-max values as well as element strings of distinct values.

我正在运行MySQL 5.0.88并进行搜索，我正在运行预搜索以确定记录数量。 min-max值以及不同值的元素字符串。

A result of the query will look like this:

查询结果如下所示：

  records      min-price    max-price   sizeRange   colorRange
  1234         9.00         124.00      S,M,L,XL    red,blue,white,orange

My query looks like this:

我的查询如下所示：

SELECT    COUNT(recordcount) AS total_records
        , MIN(min_price_ek) AS ek_min
        , MAX(max_price_ek) AS ek_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(<cfif variables.preislisten neq "">IFNULL(p.ek, a.preis_ek)<cfelse>a.preis_ek</cfif>) AS max_price_ek
                    , MIN(<cfif variables.preislisten neq "">IFNULL(p.ek, a.preis_ek)<cfelse>a.preis_ek</cfif>) AS min_price_ek
                    , a.groesse AS sizeRange
                    , zu.systemfarbe AS colorRange

              FROM artikelstammdaten a

              LEFT JOIN farbenzuordnung zu 
                ON a.farbe = zu.farbe 

              WHERE a.aktiv = "ja"
          AND a.artikelnummer LIKE <cfqueryparam value="#art#" cfsqltype="cf_sql_varchar">

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

        ) AS temp

This works ok, but I'm still having trouble with selecting all available sizes/colors. I'm getting some values, but not all.

这可以，但我仍然无法选择所有可用的尺寸/颜色。我得到了一些价值，但不是全部。

My table looks like this:

我的表看起来像这样：

  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,
 `preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
 `preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
 `firma` VARCHAR(35) NULL DEFAULT NULL,
 `nos` VARCHAR(4) NULL DEFAULT NULL,
 `nos_anzeige` VARCHAR(4) NULL DEFAULT NULL,
 `aktiv` VARCHAR(4) NULL DEFAULT NULL,
 `bestand` DECIMAL(10,0) NULL DEFAULT '0'
  )

So an product will be stored in the table with one entry per size like so:

因此，产品将存储在表中，每个大小一个条目，如下所示：

 product_id      ean           size     price
 1234          111111111111    S        9.99
 1234          111111111112    M        9.99
 1234          111111111113...

From what I'm getting back in MySQL, I think I'm only selecting the first size (S) of articles and create a GROUP_CONCAT of all DISTINCT first sizes vs a GROUP_CONCAT of all DISTINCT sizes of the recordset`.

从我在MySQL中获得的内容来看，我认为我只选择文章的第一个尺寸（S）并创建所有DISTINCT第一尺寸的GROUP_CONCAT与记录集的所有DISTINCT尺寸的GROUP_CONCAT。

Question: Can someone give me a pointer on how I need to tweak my GROUP_CONCAT?

问题：有人可以给我一个关于如何调整GROUP_CONCAT的指针吗？

Thanks!

谢谢！

This is the query I'm currently testing with

这是我正在测试的查询

SELECT    COUNT(recordcount) AS total_records
        , MIN(min_price_ek) AS ek_min
        , MAX(max_price_ek) AS ek_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(a.preis_ek) AS max_price_ek
                    , MIN(a.preis_ek) AS min_price_ek
                    , a.groesse AS sizeRange
                    , zu.systemfarbe AS colorRange

                FROM artikelstammdaten a

                LEFT JOIN farbenzuordnung zu 
                    ON a.farbe = zu.farbe 
                WHERE a.aktiv = "ja"
                AND a.artikelnummer LIKE "%402%"

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

        ) AS temp

I have removed the 3rd table (currency lookup/leftjoin), because results are false with or without it. This is the remaining two tables:

我已经删除了第3个表（货币查询/ leftjoin），因为无论是否有结果都是假的。这是剩下的两个表：

   ** artikelstammdaten = product data **
   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,
    `preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
    `preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
    `preis_aktuell` DECIMAL(12,2) 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'
 )

   ** global colors / farbenzuordnung **
   CREATE TABLE dummy (
    `ILN` VARCHAR(13) NOT NULL,
    `farbe` VARCHAR(35) NOT NULL,
    `systemfarbe` VARCHAR(35) NOT NULL,
    `systemfarbe_en` VARCHAR(35) NOT NULL
   )

I'm testing with a single product right now (402). The product has 4 sizes and two colors, so this is 8 records in the database. Running the query on it should return s,m,l,xl for sizes and red, black for colors as Group__concat. However I'm only getting (arbitraty?) s and red as results.

我现在正在使用单一产品进行测试（402）。该产品有4种尺寸和两种颜色，因此这是数据库中的8条记录。在它上面运行查询应返回s，m，l，xl表示大小，红色表示黑色，黑色表示Group__concat。但是我只得到（任意？）s和红色作为结果。

Still clueless as to why.

仍然无能为力的原因。

1 个解决方案

#1

You can use query as follows using GROUP_CONCAT along with ORDER BY CLAUSE,

您可以使用GROUP_CONCAT和ORDER BY CLAUSE按如下方式使用查询，

Note: you need to define the criteria in following query based on your requirements

注意：您需要根据您的要求在以下查询中定义条件

SELECT    COUNT(recordcount) AS total_records
        , MIN(min_price_ek) AS ek_min
        , MAX(max_price_ek) AS ek_max
        , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ORDER BY [criteria]), ',', 20  ) AS sz_rng
        , SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT colorRange ORDER BY [criteria]), ',', 20  ) AS cl_rng 

        FROM (SELECT  a.id AS recordcount
                    , a.nos
                    , a.nos_anzeige
                    , MAX(<cfif variables.preislisten neq "">IFNULL(p.ek, a.preis_ek)<cfelse>a.preis_ek</cfif>) AS max_price_ek
                    , MIN(<cfif variables.preislisten neq "">IFNULL(p.ek, a.preis_ek)<cfelse>a.preis_ek</cfif>) AS min_price_ek
                    , a.groesse AS sizeRange
                    , zu.systemfarbe AS colorRange

              FROM artikelstammdaten a

              LEFT JOIN farbenzuordnung zu 
                ON a.farbe = zu.farbe 

              WHERE a.aktiv = "ja"
          AND a.artikelnummer LIKE <cfqueryparam value="#art#" cfsqltype="cf_sql_varchar">

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

        ) AS temp

Hope it helps...

希望能帮助到你...

#1