SQL-fråga: Luriga Joins och Counts

Tråden skapades 2004-01-26 och har fått 2 svar. Det senaste inlägget skrevs 2004-01-26.

1

Skriv svar

2004-01-26 12:29

#1

capojava
Medlem ●
2004-01-26 12:29

Hej,

jag har problem med en SQL-fråga. Jag har två tabeller, users och events, som ser ut såhär:

mysql> DESCRIBE users;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| username      | varchar(20)      |      | PRI |         |                |
| password      | varchar(32)      |      |     |         |                |
| email         | varchar(40)      | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

mysql> describe events;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | int(10) unsigned      |      | PRI | NULL    | auto_increment |
| username    | varchar(20)           |      |     |         |                |
| timestamp   | timestamp(12)         | YES  |     | NULL    |                |
| description | text                  |      |     |         |                |
+-------------+-----------------------+------+-----+---------+----------------+

Jag vill ställa en fråga som returnerar en lista över alla användare samt hur många actions de har haft idag, i veckan och totat. Jag försökte göra detta såhär:

SELECT 
	u.username, 
	COUNT(DISTINCT e1.timestamp), 
	COUNT(DISTINCT e2.timestamp), 
	COUNT(DISTINCT e3.timestamp) 
FROM users AS u 
LEFT OUTER JOIN events AS e1 ON u.username=e1.username 
LEFT OUTER JOIN events AS e2 ON u.username=e2.username 
LEFT OUTER JOIN events AS e3 ON u.username=e3.username 
WHERE 
	(DATE_FORMAT(e1.timestamp, '%Y-%m-%d')=CURDATE() OR e1.timestamp IS NULL) AND 
	(YEARWEEK(e2.timestamp)=YEARWEEK(CURDATE()) OR e2.timestamp IS NULL) 
GROUP BY u.username

Problemet är att de användare som har events som inte är från idag eller denna veckan inte kommer med. Jag vill alltså att alla ska komma med, fast helt enkelt med rätt COUNT.

Några SQL-gurus som vet hur jag löser detta?

Anmäl Ändra Gilla Svara

2004-01-26 15:22

#2

jocked
Medlem ●
2004-01-26 15:22

Hej

Till och börja med så har du ett AND mellan dina två huvudvillkor vilket gör att bara events från idag och den här veckan kommer med.

Sedan borde det väl bli så att de events som har timestamp null räknas med dubbelt men det kanske är vad du vill.

Vad du borde pröva är att flytta upp where villkoren till respektive JOIN sats:

code:
SELECT 
        u.username, 
        COUNT(DISTINCT e1.timestamp), 
        COUNT(DISTINCT e2.timestamp), 
        COUNT(DISTINCT e3.timestamp) 
FROM users AS u 
LEFT OUTER JOIN events AS e1 ON (u.username=e1.username AND DATE_FORMAT(e1.timestamp, '%Y-%m-%d')=CURDATE())
LEFT OUTER JOIN events AS e2 ON u.username=e2.username AND 
        (YEARWEEK(e2.timestamp)=YEARWEEK(CURDATE())
LEFT OUTER JOIN events AS e3 ON u.username=e3.username AND 
        (YEARWEEK(e3.timestamp)<>YEARWEEK(CURDATE())

Observera att jag utelämnade NULL villkoren eftersom jag inte riktigt förstår hur de ska hanteras.

Joakim