sqland12

『壹』 關於sql，一個多條件OR，AND，查詢的問題，請高人幫幫忙！

但是，如果加入模糊查詢的OR語句，就會全部查出來，前面寫的AND條件也就失效了！ ???

這個問題用子查詢來完成..

『貳』 請問SQL查詢語句有長度限制嗎

把字元串拆開~引號里的長度有限制，拆成連個引號，再連接起來
SQLstr = "select iperiod 會計期間,ino_id 憑證編號,iflag ,cdigest 摘要,ccode 科目,md 借方金額, mc 貸方金額,cdept_id 部門,ccus_id 客戶,ccode_equal 對方科目"
+ " from UFDATA_005_2008.dbo.GL_accvouch WHERE (iflag Is Null) AND (iperiod Between 1 AND 12 ) AND ( ccode =10101)AND ( ccode_equal LIKE '%52102%') order by iperiod,ino_id"

『叄』 sql 怎麼查詢每一年1到12個月的數據

工具/材料：Management Studio。

1、首先在桌面上，點擊「Management Studio」圖標。

『肆』 在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中的BETWEEN請教

select*from表where(proceyearbetween12and13)
and(proceweekbetween11and12)

『陸』 SQL語言中按月查詢

select * from pop where year(pdate)=年份 and month(pdate)>1 and month(pdate)<3

『柒』 access sql語句zhong 可以用幾層or和and

access中，sql語句只要邏輯關系正確，無論幾層or和and都是可以的。

如學生表中有以下數據：

『捌』 sql 查詢12個月

創建測試表

createtablevouch
(cyearint,
iperiodint)
insertintovouchvalues(2012,8)
insertintovouchvalues(2012,9)
insertintovouchvalues(2012,10)
insertintovouchvalues(2012,11)
insertintovouchvalues(2012,12)
insertintovouchvalues(2013,1)
insertintovouchvalues(2013,2)
insertintovouchvalues(2013,3)
insertintovouchvalues(2013,4)
insertintovouchvalues(2013,5)
insertintovouchvalues(2013,6)
insertintovouchvalues(2013,7)
insertintovouchvalues(2013,8)
insertintovouchvalues(2013,9)

執行

selectt.cyear,t.iperiod
from
(selectcyear,iperiod,row_number()over(orderbycyeardesc,iperioddesc)rnfromvouch)t
wherern<=12orderbycyear,iperiod

結果

『玖』 一條SQL語句的長度有什麼限制嗎

把字元串拆開~引號里的長度有限制，拆成連個引號，再連接起來
SQLstr = "select iperiod 會計期間,ino_id 憑證編號,iflag ,cdigest 摘要,ccode 科目,md 借方金額, mc 貸方金額,cdept_id 部門,ccus_id 客戶,ccode_equal 對方科目"
+ " from UFDATA_005_2008.dbo.GL_accvouch WHERE (iflag Is Null) AND (iperiod Between 1 AND 12 ) AND ( ccode =10101)AND ( ccode_equal LIKE '%52102%') order by iperiod,ino_id"

『拾』 SQL語句怎麼寫（查詢不在這個時間段的內容， 如不在7-8 10-12 2-3點的數據）

不在8-9點並且不在10-12點
你這個 不在8-9點, 意思是 8:00-9:00 ? 還是 8:00 - 8:59:59 ?

如果是 8:00 - 8:59:59 ， 那麼很省事：

不知道是什麼資料庫
如果是 SQL Server 的話：
WHERE
DATEPART(hh, 日期欄位) NOT IN (8, 10, 11)
也就是 小時的數字， 不是 8， 10， 11

如果是 Oracle 的話:
WHERE
TO_CHAR( 日期欄位, 'HH24') NOT IN ('08', '10', '11')

如果是 MySQL 的話，更省事了
WHERE
HOUR(日期欄位) NOT IN (8,10,11)

如果是 如果是 8:00 - 9:00 ， 那麼很不省事：
沒法簡單的截取 日期欄位的 小時部分，來處理了。