① orcal sql语句查询指定年12月数据
SELECTSTU.ID,
STU.NAME,
DECODE(TO_CHAR(WDATE,'mm'),1,WDATE)"1月",
DECODE(TO_CHAR(WDATE,'mm'),2,WDATE)"2月",
DECODE(TO_CHAR(WDATE,'mm'),3,WDATE)"3月",
DECODE(TO_CHAR(WDATE,'mm'),4,WDATE)"4月",
DECODE(TO_CHAR(WDATE,'mm'),5,WDATE)"5月",
DECODE(TO_CHAR(WDATE,'mm'),6,WDATE)"6月",
DECODE(TO_CHAR(WDATE,'mm'),7,WDATE)"7月",
DECODE(TO_CHAR(WDATE,'mm'),8,WDATE)"8月",
DECODE(TO_CHAR(WDATE,'mm'),9,WDATE)"9月",
DECODE(TO_CHAR(WDATE,'mm'),10,WDATE)"10月",
DECODE(TO_CHAR(WDATE,'mm'),11,WDATE)"11月",
DECODE(TO_CHAR(WDATE,'mm'),12,WDATE)"12月"
FROMstu,WORK
WHEREstu.id=work.stu_id;
② SQL 一个表中存储用户的缴费记录，现需要查出用户12月的缴费记录
可以通过语句的group by和case when来实现，以下是oracle语法
费用月份我假设是日期格式
selectto_char(费用月份,'yyyy')as费用年份,用户ID,
sum(casewhento_number(to_char(sysdate,'MM'))=1then用户费用else0end)as一份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=2then用户费用else0end)as二份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=3then用户费用else0end)as三份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=4then用户费用else0end)as四份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=5then用户费用else0end)as五份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=6then用户费用else0end)as六份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=7then用户费用else0end)as七份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=8then用户费用else0end)as八份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=9then用户费用else0end)as九份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=10then用户费用else0end)as十份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=11then用户费用else0end)as十一份月费用,
sum(casewhento_number(to_char(sysdate,'MM'))=12then用户费用else0end)as十二份月费用
from缴费记录表where用户ID=1
groupbyto_char(费用月份,'yyyy'),用户ID
③ sql语句,循环生成一到十二月的汇总表
Select
xxxxxxxxxx,xxx,xxx,xxx
from
myTable
where
xxxx
group
by
xxx,xxx,xxx
关键在于分组group
by
，要你一直这么Union
那要1年365天你还得写365个union?
顺便说下，按你的意思的话，用报表中的矩阵很轻松就能解决。
④ MySQL统计过去12个月的订单数据(包括本月) ，sql怎么组织，求sql大神
select left(FROM_UNIXTIME(Order_Endtime),7), count(1)
from tableName
group by left(FROM_UNIXTIME(Order_Endtime),7)
如果需要查看某个时间段，加上where条件
如果按年统计，7改成4
⑤ 如何用sql的日期函数，分别查出1月~12月每个月的销售金额
一般情况下数据是按日期存到数据库中的，但考虑到某些月份是无数据的，可用如下语句：
1、创建测试表及插入测试数据：
create table sale
(id int,
name varchar(10),
sdate datetime,
money int)
insert into sale values (1,'西瓜','2015-01-01',10)
insert into sale values (2,'香蕉','2015-01-05',20)
insert into sale values (3,'苹果','2015-02-01',60)
insert into sale values (4,'葡萄','2015-02-23',345)
insert into sale values (5,'柚子','2015-04-23',10)
insert into sale values (6,'牛奶','2015-05-12',67)
insert into sale values (7,'地瓜','2015-06-01',10)
insert into sale values (8,'薯仔','2015-07-01',10)
2、执行语句：
with t as
( select '2015-'+right('0'+cast(number+1 as varchar),2) number from master..spt_values where type='p' and number<=11 )
select t.number month,SUM(isnull(b.money,0)) money
from t left join sale b on
t.number=CONVERT(varchar(7),b.sdate,120)
group by t.number
结果截图：
⑥ 在SQL里面怎么实现一列输出12月的数据
给你一个例子~！：
ALTER PROCEDURE [dbo].[sp_ReprotOfStaffByStorehouse]
@dbdate varchar(20), --年份选择
@inyStoresID int --门店编号
AS
--删除原数据
If object_id('tempdb..#tbReprotOfStaff') is not null
Drop table #tbReprotOfStaff
--创建新临时表
Create table #tbReprotOfStaff (
sMonth varchar(10),
sNum1 decimal(18,0),
sPrice1 int,
sNum2 decimal(18,0),
sPrice2 int,
sNum3 decimal(18,0),
sPrice3 int,
sNum4 decimal(18,0),
sPrice4 int,
sNum5 decimal(18,0),
sPrice5 int,
sNum6 decimal(18,0),
sPrice6 int,
sNum7 decimal(18,0),
sPrice7 int,
sNum8 decimal(18,0),
sPrice8 int,
sNum9 decimal(18,0),
sPrice9 int,
sNum10 decimal(18,0),
sPrice10 int,
sNum11 decimal(18,0),
sPrice11 int,
sNum12 decimal(18,0),
sPrice12 int
)
BEGIN
begin
insert into #tbReprotOfStaff
select datepart(month,v.pMonth) as pMonth,sum(v.sNum1) as sNum1,
sum(v.sNum1*v.sPrice1) as sPrice1,
sum(v.sNum2) as sNum2,
sum(v.sNum2*v.sPrice2) as sPrice2,
sum(v.sNum3) as sNum3,
sum(v.sNum3*v.sPrice3) as sPrice3,
sum(v.sNum4) as sNum4,
sum(v.sNum4*v.sPrice4) as sPrice4,
sum(v.sNum5) as sNum5,
sum(v.sNum5*v.sPrice5) as sPrice5,
sum(v.sNum6) as sNum6,
sum(v.sNum6*v.sPrice6) as sPrice6,
sum(v.sNum7) as sNum7,
sum(v.sNum7*v.sPrice7) as sPrice7,
sum(v.sNum8) as sNum8,
sum(v.sNum8*v.sPrice8) as sPrice8,
sum(v.sNum9) as sNum9,
sum(v.sNum9*v.sPrice9) as sPrice9,
sum(v.sNum10) as sNum10,
sum(v.sNum10*v.sPrice10) as sPrice10,
sum(v.sNum11) as sNum11,
sum(v.sNum11*v.sPrice11) as sPrice11,
sum(v.sNum12) as sNum12,
sum(v.sNum12*v.sPrice12) as sPrice12
from(select datepart(month,sDateTime) as pMonth,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=1 then sNum else 0 end as sNum1,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=1 then sPrice else 0 end as sPrice1,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=2 then sNum else 0 end as sNum2,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=2 then sPrice else 0 end as sPrice2,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=3 then sNum else 0 end as sNum3 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=3 then sPrice else 0 end as sPrice3 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=4 then sNum else 0 end as sNum4 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=4 then sPrice else 0 end as sPrice4,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=5 then sNum else 0 end as sNum5 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=5 then sPrice else 0 end as sPrice5,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=6 then sNum else 0 end as sNum6 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=6 then sPrice else 0 end as sPrice6,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=7 then sNum else 0 end as sNum7 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=7 then sPrice else 0 end as sPrice7,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=8 then sNum else 0 end as sNum8 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=8 then sPrice else 0 end as sPrice8,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=9 then sNum else 0 end as sNum9 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=9 then sPrice else 0 end as sPrice9,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=10 then sNum else 0 end as sNum10 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=10 then sPrice else 0 end as sPrice10,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=11 then sNum else 0 end as sNum11 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=11 then sPrice else 0 end as sPrice11,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=12 then sNum else 0 end as sNum12 ,
case when datepart(YEAR,sDateTime)=@dbdate and datepart(month,sDateTime)=12 then sPrice else 0 end as sPrice12
from tbSalesInfo where StoresID=@inyStoresID) as v
group by datepart(month,v.pMonth)
end
select * from #tbReprotOfStaff
Drop table #tbReprotOfStaff;
END
⑦ sql 怎么查询每一年1到12个月的数据
工具/材料：Management Studio。
1、首先在桌面上，点击“Management Studio”图标。
⑧ sql如何实现 连续12个月对应数据求和
select
sum(case when Year(时间)=2014 then 统计列 else 0 end ) as 1-12月,
sum(case when Month(时间)=1 then 统计列 else 0 end ) as 1月,
sum(case when Month(时间)=1 or Month(时间)=2 then 统计列 else 0 end ) as 1-2月,
....
from 表
⑨ sql如何一次查询得出十二个月中每个月的销售额一列显示。
select
地区
, sum(case when 月=1月 then 销售额 else 0 end ) as sum_1
,sum(case when 月=2月 then 销售额 else 0 end ) as sum_2
,sum(case when 月=3月 then 销售额 else 0 end ) as sum_3
,sum(case when 月=4月 then 销售额 else 0 end ) as sum_4
,sum(case when 月=5月 then 销售额 else 0 end ) as sum_5
,sum(case when 月=6月 then 销售额 else 0 end ) as sum_6
,sum(case when 月=7月 then 销售额 else 0 end ) as sum_7
,sum(case when 月=8月 then 销售额 else 0 end ) as sum_8
,sum(case when 月=9月 then 销售额 else 0 end ) as sum_9
,sum(case when 月=10月 then 销售额 else 0 end ) as sum_10
,sum(case when 月=11月 then 销售额 else 0 end ) as 搜索sum_11
,sum(case when 月=12月 then 销售额 else 0 end ) as sum_12
from 表
group by 地区
⑩ SQL数据库如何提取数据
程序有问题呀，你的查询返回了login表中的所有数据，在判断用户名是否存在表中时，只读取了第一条，只要第一条记录的用户名不是你输入的那个用户名，a==username就不会成立。要用一个循环依次读取所有记录来判断才行。
建议在查询时以用户名为条件查询，这样只在查询后检查dr是否返回数据就可以判断用户名存不存在了。
string
username
=
textbox1.text.tostring();
string
password
=
textbox2.text.tostring();
string
qq
=
"select
*
from
login
where
username='"
+
username
+
"'";
sqlconnection
conn
=
new
sqlconnection(connstring);
conn.open();
sqlcommand
cmd
=
new
sqlcommand(qq,
conn);
sqldatareader
dr
=
cmd.executereader();
if
(dr.hasrows)
//判断dr是否返回了一行或多行数据
{
messagebox.show("用户名已存在，请重新输入!");
}
else
{
。。。。。。
}