サブクエリなしで２つのテーブルを集計する方法

質問

テーブルA
id|cnt|name
1 2 田中さん
2 5 鈴木さん

テーブルB
id|cnt|name
1 4 田中さん
2 4 鈴木さん

サブクエリを使用せず、上記２つのテーブルを集計し、
下記のように出力できるSQLはありますでしょうか？

■期待する総合ランキング結果
id|cnt|name
2 9 鈴木さん
1 4 田中さん

P.S
まだまだ、安価なレンタルサーバはMySQLは4.0が多く苦労します・・

回答No.4

chukenkenkou · ベストアンサー

#3回答者です。

>同一のTemporary Tableを、１つのクエリーで２つ以上結合できないという制約

念のために確認しますが、提示したSQLを、「create temporary table」で作った表に対して実行していませんか？
もしそうなら、temporaryでない表で実行してください。
MySQLでは、バージョン5.0でも、「同一の一時表に対しては、自己結合等ができない」という制約があります。

提示したSQLは、4.0系のマニュアルを見た範囲では、明らかにその時点で未サポートの機能は使っていないと思います。
（こちらは、MySQL 5.0で実機確認しています）

また、バージョン4.0のunionには、以下の制約があるとのことです。
＝＝＝＝＝マニュアル引用＝＝＝＝＝
MySQL 4.1.1 より前のバージョンの UNION では、最初の SELECT で使用された値のみに基づいて結果の型と長さが決まる、という制約がありました。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

つまり、短い文字データ（今回のname列）が最初に見つかると、それ以降の文字データもその長さで切り捨てられるということです。

これに対応するには、cast関数でchar(n)に変換してしまう方法がありますが、サポートされたのがMySQL 4.0.6だそうです。

もう一点、unionを使って全体をorder byする場合は、「各クエリを括弧で囲む」という制約がありました。この制約は、MySQL 5.0では解除されているようです。

これらを考慮すると、以下のようになります。

【改訂版SQL例】
(select A.id,ifnull(A.cnt,0)+ifnull(B.cnt,0) as cnt,cast(A.name as char(10)) as name
from A inner join B
on A.id=B.id)
union all
(select A.id,ifnull(A.cnt,0)+ifnull(B.cnt,0) as cnt,cast(A.name as char(10)) as name
from A left join B
on A.id=B.id
where B.id is null)
union all
(select B.id,ifnull(A.cnt,0)+ifnull(B.cnt,0) as cnt,cast(B.name as char(10)) as name
from B left join A
on B.id=A.id
where A.id is null)
order by cnt desc;

回答No.5

yambejp

SQL的にはNGですが、単純にテンポラリにテーブルAとBを流しこんで
GROUP BY してSUMを得るのが妥当では？

CREATE TEMPORARY TABLE `TEMP集計`(`id` INT ,`cnt` INT,`name` VARCHAR(30));
INSERT INTO `TEMP集計` SELECT `id`,`cnt`,`name` FROM `テーブルA`;
INSERT INTO `TEMP集計` SELECT `id`,`cnt`,`name` FROM `テーブルB`;
SELECT `id`,SUM(`cnt`) AS `cnt`, `name` FROM `TEMP集計` GROUP BY `id`;

テーブルAとBを別に管理しているのが問題の原因なので、それを
何とかしない限り効率的なSQLは難しいでしょう

回答No.3

chukenkenkou

#2回答者です。
以下のSQLでどうでしょうか？

最初のクエリは、両方にあるもの、
2番目は、AにありBにないもの、
最後が、BにありAにないもの
を得て、unionで繋げています。

select A.id,ifnull(A.cnt,0)+ifnull(B.cnt,0) as cnt,A.name
from A inner join B
on A.id=B.id
union all
select A.id,ifnull(A.cnt,0)+ifnull(B.cnt,0) as cnt,A.name
from A left join B
on A.id=B.id
where B.id is null
union all
select B.id,ifnull(A.cnt,0)+ifnull(B.cnt,0) as cnt,B.name
from B left join A
on B.id=A.id
where A.id is null
order by cnt desc;

お礼

同一のTemporary Tableを、１つのクエリーで２つ以上結合できないという制約により実現できませんでしたが、SQLって結構なんでもできるんですね。大変勉強になりました。結合に関してきちんと勉強してみたいと思います。ありがとうございました。
http://www.mysql.gr.jp/mysqlml/mysql/msg/9187

回答No.2

chukenkenkou

#1で補足要求した通りだとすると、こんな感じでしょう。

select A.id,A.cnt+B.cnt as cnt,A.name
from A,B
where A.id=B.id
order by cnt desc;

お礼

ありがとうございます。補足の通りで私の計算ミスでした。
とりあえず1歩前進できました。
しかし・・また新たな壁にぶち当たりました。。

AにあってBにない。BにあってAにない場合がありました。。
流石にこれを１つのSQLで実行することは無理でしょうか？

回答No.1

chukenkenkou

テーブルAとテーブルBのcntを、足し算するのですか？
期待する結果の「田中さん」のcnt列は、6の間違い？

ランキング表ということは、cnt列で降順ソートするのですか？