MySQLで複雑な条件でレコードを取得したい

Question

やりたいこと

itemテーブルがあり、
そこには、id、add_lat、add_lng、add_cityというカラムが存在するのですが、
以下のような異なる条件で取得をして、重複するレコードを除外するという処理をやりたいのですが、詰まってしまい質問させてください。

やったこと

SELECT *,(6371 * ACOS(COS(RADIANS(35.694003)) * COS(RADIANS(add_lat)) * COS(RADIANS(add_long) - RADIANS(139.753595)) + SIN(RADIANS(35.694003)) * SIN(RADIANS(add_lat)))) AS distance FROM item WHERE state = 1 HAVING distance <= 25 ORDER BY distance;

以上のSQLで緯度35.694003、経度139.753595から半径25km内の距離にあるレコードを抽出出来ます。仮にAとします。

SELECT * FROM item WHERE state = 1 AND add_city = '千代田区';

上記で千代田区内に属するレコードを取得できます。仮にBとします

イメージとしては、Aのレコード+Bのレコードで重複したidを持つレコードを除外したいと考えています。

UNIONを使って以下のようにしてみたけど、正しい値が出力されないです。

(SELECT *,(6371 * ACOS(COS(RADIANS(35.694003)) * COS(RADIANS(add_lat)) * COS(RADIANS(add_long) - RADIANS(139.753595)) + SIN(RADIANS(35.694003)) * SIN(RADIANS(add_lat)))) AS distance FROM item WHERE state = 1 HAVING distance <= 25 ORDER BY distance)
UNION
(SELECT *,(6371 * ACOS(COS(RADIANS(35.694003)) * COS(RADIANS(add_lat)) * COS(RADIANS(add_long) - RADIANS(139.753595)) + SIN(RADIANS(35.694003)) * SIN(RADIANS(add_lat)))) AS distance FROM item WHERE state = 1 AND add_city = '千代田区');

難解な処理をしようとしているため、説明が難しく、ツッコミをいただけましたら、補足をくわえさせていただきます。

補足

Bでは千代田区内のみを抽出するのですが、Aでは基準点から半径25km以内のレコードも取得しているので、その差分をBに加えるという処理を行いたいです

Comments

[x_x]

本質ではないかもしれませんが、UNION前のORDER BYは無効では？　MySQLならいけるのでしょうか？

Answer 1

条件としては、「千代田区で緯度35.694003、経度139.753595から半径25km内の距離にあるもの」ではないですか？
だとしたら、最初のSQLのwhere条件に「AND add_city = '千代田区'」を加えるだけだと思いますけど。

追記

補足をみるとAまたはBの集合ってことの様ですね。
でしたら、

select *
      ,[基準からの距離] as distance
where id in (
        select id from item
        where state=1
          and [基準からの距離]<=25 
      union all
        select id from item
        where state=1 AND add_city = '千代田区'
      )
ORDER BY distance

※別にunionじゃなくてorでよかった。

select *
from (
    select *,[基準からの距離] as distance from item 
) t1
where state=1
  and (distance<=25 or add_city = '千代田区')
ORDER BY distance

Comments

[kenny_sayama]

AまたはBの集合の集合です！
上記を実行したのですが、エラーとなってしまいました。

select *
from (
select *,(6371 * ACOS(COS(RADIANS(35.694003)) * COS(RADIANS(add_lat)) * COS(RADIANS(add_long) - RADIANS(139.753595)) + SIN(RADIANS(35.694003)) * SIN(RADIANS(add_lat)))) as distance from item
)
where state=1
and (distance <= 25 or add_city = '千代田区')
ORDER BY distance;

エラー内容
Every derived table must have its own alias

こちら原因わかりますでしょうか...？

[sazi]

サブクエリーにエイリアスがついていないからですね。
回答だと、t1の部分

[kenny_sayama]

出来ました！ありがとうございます！

Answer 2

SQLはCREATE TABLE文, 現状の設定されているデータをINSERT文で提示できると適切なコメントが付き易いです。

UNIONを使って以下のようにしてみたけど、エラーとなる。

そのエラーメッセージをそのまま載せては？UNIONの上と下でSELECTされた列数が違うのでエラーになっているのでしょうけど。

緯度 経度 地名 API

でググると使えそうなAPIがあるようです。

Comments

[kenny_sayama]

エラーを回避したものに本文を修正いたしましたm(_ _)m

[Orlofsky]

>AND add_city = '千代田区'
を上のSQLに追加すればUNION以下は不要では？現状のSELECT結果もCREATE TABLEもINSERTも提示する気はないようだから、それで正しい結果になるかはわかりません。
APIを探した方が早そうな気がします。

[kenny_sayama]

公開出来ない情報があり、申し訳ありません...。
APIも検討したのですが、制約がありSQLだけで対応をしなければいけないという状態です。

Answer 3

単純に下のSELECT文に列別名「distance」に相当する列がないだけでは？

Comments

[kenny_sayama]

すみませんもう少々補足いただけますでしょうか...?

[kenny_sayama]

エラーを回避したものに本文を修正いたしましたm(_ _)m