✔ [MySQL] Vorgehensweise Kombinationen durchrechnen

SELECT SUM( `Total` )

SELECT
	GROUP_CONCAT( CONCAT('vt_', o.item, '.vendor AS v_', o.item)),
	GROUP_CONCAT( CONCAT('b_vendor AS vt_', o.item)) 
	INTO @sel, @from1
FROM
  b_order o;
-- Mögliche Kobinationen zu einer ID auswerten
SET @sql = CONCAT('SELECT @id := @id+1 AS id, ', @sel, ' FROM (SELECT @id:=0) AS vars, ', @from1);

-- Diese wieder mittels UNION normalisieren
SELECT
	GROUP_CONCAT( CONCAT('SELECT id, \'', o.item, '\' AS item, v_', o.item, ' AS vendor FROM (', @sql, ') AS t') SEPARATOR ' UNION ALL ')
	INTO @sql
FROM
  b_order o;

-- Führt beim ersten Mal zu einem Fehler, dann temporär den DROP auskommentieren
DROP TABLE b_groups;
SET @sql = CONCAT('CREATE TABLE b_groups AS ', @sql, ' ORDER BY id, item');

-- SELECT @sql AS t;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT
	g.*,
	iv.price_per_unit,
	o.qty,
	iv.price_per_unit * o.qty AS total,
	v.shipping_coasts,
	v.min_buy
FROM
	(
		-- Nur die Auswählen die auch leifern können
		SELECT
			g1.id
		FROM
			b_groups g1
			LEFT JOIN b_item_by_vendor v1
				ON v1.item = g1.item
				AND v1.vendor = g1.vendor
		WHERE
			NOT v1.item IS NULL
		GROUP BY
			g1.id
		HAVING
			COUNT(*) = 3
	) ids
	INNER JOIN b_groups g
		ON ids.id = g.id
	INNER JOIN b_item_by_vendor iv
		ON iv.item = g.item
		AND iv.vendor = g.vendor
	INNER JOIN b_order o
		ON o.item = g.item
	INNER JOIN b_vendor AS v
		ON v.vendor = g.vendor;

SELECT
	d1.*
FROM
	b_detail d1
	INNER JOIN (
			SELECT
				id
			FROM
				(
					SELECT
						d.id,
						d.vendor,
						MAX(d.shipping_coasts) AS shipping_coasts,
						SUM(d.total)	AS total_price,
						SUM(d.qty)		AS qty,
						MAX(d.min_buy) AS min_buy,
						MAX(d.shipping_coasts) + SUM(d.total)	AS total_vendor,
						-- Prüfen ob die Gesammtmenge nicht unter den Anforderungen des Verkäufers leigen
						(SUM(d.qty) > MAX(d.min_buy)) AS valid
					FROM
						b_detail d
					GROUP BY
						d.id,
						d.vendor
				) AS per_v
			GROUP BY
				id
			HAVING
				-- Alle bei denen bei einem Verkäufer die Mindestmenge durchgefallen ist ignorieren
				MIN(valid) != FALSE
			-- Und den kleinsten auswählen
			ORDER BY
				SUM(total_vendor)
			LIMIT 1 	
		) AS m
	ON m.id = d1.id
;

id ; item  ; vendor ; price_per_unit ; qty ; total ; shipping_coasts ; min_buy
17 ; item1 ; shopB  ; 3              ; 5   ; 15    ; 6               ; 0
17 ; item2 ; shopC  ; 2              ; 11  ; 22    ; 5               ; 3
17 ; item3 ; shopB  ; 2              ; 1   ; 2     ; 6               ; 0

1 row(s) affected, 1 warning(s): 1260 Row 3 was cut by GROUP_CONCAT()