复杂的MySQL计数使用连接

Basically, what's happening is there is a table which tracks checkins to businesses(BID) by username(UID). $startday = beginning of the day

Checkins:
 ID | UID | BID | DATE

Then there is a second table which tracks friends. User can be a SENDER or RECIEVER

Friends:
 SENDER | RECIEVER | STATUS

What I'm trying to do is count the number of checkins from any friends. So (pseudocode)

(if(SENDER = UID) AND (RECEIVER exists in checkins) AND (DATE > $startday)) OR
(if(RECEIVER = UID) AND (SENDER exists in checkins) AND (DATE > $startday))

Then count those results. I know I'll need to do a JOIN of some kind, but I really don't know where to start with this design. I'd appreciate any help...

try this

SELECT COUNT(1) FROM Checkins ch
JOIN Friends fr
ON (fr.SENDER = ch.UID OR fr.RECIEVER = ch.UID)
WHERE ch.DATE = '$startday'

Disclaimer: This is not pretty at all and can probably be done a lot neater, I make no claims to its performance, simply trying to give you a starting point, presuming I understand the question correctly.

Assumptions: I have assumed the following:

• Friends are defined by being in ANY relationship either SENDER or RECEIVER
• You want a count for a single individual of how many of their friends in total have checked in
• That you can add your own WHERE clause to dictate the date ranges to check

Answer: The below query worked for me

SELECT COUNT(*) FROM checkins
INNER JOIN 
(
    SELECT DISTINCT friend FROM 
    (
        SELECT receiver AS friend FROM Friends
            WHERE sender = 1
        UNION ALL
        SELECT sender AS friend FROM Friends
            WHERE receiver = 1
    ) AS t 
) AS allfriends
    ON checkins.uid = allfriends.friend

Example Data: I used is as follows

Checkins

ID          UID         BID         Date
----------- ----------- ----------- ----------
1           1           1           NULL
2           2           1           NULL
3           3           1           NULL
4           4           1           NULL
5           5           1           NULL
6           6           1           NULL
7           1           1           NULL
8           2           1           NULL
9           3           1           NULL
10          4           1           NULL

Friends

sender      receiver    status
----------- ----------- -----------
1           2           NULL
1           3           NULL
1           4           NULL
2           3           NULL
2           5           NULL
2           6           NULL
3           1           NULL
2           1           NULL

Explanation: I'll break it up so you can understand it easily (you can run each of these individually to see the results).

First I got a list of all the IDs of a users friends where they are the sender (for UID 1).

        SELECT receiver AS friend FROM Friends
            WHERE sender = 1

Result:

friend
-----------
2
3
4

Then again where they're the receiver.

        SELECT sender AS friend FROM Friends
            WHERE receiver = 1

Result:

friend
-----------
3
2

Then UNION ALL them together to get a list containing duplicates.

        SELECT receiver AS friend FROM Friends
            WHERE sender = 1
        UNION ALL
        SELECT sender AS friend FROM Friends
            WHERE receiver = 1

Result:

friend
-----------
2
3
4
3
2

Removed the duplicates doing a DISTINCT subselect

    SELECT DISTINCT friend FROM 
    (
        SELECT receiver AS friend FROM Friends
            WHERE sender = 1
        UNION ALL
        SELECT sender AS friend FROM Friends
            WHERE receiver = 1
    ) AS t 

Result:

friend
-----------
2
3
4

Finally I joined the checkins table to the list of distinct friends, based on the userid using an INNER JOIN. INNER JOIN will pull all results from checkins where the uid also exists in our subselect allfriends. Adding COUNT(*) gives you a count of all the rows.

Result of full query:

6

You can then wrap your date selection around this, using a WHERE, to only get the results BETWEEN two dates as required.