Απ: βοηθεια με sql select

spaceman — Mon, 01 Nov 2010 22:52:30 GMT

--products table
DECLARE @tblProducts TABLE (PID SMALLINT, 
			Name VARCHAR(10), 
			Price MONEY)

INSERT INTO @tblProducts(PID, Name, Price)
VALUES (1, 't1', 1), (2, 't2', 11), (3, 't3', 22), (4, 't4', 33), (5, 't5', 44)

--shipments table
DECLARE @tblShipping TABLE(ShipID SMALLINT, 
			TypeID SMALLINT, 
			PID SMALLINT, 
			Price MONEY)
							
INSERT INTO @tblShipping(ShipID, TypeID, PID, Price)
VALUES (1, 1, 1, 12), (2, 2, 1, 14), (3, 4, 1, 15), (4, 1, 2, 11), (5, 3, 2, 11), (6, 4, 2, 11), 
(7, 1, 3, 11), (8, 2, 3, 11), (9, 3, 3, 11), (10, 1, 4, 12), (11, 5, 5, 13)




--table grouping products that should have common shipment types
DECLARE @prodgroups TABLE(PID SMALLINT, groupID CHAR(1))
INSERT INTO @prodgroups(PID, groupID) 
VALUES (1, 'a'), (2, 'a'), (4, 'a'), --get common shipment types for the combination of products: 1, 2, 4
		(2, 'b'),(3, 'b'), --get common shipment types for the combination of products: 2, 3
		(1, 'c'), (5, 'c') --get common shipment types for the combination of products: 1, 5


SELECT groupprods.groupID, groupshipments.TypeID
FROM
	(--get counter of services per group
	   SELECT groupID, COUNT(DISTINCT PID) AS groupprodcounter
	   FROM @prodgroups
	   GROUP BY groupID 
	)AS groupprods 
LEFT JOIN 			
	(--get counter of shipped products per group and shipment type
	   SELECT pgrp.groupID, shp.TypeID, COUNT(DISTINCT shp.PID) AS shipmentprodcounter
	   FROM @prodgroups AS pgrp
	      JOIN @tblShipping AS shp ON pgrp.PID = shp.PID
	   GROUP BY pgrp.groupID, shp.TypeID
	) AS groupshipments ON groupprods.groupID = groupshipments.groupID AND groupprods.groupprodcounter = groupshipments.shipmentprodcounter
ORDER BY groupprods.groupID

--HTH--

βοηθεια με sql select

DaltonGR — Wed, 27 Oct 2010 22:15:30 GMT

καλημέρα,
έχω 2 πίνακες,

tblProducts
PID
Name
Price

tblShipping
ShipID
TypeID
PID
Price

με τα εξής δεδομένα
tblProducts
1,2,3,4
t1,t2,t3,t4
1,11,22,33

tblShipping
1,2,3,4,5,6,7,8,9,10
1,2,4,1,3,4,1,2,3,1
1,1,1,2,2,2,3,3,3,4
12,14,15,11,11,11,11,11,11,12

με λίγα λόγια έχω κάποια προϊόντα που υποστηρίζουν κάπιοες μέθοδοι αποστολής. Τα προϊόντα δεν έχουν τον ίδιο τρόπο αποστολής αλλα έχουν μερικά κοινά.
Πώς μπορώ να πάρα τους κοινούς τρόπους αποστολής για τα προϊόντα που θέλω;

Ευχαριστώ εκ των προτέρων.

Πρώτα Βήματα - Βάσεις δεδομένων

Απ: βοηθεια με sql select

βοηθεια με sql select