This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 求助：被一个SQL查询语句难住了。问题是这样的：条件：
有两个表，一个包括用户信息，有：userid, city, email；另一个是transaction表，包括userid, tranamount（交易金额）,trantype（方式，只有deposit, withdraw两种）
要求：
查出交易方式为deposit，交易金额大于1000的用户信息，结果中列出
userid, city, email，deposit金额，deposit次数
哪位大侠有空看看？多谢！
-dakandao(~相留醉~);
2004-8-30
{312}
(#1865132@0)
-
顶！——水好大。
-dakandao(~相留醉~);
2004-8-30
(#1865165@0)
-
is it work or assignment? If it is for your job, you had better take a class; if it is an assignment, you had better DIY
-ra_95(小人-寻找生命的标价);
2004-8-30
(#1865188@0)
-
没看懂。 DIY?
-dakandao(~相留醉~);
2004-8-30
(#1865193@0)
-
他是说你这个问题太难啦，没学过SQL的都不会做。
-canadiantire(八卦轮胎);
2004-8-30
(#1865236@0)
-
try thisselect u.userid,city,email,sum(t.tranamount),count(*)
from user u,transaction t
where u.userid=t.userid and t.trantype='deposit' and t.tranamount>1000
group by u.userid,city,email
-yymeg(cherry);
2004-8-30
{184}
(#1865191@0)
-
测试通过。多谢cherry！
-dakandao(~相留醉~);
2004-8-30
(#1865204@0)
-
试一试SELECT userid, city, email, sum(transamount) AS Total, count(transamount) AS Times
FROM customer INNER JOIN transactions ON customer.ID = transactions.ID
WHERE transamount>1000 AND transtype='deposit'
GROUP BY customer.userid
-23456789(大白呼);
2004-8-30
{230}
(#1865206@0)
-
不行。1 报错不行。
1 报错“City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”，
2 即使将其加入group by, 返回的count数目也总是1。
Anyway，Cherry的答案对我已经足够了，多谢回复。
-dakandao(~相留醉~);
2004-8-30
{240}
(#1865234@0)
-
select userid,city,email,sum(tranamount), count(table2.trantype) from table1, table2 where table1.userid=table2.userid and table2.trantype='deposit'
group by 1,2,3
having sum(tranamount)>1000
-oceandeep(北极熊·湖州粽子);
2004-8-30
(#1865213@0)
-
Better soluntion. 很好用。
-dakandao(~相留醉~);
2004-8-30
(#1865262@0)
-
不行。１报错。是单项交易金额大于一千，不是交易总金额。要用where
-23456789(大白呼);
2004-8-30
{49}
(#1865265@0)
-
呵呵，我看题的理解是，总金额大于1000，无所谓了，反正不是where就是having了
-oceandeep(北极熊·湖州粽子);
2004-8-30
(#1865276@0)
-
刚才测试不细。新的结果如下：1 cherry大侠的放法返回次数好象不对（有2次deposit只返回次数为1的情况；），没来得及查原因；
2 北极熊的方法加一个临时表可以解决问题；
多谢各位！
-dakandao(~相留醉~);
2004-8-30
{145}
(#1865309@0)
-
yes, it is, i knew it :p, let me think about another solution
-yymeg(cherry);
2004-8-30
(#1865362@0)
-
back from lunch, try this, no temp table is requiredselect u.userid,city,email,sum(t.tranamount),count(*)
from user u,transaction t
and u.userid=t.userid and u.userid in
(select unique userid
from transaction
where trantype='deposit' and tranamount>1000)
and t.trantype='deposit'
group by u.userid,city,email
-yymeg(cherry);
2004-8-30
{275}
(#1865386@0)
-
这次结果好象对了,只要把第四行unique改成distinct就好了。谢谢！
-dakandao(~相留醉~);
2004-8-30
(#1865401@0)
-
select T1.userid, city, email, sum( tranamount), count(deposit) from Table1 T1 inner join table2 T2
on (T1.userid=T2.userid and trantype='deposit' and T2.tranamount>1000)
group by T1.userid, city, email
-charleslike(愚人);
2004-8-30
(#1865342@0)
-
好奇一下，transaction table 的key是什么?
-zxcvb(朝天椒);
2004-8-30
(#1865375@0)
-
uerid是一个Index, 另外有一个PK.
-dakandao(~相留醉~);
2004-8-30
(#1865390@0)
-
多谢以上各位的回复，解决了我的问题！我要接着干活了，不然交不了差了，所以暂时退出讨论。再次感谢！
-dakandao(~相留醉~);
2004-8-30
(#1865407@0)