select top 200 a.*,b.*
from MemberPrixList a
inner join memberlist b on a.mmembercardid=b.mmembercardid
inner join (select mRcommendId,sum(taoc) as 次数
from memberlist
group by mRcommendId
having sum(taoc)>1)c
on b.mRcommendId=c.mRcommendId
order by c.次数 desc
from MemberPrixList a
inner join memberlist b on a.mmembercardid=b.mmembercardid
inner join (select mRcommendId,sum(taoc) as 次数
from memberlist
group by mRcommendId
having sum(taoc)>1)c
on b.mRcommendId=c.mRcommendId
order by c.次数 desc
解决方案 »
- 帮我写条查询语句
- 为什么高手们要写这种添加数据的SQL语句?
- SQLSetPos执行批量添加
- SQL server中动态建立表
- 问个sum group by 的查询语句
- 如何实现三张表格的连接查询
- SQLserver 导出 大文件 到局域网内的电脑
- ADO+SQL SERVER+DCOM做的C/S三层程序,在客户端(win98)是否要装SQL SERVER CLIENT版?
- VC++中如何调用存储过程?
- SQL server全备的恢复时间点是哪一个?是备份开始的时间点,还是结束的时间点?
- sqlserver 怎么把查询结果的一个字段的多条数据合成一条呢,用一条语句
- 存储过程的性能会比批处理的T-SQL语句差吗?
select a.*
from memberlist a
where mRcommendId in
(select top 200 mRcommendId,sum(taoc) as 次数
from memberlist
group by mRcommendId
having sum(taoc)>1
order by 2 desc)-- 2
select a.*,b.cnt
from memberlist a
inner join
(select top 200 mmembercardid,cnt=sum(msummoney)
from MemberPrixList
group by mmembercardid
order by cnt desc) b on a.mmembercardid=b.mmembercardid
select top 200 mRcommendId,sum(taoc) as 次数, mphone,membername
from memberlist
group by mRcommendId
having sum(taoc)>1
order by 2 desc 2.
还有需要统计另外一个表
SELECT top 200 mmembercardid,cnt=sum(msummoney), memberlist.mphone, memberlist.membername
FROM MemberPrixList
inner join memberlist on memberlist.mmembercardid = MemberPrixList.mmembercardid
GROUP BY mmembercardid
ORDER BY cnt DESC
select a.*
from memberlist a
where mRcommendId in
(select top 200 mRcommendId
from memberlist
group by mRcommendId
having sum(taoc)>1
order by 2 desc)
-- 2
select a.*,b.cnt
from memberlist a
inner join
(select top 200 mmembercardid,cnt=sum(msummoney)
from MemberPrixList
group by mmembercardid
order by cnt desc) b on a.mmembercardid=b.mmembercardid
第一个提示ORDER BY 位置号 2 超出了选择列表中项数的范围。
第二个代码好像也不对。应该是排列处msummoney 最多的前200名。好像你是累加了前200数据?和直接
select sum(msummoney) from memberprixlist where mmembercardid='会员号' 查询出来的有出入
select top 200 mRcommendId,sum(taoc) as 次数,
MAX(mphone) mphone,
max(membername) membername
from memberlist
group by mRcommendId
having sum(taoc)>1
order by 2 desc 2:
SELECT top 200 mmembercardid,cnt=sum(msummoney),
max(memberlist.mphone) as mphone,
max(memberlist.membername) as membername
FROM MemberPrixList
inner join memberlist
on memberlist.mmembercardid = MemberPrixList.mmembercardid
GROUP BY mmembercardid
ORDER BY cnt DESC
可以了。我修改成
SELECT top 200 MemberPrixList.mmembercardid,cnt=sum(msummoney),
max(memberlist.mphone) as mphone,
max(memberlist.mmembername) as mmembername
FROM MemberPrixList
inner join memberlist
on memberlist.mmembercardid = MemberPrixList.mmembercardid
GROUP BY MemberPrixList.mmembercardid
ORDER BY cnt DESC
SELECT top 200 memberlist.mmembercardid,cnt=sum(msummoney),
max(memberlist.mphone) as mphone,
max(memberlist.membername) as membername
FROM MemberPrixList
inner join memberlist
on memberlist.mmembercardid = MemberPrixList.mmembercardid
GROUP BY memberlist.mmembercardid
ORDER BY cnt DESC
可以了。我修改成
SELECT top 200 MemberPrixList.mmembercardid,cnt=sum(msummoney),
max(memberlist.mphone) as mphone,
max(memberlist.mmembername) as mmembername
FROM MemberPrixList
inner join memberlist
on memberlist.mmembercardid = MemberPrixList.mmembercardid
GROUP BY MemberPrixList.mmembercardid
ORDER BY cnt DESC哦,是的, 这样对的
刚发现第一个也有错误啊。mphone和name和id对应不上。。
刚发现第一个也有错误啊。mphone和name和id对应不上。。哦,对了,你的 mmembercardid 和 mphone、name 是一对多的关系吗
刚发现第一个也有错误啊。mphone和name和id对应不上。。哦,对了,你的 mmembercardid 和 mphone、name 是一对多的关系吗不是一对多的。一个mmembercardid只有一个mphone和name
刚发现第一个也有错误啊。mphone和name和id对应不上。。哦,对了,你的 mmembercardid 和 mphone、name 是一对多的关系吗不是一对多的。一个mmembercardid只有一个mphone和name那照理就不会出现对应不上的情况的
刚发现第一个也有错误啊。mphone和name和id对应不上。。哦,对了,你的 mmembercardid 和 mphone、name 是一对多的关系吗不是一对多的。一个mmembercardid只有一个mphone和name那照理就不会出现对应不上的情况的应该是因为查询的字段是mRcommendId,mRcommendId是“推荐人”字段,所以这个是很多的。每个会员都有一个mRcommendId,所以可能是因为这个?
t1.mRcommendId,
sum(t1.taoc) as 次数,
MAX(t2.mphone) mphone,
max(t2.membername) membername
from memberlist t1
inner join memberlist t2
on t1.mRcommendId = t2.mmembercardid
group by t1.mRcommendId
having sum(t1.taoc)>1
order by 2 desc
可以了。感谢。顺便问价,如果有条件怎么加啊?在from memberlist t1后面加where ti.msd<>1 提示出错。
可以了。感谢。顺便问价,如果有条件怎么加啊?在from memberlist t1后面加where ti.msd<>1 提示出错。试试这个:
select top 200
t1.mRcommendId,
sum(t1.taoc) as 次数,
MAX(t2.mphone) mphone,
max(t2.membername) membername
from memberlist t1
inner join memberlist t2
on t1.mRcommendId = t2.mmembercardid
where ti.msd<>1 --加在这里试试
group by t1.mRcommendId
having sum(t1.taoc)>1
order by 2 desc