Quick SQL question
I have 3 tables:
User (PK userid, ...)
Computer (PK computerid, FK userid, FK cpuid, ...)
CPU (PK cpuid, ...)
So, a user can have multiple computers, and each computer has exactly one CPU. Same cpu can appear in different computers.
I would like to get the number of computers and unique CPUs per user.
userid | Number of computers | Number of CPUs
---------------------------------------------
1 | 3 | 1 <- has 3 comps all with the same 开发者_Go百科cpu
2 | 13 | 4 <- has 13 comps with 4 different cpus
...and so on
I dont have the problem with counting computers, but I stuck on counting the CPUs. Joining tables causes bad results because if the same cpu appears in more then one computer - count() returns total number of records as if they were not grouped...
You don't need a join, because you can count the IDs instead of using COUNT(*)
. In this case, your query would be:
SELECT userid, COUNT(DISTINCT computerid) AS Computers, COUNT(DISTINCT cpuid) AS CPUs
FROM Computer
GROUP BY userid
This is the most logical solution...
SELECT userid,
(SELECT COUNT(*)
FROM Computer
WHERE userid = User.userid
) num_computers,
(SELECT COUNT(*)
FROM CPU
WHERE EXISTS (
SELECT *
FROM Computer
WHERE cpuid = CPU.cpuid
AND userid = User.userid
)
) AS num_cpus
FROM User
...but it is quite messy in SQL (and also possibly quite slow). The following is a more SQL-friendly arrangement:
SELECT userid,
(SELECT COUNT(*)
FROM Computer
WHERE userid = User.userid
) num_computers,
(SELECT COUNT(DISTINCT cpuid)
FROM CPU
JOIN Computer USING(cpuid)
WHERE userid = User.userid
) AS num_cpus
FROM User
This will include users with or without computers and handles distinct CPU counting.
I replicated your SQL Tables as such
Schemas
Users
ID - Int (PK)
Name - Nvarchar(50)
Table CPUs
CPUID - Int (PK)
Name - Nvarchar(50)
Table Computers
CompID - Int(PK)
CPUID - Int(FK)
UserID - Int(FK)
Name - Nvarchar(50)
Data in Tables
Users
ID Name
1 Tommy
2 Steve
3 Jeff
Computers
ID UserID CPUID Name
1 1 1 Dell 1
2 1 1 Dell 2
3 1 1 Dell 3
4 2 3 Dell 4
5 2 3 Dell 5
6 2 4 Dell 6
CPUs
CPUID Name
1 Intel 1
2 Intel 2
3 AMD 1
4 AMD 2
Query
SELECT COUNT(DISTINCT Computers.CPUID) AS CPUs, COUNT(Computers.ComputerID)
AS numComputers, Users.Name
FROM Computers RIGHT OUTER JOIN
Users ON Computers.UserID = Users.UserID
GROUP BY Users.Name
Results
CPUs numComputers Name
0 0 Jeff
2 3 Steve
1 3 Tommy
this should cover all cases.. (even users without computers..)
SELECT
User.UserId,
COUNT(Computer.ComputerId) AS [Computer #],
COUNT(DISTINCT Computer.CpuId) AS [CPU #]
FROM
User
LEFT OUTER JOIN
Computer ON Computer.UserId = User.UserId
GROUP BY
User.UserId
Try
select
[user].userid,
COUNT(computerid) AS Computers,
COUNT(distinct CpuID) AS CpuModels
from [user] left outer join computer on [user].userid=computer.userid
group by [user].userid
LEFT OUTER JOIN will return all Users but only matching computers. If a user has no computer it will return NULL values for the fields that come from Computer (ComputerID,CpuID). COUNT() doesn't count NULLs so you get 0 Computers from users without computers. You need DISTINCT CpuID to count distinct values of CpuID instead of all instances.
Your tables are not normalised. You should make a associative entity between CPU and computer. Call it something like CompCpu with the pk of computer and cpu in it.
If you do that, you can inner join stuff easily. It should look roughly like....
SELECT u.userid, COUNT(c.computerid), COUNT(cpu.cpuid)
FROM User u
INNER JOIN Computer c ON u.userid = c.userid
INNER JOIN CompCpu cc ON c.computerid = cc.computerid
INNER JOIN CPU cpu ON cpu.cpuid = cc.cpuid
GROUP BY u.userid
精彩评论