Witam,
potrzebuje pomocy w dokończeniu zapytania bo nie wiem jak mam to wykonać a próby kończą się nie powodzeniem.
W ten sposób pobieram elementy do zapytania:


  1. SELECT GROUP_CONCAT(DISTINCT
  2. CONCAT('\nCOUNT(CASE WHEN c.MSH_PRICES_DATE= "',
  3. c.MSH_PRICES_DATE, '" THEN id ELSE NULL END) AS
  4. ', c.MSH_PRICES_DATE)) AS column_list
  5. FROM MSH_PRICES c;


zwraca mi to przykladowo:

  1. COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN id ELSE NULL END) AS
  2. 2009-08-16,
  3. COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN id ELSE NULL END) AS
  4. 2009-08-17,
  5. COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN id ELSE NULL END) AS
  6. 2009-08-18,
  7. COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN id ELSE NULL END) AS
  8. 2009-08-19,
  9. COUNT(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN id ELSE NULL END) AS
  10. 2009-08-20


chce to wstawic (pierwszy kod) w zapytanie:

  1. SELECT o.MSH_CLASSES_NAME
  2.  
  3. { column_list = kod pierwszy a raczej jego wyniki}
  4.  
  5. FROM MSH_CLASSES o
  6. WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID
  7. GROUP BY o.MSH_CLASSES_NAME


tak się nie da (próbowałem) bo drugi sql zwraca mi bloba:

  1. SELECT (
  2. SELECT o.MSH_CLASSES_NAME
  3. FROM MSH_CLASSES o
  4. WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID
  5. ) AS CLASSES,(
  6. SELECT GROUP_CONCAT(DISTINCT CONCAT(
  7. 'MAX(CASE WHEN c.MSH_PRICES_DATE =
  8. "',c.MSH_PRICES_DATE,'"
  9. THEN c.MSH_PRICES_PRICE ELSE NULL END) AS
  10. "',c.MSH_PRICES_DATE,'"'))
  11. )
  12. FROM MSH_PRICES c
  13. GROUP BY c.MSH_PRICES_MSH_CLASSES_ID
  14. LIMIT 0, 30;



koncowy sql musi tak wygladac tylko nie wiem jak za pomoca jednego zapytania do niego dojść (skopiowalem to co wyszło z pierwszego do drugiego sql'a i jest okej)

  1. SELECT (SELECT o.MSH_CLASSES_NAME FROM MSH_CLASSES o WHERE o.MSH_CLASSES_ID = c.MSH_PRICES_MSH_CLASSES_ID) AS CLASSES,
  2. MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-16" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-16",
  3. MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-17" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-17",
  4. MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-18" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-18",
  5. MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-19" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-19",
  6. MAX(CASE WHEN c.MSH_PRICES_DATE= "2009-08-20" THEN c.MSH_PRICES_PRICE ELSE NULL END) AS "2009-08-20"
  7. FROM MSH_PRICES c
  8. GROUP BY c.MSH_PRICES_MSH_CLASSES_ID
  9. LIMIT 0, 30;


moze ktoś wie jak to zrobić bo moja wiedza tak daleko nie sięga.
będę wdzięczny.