现有如下表:t1(主表),字段有:userid,name,xingbie,guojiaid,yuyanid
t2,字段有:uid,pinglun
t3,字段有:id,gname
t4, 字段有:id,lname
---------------------
要注意的是:userid在t2中和uid是对应的。guojiaid和t3中的id是对应的。yuyanid和t4中的id是对应的。pinglun的是1或0
我像得到结果如下:
userid ,name,xingbie,guojia,yuyan,haop,huaip
其中haop是t2中pinglun字段值为1的行数,huaip是t2中pinglun字段值为0的行数
也就是说一个用户如果在t2中有数据,就查出来并显示好评和坏评各是多少。如果在t2中没有数据。那就全部显示0
gname 是国家名称。Lname是语言名称。根据ID要显示到结果中
不知道大家明白吗? 用SQL 语句怎么实现。谢谢。好的话 高分送上。或再加分。
t2,字段有:uid,pinglun
t3,字段有:id,gname
t4, 字段有:id,lname
---------------------
要注意的是:userid在t2中和uid是对应的。guojiaid和t3中的id是对应的。yuyanid和t4中的id是对应的。pinglun的是1或0
我像得到结果如下:
userid ,name,xingbie,guojia,yuyan,haop,huaip
其中haop是t2中pinglun字段值为1的行数,huaip是t2中pinglun字段值为0的行数
也就是说一个用户如果在t2中有数据,就查出来并显示好评和坏评各是多少。如果在t2中没有数据。那就全部显示0
gname 是国家名称。Lname是语言名称。根据ID要显示到结果中
不知道大家明白吗? 用SQL 语句怎么实现。谢谢。好的话 高分送上。或再加分。
A.userid,
a.name,
A.xingbie,
C.gname,
D.lname,
SUM(CASE WHEN B.pinglun=1 THEN 1 ELSE 0 END) AS haop,
SUM(CASE WHEN B.pinglun=0 THEN 1 ELSE 0 END) AS huaip
FROM t1 AS A
LEFT JOIN t2 AS B
ON A.userid=B.uid
LEFT JOIN t3 AS C
ON A.guojiaid=C.id
LEFT JOIN t4 AS D
ON A.yuanid=D.id
GROUP BY
A.userid,
a.name,
A.xingbie,
C.gname,
D.lname
select userid ,
name,
xingbie,
guojia,
yuyan,
haop = isnull(sum(case when pinglun = 1 then 1 else 0 end),0),
huaip = isnull(sum(case when pinglun = 0 then 1 else 0 end),0)
from
(
select a.*,b.pinglun ,c.gname ,d.lname
from t1 a left join t2 b on a.userid = b.uid
left join t3 c on a.guojiaid = c.id
left join t4 d on a.yuyanid = d.id
)T
group by userid,name,xingbie,guojia,yuyan
a.userid,a.name,a.xingbie,c.gname as guojia,d.lname as yuyan,
haop=sum(case when b.pinglun=1 then 1 else 0 end),
huaip=sum(case when b.pinglun=1 then 1 else 0 end)
from
t1 a
left join t2 b on a.userid=b.uid
left join t3 c on a.guojiaid=c.id
left join t4 d on a.yuyanid=d.id
group by
a.userid,
a.name,
a.xingbie,
c.gname
d.lname
haop=sum(case pinglun when 1 then 1 else 0 end),
huaip=sum(case pinglun when 0 then 0 else 1 end)
from t1 left join t2 on t1.userid=t2.uid
left join t3 on t1.guojiaid=t3.id
left join t4 on t1.yuyanid=t4.id
select a.userid,a.name,a.xingbie,c.gname,d.lname,
sum (case when b.pinglun =1 then 1 else 0 end) as haop,
sum (case when b.pinglun =0 then 1 else 0 end) as huaip
from
t1 a left join t2 b on a.userid=b.uid
left join t3 c on a.guojiaid=c.id
left join t4 d on a.yuyanid=d.id
group by a.userid,a.name,a.xingbie,c.gname,d.lname
name,
xingbie,
guojia,
yuyan,
haop = isnull(sum(case when pinglun = 1 then 1 else 0 end),0),
huaip = isnull(sum(case when pinglun = 0 then 1 else 0 end),0)
from
(
select a.*,b.pinglun ,c.gname ,d.lname
from t1 a left join t2 b on a.userid = b.uid
left join t3 c on a.guojiaid = c.id
left join t4 d on a.yuyanid = d.id
)T
group by userid,name,xingbie,guojia,yuyan
haop= SUM(CASE WHEN b.pinglun=1 THEN 1 ELSE 0 END),
huaip=SUM(CASE WHEN b.pinglun=1 THEN 1 ELSE 0 END)
FROM t1 LEFT JOIN t2 ON t1.userid=t2.uid
LEFT JOIN t3 ON t1.guojiaid=t3.id
LEFT JOIN t4 ON t1.yuyanid=t4.id
GROUP BY t1.userid,t1.name, t1.xingbie,t3.gname,t4.lname
sum(case when t2.pinglun=1 then 1 else 0 end ) as haop,
sum(case when t2.pinglun=0 then 1 else 0 end) as huaip
from t1 left join t2
on t1.userid=t2.usid
left join t3
on t1.guojiaid=t3.id
left join t4
on t1.yuyanid=t4.id
group by t1.userid,t1.name,t1.xingbie,t3.gname,t4.lname