我有两张桌子-
tbl_business
------------------------
| id | name | lat |long|
|----|------|-----|----|
| 1 | aaaa |12.45|6.88|
|----|------|-----|----|
| 2 | bbbb |12.34|6.45|
|----|------|-----|----|
| 3 | cccc |12.12|6.50|
|----|------|-----|----|和
tbl_deals
-------------------
| id | deal | bid |
|----|------|-----|
| 1 | xxxx | 1 |
|----|------|-----|
| 2 | yyyy | 1 |
|----|------|-----|
| 3 | zzzz | 2 |
|----|------|-----|现在,我想要找到12.44, 6.66周围1公里范围内的所有企业,以及交易数量。例如。
| bid |dcount|
|-----|------|
| 1 | 2 |
|-----|------|
| 2 | 1 |
|-----|------|
| 3 | 0 |
|-----|------|我现在正在使用这个查询-
SELECT bid, COUNT( id ) as count
FROM `tbl_deals`
WHERE bid
IN (
SELECT id
FROM tbl_business
WHERE ( 6371 * ACOS( COS( RADIANS( 12.44 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS( long ) - RADIANS( 6.66 ) ) + SIN( RADIANS( 12.44 ) ) * SIN( RADIANS( lat ) ) ) ) < 1
)
GROUP BY bid
ORDER BY count DESC我找到了here的配方。但是对于bid 3,这里并没有显示count =0。我猜我必须使用Left join来完成这个任务,但是我不知道怎么做。
发布于 2012-03-15 23:13:58
是的,您必须编写一个外部连接,一个business LEFT JOIN deals
SELECT
b.id
, COUNT( d.bid ) AS dcount
FROM
tbl_business AS b
LEFT JOIN
tbl_deals AS d
ON
d.bid = b.id
WHERE
( 6371 * ACOS( COS( RADIANS( 12.44 ) )
* COS( RADIANS( lat ) )
* COS( RADIANS( long ) - RADIANS( 6.66 ) )
+ SIN( RADIANS( 12.44 ) )
* SIN( RADIANS( lat ) )
)
) < 1
GROUP BY
b.id
ORDER BY
dcount DESC发布于 2012-03-15 22:52:20
select id, (select count(*) from tbl_deals,tbl_business a1 where tbl_deals.bid=a1.id and a1.id=a2.id and ( 6371 * ACOS( COS( RADIANS( 12.44 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS( `long` ) - RADIANS( 6.66 ) ) + SIN( RADIANS( 12.44 ) ) * SIN( RADIANS( lat ) ) ) ) < 30) as count
from tbl_business a2
group by id
order by count desc我使用你的数据和改变条件来满足结果。
https://stackoverflow.com/questions/9724030
复制相似问题