日期:2014-05-16  浏览次数:20789 次

凌晨!求一句SQL
有三个表
TA
____________
UserID     Type
1               "A1 "
1               "A2 "
2               "A1 "
1               "A2 "
2               "A1 "
1               "A2 "
1               "A1 "

我想统计用户1拥有的纪录中A1,A2的个数,所以写
SELECT   SUM(IIF(Type= "A1 ",1,0)   AS   A1,SUM(IIF(Type= "A2 ",1,0)   AS   A1   WHERE   UserID=1
这句话是完全没有问题的。

另外一张表
TU
----------
UserID         Point
1                   1000
2                   1500

我想取出用户1的point,写SELECT   Point   WHERE   UserID=1
这句话也没有问题

我想把这两句话合并到一句SQL中,请问我改怎么做?

我这样
SELECT   SUM(TU.Point)   AS   Point,SUM(IIF(TA.Type= "A1 ",1,0)   AS   A1,SUM(IIF(TA.Type= "A2 ",1,0)   AS   A1,SUM(1)   AS   [Time]   FROM   TA,TU   WHERE   TA.UserID   AND   TB.UserID

这样我用Point/Time也能得到正确的Point

但是问题出来了,如果TA里没有用户1的纪录,这个查询将什么也不返回,但是我希望,他能返回Point,TA如果没有纪录,就返回0

  请问,这条语句该怎样写?谢谢



------解决方案--------------------
--用子查询处理

SELECT
(select SUM([Point]) from TU where [UserID]=T.[UserID] ) AS Point,
SUM(IIf([Type]= "A1 ",1,0)) AS A1,
SUM(IIf([Type]= "A2 ",1,0)) AS A2
FROM TA as T
WHERE [UserID]=1