1. select kcode,max(kmoney) as kmoney
from ( select top 100 * from tablename ) a
group by kcode2. select kcode,
sum(case when ktype='yes' then kmoney*konum else kmoney end) knew_money
from ( select top 100 * from tablename ) a
group by kcode
解决方案 »
- sde与dbo的区别
- SQL语句求优化
- EXECUTE sp_executesql 返回游标值传入变量
- 如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中?
- 查询结果要分好几个组
- 一个简单问题:A电脑怎么访问到B电脑上的ODBC数据源
- 请教,如何在客户端中显示server中的查询的结果集,谢谢你提出建议!
- 还是如何替换text 字段
- 如何向数据库中插入datatime/smalldatatime/timestamp类型?使用odbc API!
- 请问:: 用asp写调用数据库并模糊查询的方法。。
- 求sql语句,应该有人问过的!
- 为什么我的这个查询可以运行,但由它产生的脚本却不能运行?
select a.kCode,[kMoney]=sum(a.kMoney)
from (select top 100 * from test) a
group by a.kCode
----Second
select a.kCode,
[knew_money]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end))
from (select top 100 * from test) a
group by a.kCode
select a.kCode,
[knew_money]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end))
from (select top 100 * from test) a
group by a.kCode
union all
select '汇总',
[knew_money]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end))
from test a
----First
select top 100 b.*
from (select a.kCode,[kMoney]=sum(a.kMoney) from test a group by a.kCode) b
按你的 1 SQL 所执行的结果。
kcode kmoney
-----------------
1030204 .00
130121 6560.00
2090087 1896.00
2090092 286.00
nokia .00不对的呀!
select a.kCode,
[kMoney]=sum(a.kMoney) into #tmpTab from test a
group by a.kCode
order by kMoney desc
select top 100 * from #tmpTab
-----------------
1030204 .00
130121 6560.00
2090087 1896.00
2090092 286.00
nokia .00也不是我想要的!根据 kmoney 的汇总进行降序排列,而不是 kcode !!!
[kMoney]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end))
into #tmpTab
from test a
group by a.kCode
order by kMoney desc
select top 100 * from #tmpTab
union all
select '汇总',
[knew_money]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end))
from test a
select a.kcode,sum(kmoney) as kmoney from (select top 100 * from tbl) a
group by a.kcode第二个查询
select a.kcode,
[knew_money]=sum(a.kmoney*(case a.ktype
when 'no' then 1
else cast(a.konum as numeric(10,2))
end))
from (select top 100 * from tbl ) a
group by a.kcode
kcode kmoney
-----------------
130121 6560.00
2090087 1896.00
2090092 286.00
nokia .00
1030204 .00
kcode kmoney
-----------------
130121 6560.00
2090087 1896.00
2090092 286.00
nokia .00
1030204 .00
这样还不行?你要怎么样的?
-----------------
130121 6560.00
2090087 1896.00
2090092 286.00
nokia .00
1030204 .00我每次测试都是提取 TOP 5 的记录,而我的记录近有5000个记录,排在前5位怎么会为0??
shuichangliu 后最的那个sql :
select a.kCode,
[kMoney]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end))
into #tmpTab from test a group by a.kCode order by kMoney desc
select top 100 * from #tmpTab
union all
select '汇总',
[knew_money]=sum(a.kMoney*(case a.kType when 'no' then 1 else a.kOnum end)) from test a可以实现,即得到前5个最大的kmoney 如下:kcode kmoney
-----------------
100001 41814.000000
555666 17576.320000
10300788 16340.000000
1030190 15662.000000
1030123 15220.000000但涉及到写入临时表,可不可以有更简便的方法呀!?