Help with SQL Query, is this possible?
I'm developing a system to process recurring billing for members. Items to be purchased can be grouped together for a special package rate, or purchased individually for a higher, stand-alone rate. The portion of my database schema that determines the amount to be paid for recurring items consists of the following 4 tables:
MEMBER_RECUR_GROUP
member_id (PK)
recur_group_id (PK)
RECUR_GROUP
recur_group_id (PK)
RECUR_GROUP_ITEM
recur_group_id (PK)
recur_item_id (PK)
recur_amount
RECUR_ITEM
recur_item_id (PK)
item_code
recur_term
Basically, items are attached to recur groups and then associated with members. The same item can appear in multiple recur groups for a given member.
For example, let's say we have 3 simple items: apple, orange, and banana. There can be a recur group containing all 3 (individual item prices in this group would be the cheapest, say $1 each). There can also be a recur group for just 2 items, say orange and banana. And there can be a recur group for just apple (it's stand-alone price, say $2, would higher than it's "packaged" price within the other groups), another for just orange, and another for just banana.
Given a member_id and one or many item_codes (probably a CSV, something like "apple, banana"), I would like a query to return the recur_term and recur_amount for each item_code from the recur_group that contains all of the item_codes. If there is not a recur_group that contains all of the items, then the recur_term and recur_amount values from the stand-alone recur_group for the individual items should be returned. Is this possible to achieve in a single query?
I am using SQL Server 2005 if implementation matters. Thank you!
Here is the sample data, with expected result below.
MEMBER_RECUR_GROUP
1, 1
1, 3
1, 4
1, 5
RECUR_GROUP
1
2
3
4
5
RECUR_GROUP_ITEM
1, 1, 1.00
1, 2, 1.00
1, 3, 1.00
2, 2, 1.50
2, 3, 1.50
3, 1, 2.00
4, 2, 2.00
5, 3, 2.00
RECUR_ITEM
1, apple, 3
2, orange, 3
3, banana, 3
If member ID 1 is auto-billed for apple, orange, and banana, the pricing for recur group ID 1 is used.
If member ID 1 is auto-billed for apple and orange, the combined pricing for recur group ID 3 and 4 is used. (Because member ID 1 doesn't have a recur group with apple and orange.)
If member ID 1 is auto-billed for orange and banana, the pricing for recur group ID 2 is used.
If member ID 1 is auto-billed for apple, the pricing for recur group ID 3 is used.
If member ID 1 is auto-billed for orange, the pricing for recur group ID 4 is used.
If member ID 1 is auto-billed for banana, the pricing for recur group ID 5 is used.
This might get you going in the right direction. First thing, the biggest problem is that this computation spins widely out of control for even a moderate number of items and/or groups.
The query I'm putting in here was written for MySQL and I'm sure some of the stuff in there isn't available in SQL Server but you can adapt it, I'm sure. I had to jump through a lot of hoops specific to MySQL to get just the cheapest row for each combination of items. From what I understand, it is actually easier with MS SQL since it seems to support a FIRST aggregate function.
Also, you didn't specify if it makes sense in your situation for someone to get multiples of the same item. I assumed no, but if you removed the having clause it would include multiples.
The central issue is that you need to generate all the possible combinations of groups that might include all of the elements being purchased. To do that the recur_group table has to be joined with itself up to the number of items being purchased. In my example query, I include up to 4 self-joins which would cover all possible combinations of up to 4 items.
If the groups and prices don't change that frequently, you could store the query results into a table that gets repopulated whenever your data changes.
The bottom line, though, is that you probably need to simplify something unless you are only going to have a pretty small number of groups and items.
set @type = '';
set @num = 1;
select item_list, total, g1, g2, g3, g4,
@num := if(@type = item_list, @num + 1, 1) as row_number,
@type := item_list as dummy
from (
select group_concat(i.item_code order by i.item_code separator ',')
as item_list,
( select sum(recur_amount)
from recur_group_item
where recur_group_id in (
perms.g1, perms.g2, perms.g3, perms.g4
)) as total,
perms.g1, perms.g2, perms.g3, perms.g4
from recur_item i
join recur_group_item gi
on gi.recur_item_id = i.recur_item_id
join (
select recur_group_id as g1,
null as g2, null as g3, null as g4
from recur_group
union
select g1.recur_group_id as g1, g2.recur_group_id as g2,
null as g3, null as g4
from recur_group g1, recur_group g2
where g1.recur_group_id < g2.recur_group_id
union
select g1.recur_group_id as g1, g2.recur_group_id as g2,
g3.recur_group_id as g3, null as g4
from recur_group g1, recur_group g2, recur_group g3
where g1.recur_group_id < g2.recur_group_id
and g2.recur_group_id < g3.recur_group_id
union
select g1.recur_group_id as g1, g2.recur_group_id as g2,
g3.recur_group_id as g3, g4.recur_group_id as g4
from recur_group g1, recur_group g2, recur_group g3, recur_group g4
where g1.recur_group_id < g2.recur_group_id
and g2.recur_group_id < g3.recur_group_id
and g3.recur_group_id < g4.recur_group_id
) as perms
on gi.recur_group_id in (perms.g1, perms.g2, perms.g3, perms.g4)
group by perms.g1, perms.g2, perms.g3, perms.g4
having count(i.item_code) = count(distinct i.item_code)
order by item_list asc, total asc
) as groupings
group by item_list, total, g1, g2, g3, g4
having row_number = 1;
Which, with a slightly expanded dataset, produces results similar to this:
item_list total g1 g2 g3 g4
----------------------------- ----- -- ------ ------ ------
apple 2.0 3 (null) (null) (null)
apple,banana 4.0 3 5 (null) (null)
apple,banana,onion 4.0 5 9 (null) (null)
apple,banana,onion,orange 5.0 2 9 (null) (null)
apple,banana,onion,pear 7.0 5 6 9 (null)
apple,banana,onion,pineapple 8.0 5 7 9 (null)
apple,banana,orange 3.0 1 (null) (null) (null)
apple,banana,orange,pear 6.0 1 6 (null) (null)
apple,banana,orange,pineapple 7.0 3 5 10 (null)
apple,banana,pear 7.0 3 5 6 (null)
apple,banana,pear,pineapple 11.0 3 5 6 7
apple,banana,pineapple 8.0 3 5 7 (null)
apple,onion 2.0 9 (null) (null) (null)
apple,onion,orange 4.0 4 9 (null) (null)
apple,onion,orange,pear 7.0 4 6 9 (null)
apple,onion,orange,pineapple 5.0 9 10 (null) (null)
apple,onion,pear 5.0 6 9 (null) (null)
apple,onion,pear,pineapple 9.0 6 7 9 (null)
apple,onion,pineapple 6.0 7 9 (null) (null)
apple,orange 4.0 3 4 (null) (null)
apple,orange,pear 7.0 3 4 6 (null)
apple,orange,pear,pineapple 8.0 3 6 10 (null)
apple,orange,pineapple 5.0 3 10 (null) (null)
apple,pear 5.0 3 6 (null) (null)
apple,pear,pineapple 9.0 3 6 7 (null)
apple,pineapple 6.0 3 7 (null) (null)
banana 2.0 5 (null) (null) (null)
banana,onion 8.0 5 8 (null) (null)
banana,onion,orange 9.0 2 8 (null) (null)
banana,onion,orange,pear 12.0 2 6 8 (null)
banana,onion,orange,pineapple 11.0 5 8 10 (null)
banana,onion,pear 11.0 5 6 8 (null)
banana,onion,pear,pineapple 15.0 5 6 7 8
banana,onion,pineapple 12.0 5 7 8 (null)
banana,orange 3.0 2 (null) (null) (null)
banana,orange,pear 6.0 2 6 (null) (null)
banana,orange,pear,pineapple 8.0 5 6 10 (null)
banana,orange,pineapple 5.0 5 10 (null) (null)
banana,pear 5.0 5 6 (null) (null)
banana,pear,pineapple 9.0 5 6 7 (null)
banana,pineapple 6.0 5 7 (null) (null)
onion 6.0 8 (null) (null) (null)
onion,orange 8.0 4 8 (null) (null)
onion,orange,pear 11.0 4 6 8 (null)
onion,orange,pear,pineapple 12.0 6 8 10 (null)
onion,orange,pineapple 9.0 8 10 (null) (null)
onion,pear 9.0 6 8 (null) (null)
onion,pear,pineapple 13.0 6 7 8 (null)
onion,pineapple 10.0 7 8 (null) (null)
orange 2.0 4 (null) (null) (null)
orange,pear 5.0 4 6 (null) (null)
orange,pear,pineapple 6.0 6 10 (null) (null)
orange,pineapple 3.0 10 (null) (null) (null)
pear 3.0 6 (null) (null) (null)
pear,pineapple 7.0 6 7 (null) (null)
pineapple 4.0 7 (null) (null) (null)
下一篇: 帮助SQL查询，这可能吗？