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 ， 那么很不省事：
没法简单的截取 日期字段的 小时部分，来处理了。