现在有两个表
表一(user)：
UID 编号
UNAME 姓名
表二(yeji)：
UID 编号
cj 金额
sign 是否成交（成交1 不成交0）
现在要做一个统计结果
希望显示的效果是：
UNAME CJ1(成交的金额) cj(金额) radio(成交比例)
要求： 表一中的UNAME中全部列出来（因为不一定每个人都有业绩的），结果按照CJ1降序排序
请问这个查询语句怎么写呢？谢谢！
------解决方案--------------------
select a.UID,UNAME,sum(case when sign=1 then cj else 0 end) CJ1,sum(cj) cj,sum(case when sign=1 then cj else 0 end)*1.0/sum(cj) radio from [User] a Inner Join [yeji] b on a.UID=b.UID group by a.UID,UNAME order by 3 desc
------解决方案--------------------
DECLARE @T1 TABLE(UID INT,UNAME VARCHAR(20))
INSERT INTO @T1
SELECT 1,'张三'
UNION ALL SELECT 2,'李四'
UNION ALL SELECT 3,'王五'
UNION ALL SELECT 4,'赵六'
DECLARE @T2 TABLE(UID INT,CJ DECIMAL(28,2),[SIGN] CHAR(1))
INSERT INTO @T2
SELECT 1,1000.23,'1'
UNION ALL SELECT 2,1000.23,'1'
UNION ALL SELECT 3,2000.34,'1'
UNION ALL SELECT 4,3000.56,'1'
UNION ALL SELECT 1,4000.78,'1'
UNION ALL SELECT 2,5000.9,'0'
UNION ALL SELECT 3,6000.01,'1'
UNION ALL SELECT 4,7000.23,'0'
SELECT T1.UNAME,CJ1=SUM(CASE WHEN T2.[SIGN]='1'THEN T2.CJ ELSE 0 END),
CJ=SUM(CJ),
RADIO=SUM(CASE WHEN T2.[SIGN]='1'THEN T2.CJ ELSE 0 END)/SUM(CJ)
FROM @T1 T1 LEFT JOIN @T2 T2 ON T1.UID=T2.UID
GROUP BY T1.UID,T1.UNAME
ORDER BY SUM(CASE WHEN T2.[SIGN]='1'THEN T2.CJ ELSE 0 END)DESC
------解决方案--------------------
select t.* , cjje*100/cj from
(
select user.uname , cjje = sum(case yeji.sign when 1 then yeji.je else 0 end) , sum(yeji.cj) yeji.cj from user,yeji where user.uid = yeji.uid
) t
order by uid