skomplikowane zapytanie Opcje

[Anna]

Zapytanie powinno zwracać:
- dane wszystkich userów users_data.*
- sumę punktów zdobytych przez usera w danym teście SUM(users_asks.point)+SUM(answers.point), lub NULL jeśli user testu nie wypełniał
- maksymalną sumę punktów jaką user mógł zdobyć w danym teście

Macie jakieś pomysły? (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

Kombinuję w ten sposób:

[SQL] pobierz, plaintext 
SELECT users_data. * , ask.suma + ans.suma, ask.max + ans.max
FROM users_data LEFT JOIN (
SELECT SUM( users_asks.point ) AS suma, COUNT( asks.ask_id ) AS max, users_asks.user_id
FROM users_asks, asks
WHERE users_asks.ask_id = asks.ask_id
GROUP BY users_asks.user_id, asks.test_id
) AS ask
USING ( user_id )
LEFT JOIN (
 
SELECT SUM( answers.point ) AS suma, COUNT( asks.ask_id ) AS max, users_answers.user_id
FROM users_answers, answers, asks
WHERE users_answers.answer_id = answers.answer_id AND answers.ask_id = asks.ask_id
GROUP BY users_answers.user_id, asks.test_id
) AS ans
USING ( user_id )
[SQL] pobierz, plaintext 

(IMG:http://forum.php.pl/style_emoticons/default/questionmark.gif) (IMG:http://forum.php.pl/style_emoticons/default/questionmark.gif)

Ten post edytował Anna 16.03.2007, 17:38:56

[DeyV]

podaj, proszę, uproszczoną strukturę tych tabel, wraz z przykładowymi danymi i oczekiwanymi wynikami.

[Anna]

(IMG:http://bobola.net.pl/test3.png)

[SQL] pobierz, plaintext 
-- 
-- Zrzut danych tabeli `answers`
-- 
INSERT INTO `answers` VALUES (1, 2, 'Odpowiedź dobra', 1, 1);
INSERT INTO `answers` VALUES (2, 2, 'Odpowiedź zła', 0, 1);
INSERT INTO `answers` VALUES (3, 3, 'Dobra odpowiedź do pytania I w teście II', 1, 1);
INSERT INTO `answers` VALUES (4, 3, 'Zła odpowiedź do pytania I w teście II', 0, 1);
-- --------------------------------------------------------
-- 
-- Zrzut danych tabeli `asks`
-- 
INSERT INTO `asks` VALUES (1, 1, 'Pytanie pierwsze w teście, na które musisz sam odpowiedzieć', 1);
INSERT INTO `asks` VALUES (2, 1, 'Pytanie drugie', 1);
INSERT INTO `asks` VALUES (3, 2, 'Pytanie I do testu II', 1);
INSERT INTO `asks` VALUES (4, 2, 'Pytanie II do testu II', 1);
-- --------------------------------------------------------
-- 
-- Zrzut danych tabeli `tests`
-- 
INSERT INTO `tests` VALUES (1, 'test nr 1', 60, 1);
INSERT INTO `tests` VALUES (2, 'Test nr 2', 60, 1);
-- --------------------------------------------------------
-- 
-- Zrzut danych tabeli `users_answers`
-- 
INSERT INTO `users_answers` VALUES (1, 1);
INSERT INTO `users_answers` VALUES (3, 1);
-- --------------------------------------------------------
-- 
-- Zrzut danych tabeli `users_asks`
-- 
INSERT INTO `users_asks` VALUES (1, 1, 'Moja wyczerpująca odpowiedź', 1);
-- --------------------------------------------------------
-- 
-- Zrzut danych tabeli `users_data`
-- 
INSERT INTO `users_data` VALUES (1, 'Tester', 'Testowicz', NULL, NULL);
INSERT INTO `users_data` VALUES (2, 'Marek', 'Konrad', marek@konrad.pl, NULL);
[SQL] pobierz, plaintext 

oczekiwane wyniki:

--------------------------------------------------------------------------
users_data.* |suma |max
--------------------------------------------------------------------------
1 | Tester | Testowicz | NULL | NULL | 2 | 2
1 | Tester | Testowicz | NULL | NULL | 1 | 2
2 | Marek | Konrad | marek@konrad.pl | NULL | NULL | NULL
--------------------------------------------------------------------------

Można też podejść do tematu inaczej i do testu dołączyć usera z wynikami:

--------------------------------------------------------------------------------------------
test.name | users_data.firstname | users_data.surname | suma | max
--------------------------------------------------------------------------------------------
test nr 1 | Tester | Testowicz | 2 | 2
test nr 2 | Tester | Testowicz | 1 | 2

--------------------------------------------------------------------------------------------

Ten post edytował Anna 16.03.2007, 17:43:45

[DeyV]

Aniu - jesteś dla mnie niedobra. Przez Ciebie będę musiał sobie ręcznie utworzyć te tabelki...

[Anna]

Ależ proszę...

[SQL] pobierz, plaintext 
-- 
-- Struktura tabeli dla  `answers`
-- 
CREATE TABLE `answers` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `ask_id` int(11) NOT NULL,
  `answer` text,
  `point` tinyint(4) DEFAULT NULL,
  `enable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`answer_id`),
  KEY `answers_FKIndex1` (`ask_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `asks`
-- 
CREATE TABLE `asks` (
  `ask_id` int(11) NOT NULL AUTO_INCREMENT,
  `test_id` int(11) NOT NULL,
  `ask` text,
  `enable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`ask_id`),
  KEY `asks_FKIndex1` (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `tests`
-- 
CREATE TABLE `tests` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `timeout` int(11) DEFAULT NULL,
  `enable` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users`
-- 
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(45) DEFAULT NULL,
  `passwd` varchar(45) DEFAULT NULL,
  `auth` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users_answers`
-- 
CREATE TABLE `users_answers` (
  `answer_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`answer_id`,`user_id`),
  KEY `users_answers_FKIndex1` (`user_id`),
  KEY `users_answers_FKIndex2` (`answer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users_asks`
-- 
CREATE TABLE `users_asks` (
  `user_id` int(11) NOT NULL,
  `ask_id` int(11) NOT NULL,
  `answer` text,
  `point` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`user_id`,`ask_id`),
  KEY `users_asks_FKIndex1` (`user_id`),
  KEY `users_asks_FKIndex2` (`ask_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
-- 
-- Struktura tabeli dla  `users_data`
-- 
CREATE TABLE `users_data` (
  `user_id` int(11) NOT NULL,
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  PRIMARY KEY  (`user_id`),
  KEY `users_data_FKIndex1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

Myślałam, że uda się coś "na sucho" wykombinować (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[DeyV]

na sucho nieco niezdrowo - czasem dziwne wyniki wychodzą (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Z tej struktury nie rozumiem jeszcze tylko jednej rzeczy.

W tabeli users_asks pojawiają się point. Jak te punkty mają się do maksymalnej ilości punktów, którą user może zdobyć w danym teście, którą przecież wyliczamy w oparciu o odpowiedzi danego usera zapisane w tabeli users_answers?

[Anna]

Co do struktury - nie wiem czy jest optymalna, ale póki co, nie widzę tego inaczej.

users_answers - odpowiedzi wybrane przez usera z answers (checkbox);
users_asks - odpowiedzi usera, na pytania, które nie mają gotowej odpowiedzi do wyboru (textarea)

Za maksymalną liczbę punktów można chyba potraktować count(asks.ask_id).

[DeyV]

Ok. Wyszło mi z tego coś takiego.

[SQL] pobierz, plaintext 
SELECT u_d.user_id,
 
t.test_id,
 
SUM( an.point ) AS MAKS,
 
( SELECT SUM( users_asks.point ) FROM users_asks LEFT JOIN asks ON (asks.ask_id= users_asks.ask_id  ) 
	WHERE users_asks.user_id = u_d.user_id AND asks.test_id = t.test_id )  AS users_asks_point,
 
( SELECT SUM( answers.point ) FROM users_answers INNER JOIN answers ON ( answers.answer_id = users_answers.answer_id  )
	INNER JOIN asks  ON ( asks.ask_id = answers.ask_id AND asks.test_id = t.test_id )
	WHERE users_answers.user_id = u_d.user_id 
 ) AS users_answers_point
 
FROM tests t
LEFT JOIN users_data u_d ON ( TRUE )
 
LEFT JOIN asks  ON ( asks.test_id = t.test_id )
LEFT JOIN answers an ON ( an.ask_id= asks.ask_id )
 
GROUP BY u_d.user_id,  t.test_id
[SQL] pobierz, plaintext 

Jedna uwaga. U siebie mam tylko MySQL 4, który nie pozwala na SubQuery, więc musiałem przetestować to zapytanie na bazie Postgres. Niestety - nie wiem, co tak naprawdę w tej chwili potrafi MySQL 5 - ponoć dużo, więc zapytanie tego typu powinno chodzić poprawnie. Jednak głowy nie dam (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)