Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

count - SQL query - address referenced 2 different ways

If the address data was stored as shown below, what is the best way to do a query to check if a user belongs in a particular country? There is a lot of other conditions needed, so I've provided this format for simplicity.

Is doing a union the most efficient way in this situation?

Tables

User

| id |name|primaryaddrid (FK to Address id)|
|----|----|--------------------------------|
|1   |bill|1                               |

UserAddresses

|id|userid|addrid   |
|--|------|---------|
|1 |1     |2        |
|2 |1     |3        |

Address

|id|addrline1|country|
|--|---------|-------|
|1 |1 main st|usa    |
|2 |1 main st|italy  |
|3 |1 main st|peru   |

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

do a query to check how many users are in usa

You can count how many distinct userid in the useraddresses table have (at least) an address in the US with count(distinct) and a correlated subquery:

select count(distinct userid) as cnt
from useraddresses ua
where exists (select 1 from address a where a.id = ua.addrid and a.country = 'usa')

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...