求高手帮小弟我解决一个SQL server语句的有关问题！
日期：2014-05-18 浏览次数：20529 次
求高手帮我解决一个SQL server语句的问题！！！
我如果想查询本月的所有支出，在SQL server中用
select SUM(price) as sumpayM from pay where DATEPART(MM,time)
=DATEPART(MM,GETDATE()) and DATEPART(YY,time)
=DATEPART(YY,GETDATE()) and userid=1;就可了。
但如果我想查询本月的每天所有支出，应该怎么写啊！
我有这么一段代码，不过是oracle数据库的语句：
//查询本月支出情况
public List getPayByM(Connection con,Long userId)throws SQLException{
Statement st = con.createStatement();
int s = this.GetDay(con);
List list = new ArrayList();
String a ="";
for(int i=0;i<s;i++){
int z = i+1;
if(i==(s-1)){
a+="sum(decode(extract(day from b.times),"+(i+1)+",b.price,0)) as a"+(i+1);
}else{
a+="sum(decode(extract(day from b.times),"+(i+1)+",b.price,0)) as a"+(i+1)+" , ";
}
}
String sql ="SELECT "+a+" FROM pay b where b.userid="+userId+" and extract(month from b.times)=07";
ResultSet rs1 = st.executeQuery(sql);
if(rs1.next()){
for(int i=0;i<s;i++){
list.add(rs1.getInt("a"+(i+1)));
}
}
return list;
}
------解决方案--------------------
我如果想查询本月的所有支出，在SQL server中用
select SUM(price) as sumpayM from pay where DATEPART(MM,time)
=DATEPART(MM,GETDATE()) and DATEPART(YY,time)
=DATEPART(YY,GETDATE()) and userid=1;就可了。
但如果我想查询本月的每天所有支出，应该怎么写啊！
我有这么一段代码，不过是oracle数据库的语句：
//查询本月支出情况
public List getPayByM(Connection con,Long userId)throws SQLException{
Statement st = con.createStatement();
int s = this.GetDay(con);
List list = new ArrayList();
String a ="";
for(int i=0;i<s;i++){
int z = i+1;
if(i==(s-1)){
a+="sum(decode(extract(day from b.times),"+(i+1)+",b.price,0)) as a"+(i+1);
}else{
a+="sum(decode(extract(day from b.times),"+(i+1)+",b.price,0)) as a"+(i+1)+" , ";
}
}
String sql ="SELECT "+a+" FROM pay b where b.userid="+userId+" and extract(month from b.times)=07";
ResultSet rs1 = st.executeQuery(sql);
if(rs1.next()){
for(int i=0;i<s;i++){
list.add(rs1.getInt("a"+(i+1)));
}
}
return list;
}
------解决方案--------------------
- SQL code
select SUM(price) as sumpayD from pay where DATEPART(MM,time) =DATEPART(MM,GETDATE()) and DATEPART(YY,time) =DATEPART(YY,GETDATE()) and userid=1 group by cast(time as varchar(10))
------解决方案--------------------
- SQL code
select convert(varchar(10),time,120) D,SUM(price) as sumpayD from pay where DATEPART(MM,time) =DATEPART(MM,GETDATE()) and DATEPART(YY,time) =DATEPART(YY,GETDATE()) and userid=1 group by convert(varchar(10),time,120)
------解决方案--------------------
- SQL code
select convert(varchar(10),time,120),d,sum(price) as sumpayD from pay where datediff(mm,time,getdate())=0 and userid=1 group by convert(varchar(10),time,120),d
------解决方案--------------------
- SQL code
select SUM(price) as sumpayM from pay where DATEPART(MM,time) =DATEPART(MM,GETDATE()) and DATEPART(YY,time) =DATEPART(YY,GETDATE()) and userid=1 group by convert(varchar(10),time,120)
------解决方案--------------------
--多分析一下问题，往往分析能帮你发现新的思路
select
convert(varchar(10),time,120),d,sum(price) as sumpayD
from
pay
where
datediff(mm,time,getdate())=0
and
userid=1
group by
convert(varchar(10),time,120),d
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
