① 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
{
。。。。。。
}