Defining views in mysql from complex queries - Try #2
Hope this format is better... thanks to OMG ponies, Peter Lang, and astander for their patience in answering my first attempt.
I'm setting up a game in which each USER selects one CONTESTANT every WEEK, kind of like picking which football team you think will win. I need to create a view that has, for each combination of USER, CONTESTANT, and WEEK, how many times that USER has picked that particular CONTESTANT cumulatively up through that week. The data tables look like this:
Data table "contestants"
contestant name
11 Aaron
12 Bob
13 Catherine
14 David
Data table "picks"
user week contestant
1001 1 11
1001 2 11
1002 1 12
1002 2 13
Now, I've got code that works and gives me the exact output that I want, but the problem is, I can't make a view out of it because there are subqueries. Here is the code and the correct output:
SELECT user, contestant ,week, count(valid_pick) num_picks
FROM
(
SELECT DISTINCT p.user , c.contestant, p.week
FROM contestants c , picks p
ORDER BY user , contestant , week
) t1
LEFT JOIN
(
SELECT user p_user , contestant p_contestant , week p_week ,
1 as valid_pick
FROM picks p
ORDER BY p.user , p_contestant , p_week
) t2
ON t1.user = t2.p_user AND t1.contestant = t2.p_contestant
AND t2.p_week <= t1.week
GROUP BY user , contestant , week
user contestant week num_picks
1001 11 1 1
1001 11 2 2
1001 12 1 0
1001 12 2 0
1001 13 1 0
1001 13 2 0
1001 14 1 0
1001 14 2 0
1002 11 1 0
1002 11 2 0
1002 12 1 1
1002 12 2 1
1002 13 1 0
1002 13 2 1
1002 14 1 0
1002 14 2 0
This has 0's in the right places and correctly counts the picks cumulatively by week.
But I really need this as a view, so I thought to just create the subqueries as mini-views. Here is the code:
CREATE OR REPLACE VIEW miniview1 AS
SELECT DISTINCT p.user , c.contestant, p.week
FROM contestants c , picks p
ORDER BY user , contestant , week
;
CREATE OR REPLACE VIEW miniview2 AS
SELECT user p_user , contestant p_contestant , week p_week ,
1 as valid_pick
FROM picks p
ORDER BY p.user , p_contestant , p_week
;
CREATE OR REPLACE VIEW myview AS
SELECT user, contestant ,week, count(valid_pick) num_picks
FROM miniview1 t1
LEFT JOIN miniview2 t2
ON t1.user = t2.p_user AND t1.contestan开发者_运维百科t = t2.p_contestant
AND t2.p_week <= t1.week
GROUP BY user , contestant , week
But what I get for "myview" is this:
user contestant week num_picks
1001 14 1 1
1002 14 1 1
1001 14 2 1
1002 14 2 1
1002 11 1 1
1002 11 2 1
1001 12 1 1
1001 12 2 1
1001 13 1 1
1002 13 1 1
1001 13 2 1
1001 11 1 1
1001 11 2 2
1002 12 1 1
1002 12 2 1
1002 13 2 1
Clearly this is wrong. It seemed like such a simple substitution. I've checked miniview1 and miniview2 and they are exactly correct. Any ideas what is happening here?
Thanks, and thanks for your patience with a mysql (and stackoverflow) neophyte!
doxguy
I think you want something like this:
Data table "contestants"
contestant name
11 Aaron
12 Bob
13 Catherine
14 David
Data table "weeks"
contestant week
11 1
11 2
12 1
12 2
Data table "user"
user name
1001 Fred
1002 Jane
Data table "picks"
user week contestant
1001 1 11
1001 2 11
1002 1 12
1002 2 13
select p.user, w.contestant, w.week,
(select count(*)
from picks p
where p.week = w.week
and p.contestant = w.contestant)
from user u
join weeks w
join contestants c on c.contestant = w.contestant
Note that this code is untested.
I have left the names short and the database demornalised for clarity. The implementation could be tidied up a bit.
miniview1:
CREATE OR REPLACE VIEW miniview1 AS SELECT p.user , c.contestant, p.week FROM contestants c , picks p GROUP BY user , contestant , week;
miniview2:
SELECT user p_user , contestant p_contestant , week p_week , 1 as valid_pick FROM picks p;
now lets try a SELECT using miniview1 and miniview2
SELECT user, contestant ,week, count(valid_pick) num_picks
FROM
( SELECT * from miniview1 ) t1
LEFT JOIN
( SELECT * from miniview2 ) t2
ON t1.user = t2.p_user AND t1.contestant = t2.p_contestant
AND t2.p_week <= t1.week
GROUP BY user , contestant , week;
this gives the same correct results as in your example. So it seems that is a problem with myview, not in miniview1 or miniview2.
I think that's because you cannot write the SELECT above as a VIEW. the SELECT uses subqueries, while the VIEW cannot use subqueries.
In other words: the SELECT statement above simply doesn't have the same semantics as the VIEW myview.
doxguy, you wrote:
... I've checked miniview1 and miniview2 and they are exactly correct ...
what would you expect as a correct result from miniview1?
When I tried your definition of miniview1 then I got results which are very obviously wrong to me:
select * from miniview1 user contestant week 1001 11 1 ... 1002 13 1 1002 13 2 1002 14 1 1002 14 2
but - for example - there is no contest with contestant #14 at all.
EDIT: I know that this doesn't answer your question. It is merely something I found strange.
精彩评论