select 代号=min(id),name'姓名',总价钱=sum(totalprice),第一次付款=sum(fsdprice), 第二次付款=sum(ssdprice) from table group by name
select min(id) as id ,name,sum(totalprice) as 总价钱,sum(fsdperice) as 第一次付款,sum(ssdprice) as 第二次付款 from table group by name
id name totalprice fsdprice ssdpriceselect sum(totalprice) as total,sum(fsdprice) as total1,sum(ssdprice) as total2 from tablename group by name
select min(id) as id,sum(totalprice) as total,max(fsdprice) as total1,max(ssdprice) as total2 from tablename group by name
select min(id) as 代号,name as 姓名,sum(totalprice) as 总价钱, sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款, cast(sum(fsdprice) as numeric(10,3))/sum(totalprice) as 第一次的百分比, cast(sum(ssdprice) as numeric(10,3))/sum(totalprice) as 第二次的百分比 from tablename group by name
select id as 代号,name as 姓名,sum(totalprice) as 总价钱,sum(fsdperice) as 第一次付款,sum(ssdprice) as 第二次付款,round(sum(fsdprice)/sum(totalprice)),4) as 第一次的百分比,round((sum(ssdprice)/sum(totalprice)),3) as 第二次百分比 from tab group by id
Column 'totalprice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. dut(到哪里都是菜鸟) ( ) 老大 出现这个
select min(id) as 代号,name as 姓名,sum(totalprice) as 总价钱, sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款, cast(sum(fsdprice) as numeric(10,3))/sum(totalprice) as 第一次的百分比, cast(sum(ssdprice) as numeric(10,3))/sum(totalprice) as 第二次的百分比 from tablename group by name你有没有改过语句?
--测试 create table test_0726 (id int,name varchar(10),totalprice int,fsdprice int,ssdprice int)insert into test_0726 select 1,'张三',30000,2000,3000 union all select 2,'张三',10000,0,0 union all select 3,'李四',40000,10000,2000 union all select 4,'李四',30000, 0 , 0 --查询 select min(id) as id ,name,sum(totalprice) as totalprice,sum(fsdprice) as fsdprice,sum(ssdprice) as ssdprice ,left(convert(varchar(10),round(sum(convert(decimal,fsdprice))/sum(convert(decimal,totalprice)),3)),4) as fsddai, left(convert(varchar(10),round(sum(convert(decimal,ssdprice))/sum(convert(decimal,totalprice)),3)),5) as ssdbai from test_0726 group by name order by id --结果 id name totalprice fsdprice ssdprice fsddai ssdbai ----------- ---------- ----------- ----------- ----------- -------- ---------- 1 张三 40000 2000 3000 0.05 0.075 3 李四 70000 10000 2000 0.14 0.029(所影响的行数为 2 行)
--测试 --建立环境 create table testtb ( id int, name varchar(20), totalprice int, fsdprice int, ssdprice int ) go insert testtb select 1, '张三', 30000, 2000, 3000 union all select 2 , '张三' , 10000 ,0 , 0 union all select 3 , '李四' , 40000 , 10000 , 2000 union all select 4 , '李四' , 30000 , 0 , 0 go--查询语句 select min(id) as 代号,name as 姓名,sum(totalprice) as 总价钱, sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款, cast(sum(fsdprice) as numeric(10,3))/sum(totalprice) as 第一次的百分比, cast(sum(ssdprice) as numeric(10,3))/sum(totalprice) as 第二次的百分比 from testtb group by name/* 结果: 代号 姓名 总价钱 第一次付款 第二次付款 第一次的百分比 第二次的百分比 ----------- -------------------- ----------- ----------- ----------- ----------------------- ----------------------- 3 李四 70000 10000 2000 .14285714285714 .02857142857142 1 张三 40000 2000 3000 .05000000000000 .07500000000000(所影响的行数为 2 行) */--删除环境 drop table testtb go
sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款
from tablename
group by name
sum(fsdprice) as '第一次付款' ,sum(ssdprice) as '第二次付款'
from yourtable
group by [name]
select 代号=min(id),姓名,总价钱=sum(totalprice),第一次付款=sum(fsdprice),第二次付款=sum(ssdprice)
from 表
group by 姓名
from 表
group by 姓名
from 表名
group by name
from tab group by id
应该是这样的了
现有
代号 姓名 总价钱 第一次付款 第二次付款
id name totalprice fsdprice ssdprice
1 张三 30000 2000 3000
2 张三 10000 0 0 名字相同,那么下面的付款为0
3 李四 40000 10000 2000
4 李四 30000 0 0现在要得到结果为
代号 姓名 总价钱 第一次付款 第二次付款 第一次的百分比 第二次的百分比
id name totalprice fsdprice ssdprice fsdbai ssdbai
1 张三 40000 2000 3000 结果(2000/40000)0.5 0.075
3 李四 70000 10000 2000 0.14 0.029
请问这个SQL语句怎么写
第二次付款=sum(ssdprice)
from table
group by name
from table group by name
from tablename group by name
from tablename group by name
sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款,
cast(sum(fsdprice) as numeric(10,3))/sum(totalprice) as 第一次的百分比,
cast(sum(ssdprice) as numeric(10,3))/sum(totalprice) as 第二次的百分比
from tablename
group by name
from tab group by id
1 张三 40000 2000 3000 结果(2000/40000)0.05 0.075
3 李四 70000 10000 2000 0.143 0.029
出现这个
sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款,
cast(sum(fsdprice) as numeric(10,3))/sum(totalprice) as 第一次的百分比,
cast(sum(ssdprice) as numeric(10,3))/sum(totalprice) as 第二次的百分比
from tablename
group by name你有没有改过语句?
create table test_0726
(id int,name varchar(10),totalprice int,fsdprice int,ssdprice int)insert into test_0726
select 1,'张三',30000,2000,3000
union all select 2,'张三',10000,0,0
union all select 3,'李四',40000,10000,2000
union all select 4,'李四',30000, 0 , 0
--查询
select min(id) as id ,name,sum(totalprice) as totalprice,sum(fsdprice) as fsdprice,sum(ssdprice) as ssdprice
,left(convert(varchar(10),round(sum(convert(decimal,fsdprice))/sum(convert(decimal,totalprice)),3)),4) as fsddai,
left(convert(varchar(10),round(sum(convert(decimal,ssdprice))/sum(convert(decimal,totalprice)),3)),5) as ssdbai
from test_0726
group by name
order by id
--结果
id name totalprice fsdprice ssdprice fsddai ssdbai
----------- ---------- ----------- ----------- ----------- -------- ----------
1 张三 40000 2000 3000 0.05 0.075
3 李四 70000 10000 2000 0.14 0.029(所影响的行数为 2 行)
--建立环境
create table testtb (
id int,
name varchar(20),
totalprice int,
fsdprice int,
ssdprice int
)
go
insert testtb
select
1, '张三', 30000, 2000, 3000
union all select
2 , '张三' , 10000 ,0 , 0
union all select
3 , '李四' , 40000 , 10000 , 2000
union all select
4 , '李四' , 30000 , 0 , 0
go--查询语句
select min(id) as 代号,name as 姓名,sum(totalprice) as 总价钱,
sum(fsdprice) as 第一次付款,sum(ssdprice) as 第二次付款,
cast(sum(fsdprice) as numeric(10,3))/sum(totalprice) as 第一次的百分比,
cast(sum(ssdprice) as numeric(10,3))/sum(totalprice) as 第二次的百分比
from testtb
group by name/*
结果:
代号 姓名 总价钱 第一次付款 第二次付款 第一次的百分比 第二次的百分比
----------- -------------------- ----------- ----------- ----------- ----------------------- -----------------------
3 李四 70000 10000 2000 .14285714285714 .02857142857142
1 张三 40000 2000 3000 .05000000000000 .07500000000000(所影响的行数为 2 行)
*/--删除环境
drop table testtb
go