SQLによって計算した変数を次のSQLに代入できますか？

質問

user(テーブル)
id | name | bango
1 | taro | 1001
2 | sato | 1012
3 | miho | 1027
4 | hiro | 1066
*idはautoincrement、bangoはユニークの値

mark(テーブル)
id | check | bango
1 | 0 | 1001
2 | 1 | 1001
3 | 1 | 1001
4 | 0 | 1012
5 | 0 | 1012
6 | 1 | 1027
7 | 1 | 1027
8 | 0 | 1066
*idはautoincrement、checkは1か0、bangoはユニークの値

checkが1の確立が高い順にnameを一覧表示したいのですが、
うまく2つのテーブルを繋げることができません。

queryによって計算した変数を次のqueryに代入することはできるのでしょうか？

具体的には以下のような感じです。

bangoが1001のcheck=1の確立を出す場合

//bangoが1001の数
$test1 = mysql_query("select count(id) from mark where bango='1001';",$conn
$row1 = mysql_fetch_array($test1, MYSQL_ASSOC);
$totalct1 = $row1["count(id)"];

//bangoが1001かつcheckが1の数
$test2 = mysql_query("select count(id) from mark where bango='1001' and check='1' ;",$conn)
$row2 = mysql_fetch_array($test2, MYSQL_ASSOC);
$totalct2 = $row2["count(id)"];

//bangoが1001かつcheckが1の確立
if($totalct2==0){
$kakuritu = '0' ;
}
else{
$kakuritu = $totalct2 / $totalct1 * 100 ;
}

上記の変数を下記のように入れ込むことはできないのでしょうか？

$test3 = mysql_query("select name from user order by $kakuritu ;",$conn)

回答No.3

chukenkenkou · ベストアンサー

>次のように変えてみたのですがうまくいかないようです。
>これは構文的に問題があるのでしょうか？
>
>count(case when `check`=1 and `bango`=1001 then 1 else null end) as chk1,

>また、下記の構文を試したのですが結果が同じでした。
>
>SUM(IF(check=1 and bango=1001,1,0)) as chk1

「うまくいかない」とだけ書かれても、判断に困ります。具体的にどういうエラーメッセージやコードが出るのか提示してください。

使用している環境とともに、
(1)エラーになるなら、具体的なエラーの内容
(2)どういう結果を期待しているのに（期待とは違う）どういう結果になるのか
といったことを示すのが、こういったサイトを利用する上での鉄則です。

また、「user表にあって、mark表にはない」といったケースは、どうしたいのでしょうか？それにより、「inner join」か「outer join」かという表の結合方法が変わってきます。
また、bango列のデータ型は何ですか？
char(4)　　？
dec(4)　　　？
int　　　　　？
その他？

MySQLは、数字と文字のキャスト（型変換）を勝手にやってくれるというか、やってしまいます。これは、便利なようで、型変換のオーバーヘッドが発生します。

下記のSQLは、次の2点を変更しています。

(1)検索条件で、「bango=1001」を追加。
(2)「left join」を「inner join」に変更。
　→「not null」の検索条件を追加すれば、「left [outer] join」で可能だが、要件が不明なため、このようにしておきました。

<SQL2>改訂版
select
name,
y.*
from `user` as u
-- left join
inner join
(
select
*,
chk1/ttl as rate1
from
(select
bango,
count(case when `check`=1 then 1 else null end) as chk1,
count(id) as ttl
from `mark`
-- bango=1001だけ　ここから
where `bango`='1001'
-- bango=1001だけ　ここまで
group by bango
) as x
) as y
on u.bango=y.bango
order by y.rate1 desc,y.ttl desc,u.bango

お礼

うまくいかないだけでは答えようがありませんね。すいませんでした。
改訂版でうまくいきました。
どうもありがとうございました。

回答No.2

chukenkenkou

#1です。

「やりたいことをSQLでやるには？」という回答をしましたが、質問の「変数の使い方とSQL」については未回答でしたので、追記します。

次の二つで、話がまったく違ってきます。

(1)SQL中の条件式や更新値、挿入値の変数での置換

例1 「?」の部分を置換
select * from t1 where c1=?
update t1 set c2=? where c1=?

(2)SQL中の表名、列名、条件式そのものなどの置換

例2 SQL中の任意の内容（xxxやyyy、zzz）を置換
select * from xxx where yyy
update t1 set xxx=yyy where zzz

前者であれば、MySQL 5.0以降なら、prepareとexecuteの組み合わせで行えます。
後者であれば、SQL自体を文字列で組み立てるので、phpからの実行であれば、文字列を組み立てるだけのphpでの話しになります。

回答No.1

chukenkenkou

具体的なアドバイスをするには、情報が不足しています。

まず、MySQLのバージョンは？
MySQL 4.1でサブクエリの実装など、MySQLは4.0まで、4.1、5.0以降で、多くの機能拡張や一部の仕様変更があります。
そのため具体的なSQLを提示しようとすると、バージョンにより違ってきます。

少し細かい話ですが、user表はbangoでユニークになるなら、わざわざauto_increment列を持つ必要はありません。
bango列のコード体系の変更の可能性で、auto_increment列を持っておいた方がいいかという話になりますが、複数のユニーク列を持つということは、重複チェックのオーバーヘッドが生じるということにもなります。

mark表は、「*idはautoincrement、checkは1か0、bangoはユニークの値」となっていますが、提示されたデータを見る限り、ユニークになるのはid列だけでは？

「check=1の出現率が同じ」の場合、どういう順序で得たいのでしょうか？

まず、MySQL 4.1以降が前提ですが、次のSQLの結果を見てみてください。
もし、MySQL 4.0なら、サブクエリ部分を一時表（create temporary tableで定義）にするといったことで実装可能です。

次の優先順で、結果を返すようにしてみました。
(1)check=1の出現率が大きい順
(2)mark表の登録行数の多い順
(3)bangoの小さい順

クエリのネストは、別名で参照できるようにし、あえて深くしています。

<SQL例1>
select
*,
chk1/ttl
from
(select
bango,
count(case when `check`=1 then 1 else null end) as chk1,
count(id) as ttl
from `mark`
group by bango
) as x
order by chk1/ttl desc,ttl desc,bango

次のSQL例が、「やりたいこと」ではないかと思います。

<SQL例2>
select
name,
y.*
from `user` as u
left join
(
select
*,
chk1/ttl as rate1
from
(select
bango,
count(case when `check`=1 then 1 else null end) as chk1,
count(id) as ttl
from `mark`
group by bango
) as x
) as y
on u.bango=y.bango
order by y.rate1 desc,y.ttl desc,u.bango

補足

詳細なご説明ありがとうございます。

MYSQLのバージョンは5.0です。

bangoは出席番号のため重複する値がないという意味でユニークと書いてしまいました。

<SQL例2>の下記部分を

count(case when `check`=1 then 1 else null end) as chk1,

次のように変えてみたのですがうまくいかないようです。
これは構文的に問題があるのでしょうか？

count(case when `check`=1 and `bango`=1001 then 1 else null end) as chk1,

また、下記の構文を試したのですが結果が同じでした。

SUM(IF(check=1 and bango=1001,1,0)) as chk1