能不能用一个sql语句实现这样的select?

[loveflea]

有三个表

card_money 卡上剩余金额 card_id 卡号[唯一] limit_money 剩余金额
card_spent 消费金额 card_id 卡号[不唯一] spent_money 消费的money
card_subscription 预约金额 card_id 卡号[不唯一] card_subscription 预约金额

一条sql语句能否取出这样数据[统计要正确，我的这个统计不正确]
select a.card_id,a.limit_money,sum(b.spent_money) as spent_money,sum(c.card_subscription) as subscription_money from card_money a inner join card_spent b on a.card_id=b.card_id left join card_subscription c on b.card_id=c.card_id group by a.card_id,b.card_id;

card_id limit_money spent_money card_subscription
00000001 1000.00 16208.80 646.20
00000002 890.20 1732.40 1096.40
00000003 1523.20 452.20


测试数据如下

[shuixin13]

用临时表吧,
除非使用 MySQL 4.1 ,
它支持子查询

[loveflea]

支持子查询的语句又该怎么写？？其实我是用再 sybase 上的；感觉不太好写！

Adaptive Server Enterprise/12.0/P/SWR 8776 ESD 1/NT (IX86)/OS 4.0/1580/32bit/OPT/Mon Dec 06 21:50:07 1999

[shuixin13]

SELECT tA.card_id, tB.spent_money, tC.card_subscription FROM card_money tA
LEFT JOIN
(
SELECT card_id, SUM(spent_money) FROM card_spent GROUP BY card_id
) tB ON tA.card_id = tB.card_id
LEFT JOIN
(
SELECT card_id, SUM(card_subscription) FROM card_subscription GROUP BY card_id
) tC ON tA.card_id = tC.card_id

[ezhou]

sybase和mysql能用相同的sql么？它们对sql的支持程度肯定不同的。

[SuperGam]

select
a.card_id,a.limit_money,b.spent_money,c.card_subscription
from
(select cardid ,limit_money from card_money) as a,
(select cardid,sum(spent_money) as spent_money) as b,
(select cardid,sum(card_subscription) as card_subscription) as c,
where
a.cardid*=b.cardid and a.cardid*=c.cardid;

[loveflea]

# MySQL-Front Dump 2.5
#
# Host: localhost Database: cards
# --------------------------------------------------------
# Server version 4.0.13-nt

# create database tmp

CREATE DATABASE IF NOT EXISTS tmp;

# use database tmp

USE tmp;

#
# Table structure for table 'card_money'
#

DROP TABLE IF EXISTS card_money;
CREATE TABLE card_money (
card_id varchar(8) NOT NULL default '',
limit_money decimal(8,2) default NULL
) TYPE=MyISAM;



#
# Dumping data for table 'card_money'
#

INSERT INTO card_money (card_id, limit_money) VALUES("00000001", "1000.00");
INSERT INTO card_money (card_id, limit_money) VALUES("00000002", "890.20");
INSERT INTO card_money (card_id, limit_money) VALUES("00000003", "1523.20");
INSERT INTO card_money (card_id, limit_money) VALUES("00000004", "254.80");
INSERT INTO card_money (card_id, limit_money) VALUES("00000005", "5789.20");


#
# Table structure for table 'card_spent'
#

DROP TABLE IF EXISTS card_spent;
CREATE TABLE card_spent (
card_id varchar(8) NOT NULL default '',
spent_money decimal(8,2) default NULL
) TYPE=MyISAM;



#
# Dumping data for table 'card_spent'
#

INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "56.00");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000003", "452.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "7894.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000002", "184.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000001", "154.20");
INSERT INTO card_spent (card_id, spent_money) VALUES("00000002", "1548.20");


#
# Table structure for table 'card_subscription'
#

DROP TABLE IF EXISTS card_subscription;
CREATE TABLE card_subscription (
card_id varchar(8) NOT NULL default '',
card_subscription decimal(8,2) default NULL
) TYPE=MyISAM;



#
# Dumping data for table 'card_subscription'
#

INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000001", "58.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000004", "15.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000005", "14.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000001", "157.20");
INSERT INTO card_subscription (card_id, card_subscription) VALUES("00000002", "548.20");

[loveflea]

Sybase SQL Server 使用子查询时应遵守以下规则：

---- subquery_select_list指定的所有列的最大长度之和不能超过256字节。

---- 子查询不能用于order by、group by和compute by列表。

---- 子查询不能在内部操纵其结果，即不能包括into、order by、compute子句和union运算符。

---- 子查询不能包括for browse子句。

---- subquery_select_list一般只能包括一个表达式或列名，只有由exists引导的子查询例外，它的select列表通常使用星号（*）来表示。

---- 由比较运算符引导的子查询必须返回单值，并且值的类型必须符合该运算符的类型转换规则。

---- 子查询中不能使用text和image数据类型。

---- 可更新游标的select语句不允许使用相关子查询。

---- 内嵌级限制为16。

---- UNION语句中，每个SELECT语句的最大子查询数为16。

[heavenmusic]

头会晕啊~~~~~~~~~~~~~~~呵呵.....