SQL 多条件查询多结果

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Mr_linjw/article/details/50894015
            var query = 'SELECT COUNT(*) as count_all , ' +
            		'SUM(Case when checkResult = 1 then 1 else 0 end ) as count_no, ' +
            		'SUM(Case when checkResult = 2 then 1 else 0 end ) as count_even, ' +
            		'SUM(Case when checkResult = 3 then 1 else 0 end ) as count_earn, ' +
            		'SUM(Case when checkResult = 4 then 1 else 0 end ) as count_lose, ' +
            		'checkTypeView as checkTypeView, '+
            		'checkNumber as checkNumber, '+
            		'FROM CheckList' +
            		' group by cellType'; 
这样查询结果将返回一个object对象,包含总数count_all,checkResult各不同的总数count_no,count_even,count_earn,count_lose,已经'checkTypeView 、'checkNumber 对象

展开阅读全文

条件查询结果页面分页的问题

07-14

————————构造查询条件REQrn$show_m_dep=trim($_GET['show_m_dep']);rn$show_m_pro=trim($_GET['show_m_pro']);rn$show_m_con=trim($_GET['show_m_con']);rn$show_m_tim1=trim($_GET['show_m_tim1']);rn$show_m_tim2=trim($_GET['show_m_tim2']);rn$show_m_kin=trim($_GET['show_m_kin']);rnrnecho $_GET[show_m_pro];rn$Req="1";rn//构造搜索条件1rnif($_GET[show_m_dep]=="99"||empty($_GET[show_m_dep]))rnrn //如果为空,查询条件不变rnrnelsern$Req.=" and M_dep = '$_GET[show_m_dep]'";rn//构造搜索条件2rnif(empty($_GET[show_m_pro]))rnrn //如果为空,搜索条件不变rnrnelsern$Req.=" and M_pro like '%$_GET[show_m_pro]%'";rn//条件3rnif(empty($_GET[show_m_con]))rnrn //如果为空,搜索条件不变rnrnelsern$Req.=" and M_con like '%$_GET[show_m_con]%'";rn//条件4rn$temptime="";rnif($_GET[show_m_tim1]!="99"&&(!empty($_GET[show_m_tim1])))rnrn $temptime.=$_GET[show_m_tim1];rn if($_GET[show_m_tim2]!="99")rn rn $temptime.=$_GET[show_m_tim2];rn rn $Req.=" and M_tim like '$_GET[temptime]%'";//只有年份或者年份月份都有rnrn//条件5rnif($_GET[show_m_kin]=="99"||empty($_GET[show_m_kin]))rnrn //如果为空,搜索条件不变rnrnelsern$Req.=" and M_kin like '$_GET[show_m_kin]'";rn$_SESSION[Req]=$Req;rn_____________________查询 打印表格内容rn$sql="select count(M_id) from myfile WHERE $Req";rn.....rn—————————分页页面链接rnfor($i=1;$i<=$pageCount;$i++)rnecho "$i ";rnrnrn____________rn大致代码如上,因为构造Req的时候从上一个界面get的内容不一定都能用上,所以最后在分页页面链接中Req感觉没法用几个GET的值来拼出来,用SESSION存似乎也不行,点页码跳转时req又变回初始的1了,因此就想请教一下这种时候查询的条件该怎么写才能在分页页面跳转时保存下来?rn 论坛

求助,sql问题 -->多条件查询

04-27

[code=SQL]ALTER PROCEDURE [dbo].[HomeAdmin_TaskProgress_SelectAllTaskBySomeCondition_p]rn @TaskListId INT rn,@TaskContent NVARCHAR(100)=NULLrn,@TypeId INT=0rn,@PlannerId INT =0rn,@TransactorId INT =0rn,@FinishState INT =0rn,@FinishTime DATETIME =NULLrn,@OrderByName NVARCHAR(50)rn,@OrderBy NVARCHAR(10)rnAS rnBEGINrn DECLARE @strSql nvarchar(2000)rn set @strSql='rn SELECT rn TaskId,rn TaskListId,rn TaskTitle,rn TaskContent,rn TypeId,rn PlannerId,rn TransactorId,rn FinishState,rn FinishTime,rn [Plan],rn Art,rn Cooperation,rn Youxian,rn CreateAdministratorID,rn CreateAdministratorName,rn CreateTimernFROM TaskProgress_Task_trn WHERE [DISABLE]=0 AND 1=1 AND TaskListId='+cast(@TaskListId as varchar(10))+' rn AND ('+@TaskContent+' IS NULL OR TaskContent LIKE ''%'+@TaskContent+'%'')rn AND ('+cast(@TypeId as varchar(10))+'=0 OR TypeId='+cast(@TypeId as varchar(10))+')rn AND ('+cast(@PlannerId as varchar(10))+'=0 OR PlannerId='+cast(@PlannerId as varchar(10))+')rn AND ('+cast(@TransactorId as varchar(10))+'=0 OR TransactorId='+cast(@TransactorId as varchar(10))+')rn AND ('+cast(@FinishState as varchar(10))+'=0 OR FinishState='+cast(@FinishState as varchar(10))+') rn AND ('+cast(@FinishTime as varchar(20))+' IS NULL OR FinishTime='+cast(@FinishTime as varchar(10))+') rn ORDER BY '+ @OrderByName +' '+@OrderByrn rn EXEC @strSqlrn rnEND[/code]rn上面是存储过程,求助各位了rn [color=#FF0000]报错 消息 2812,级别 16,状态 62,过程 HomeAdmin_TaskProgress_SelectAllTaskBySomeCondition_p,第 42 行rn找不到存储过程 ''。[/color]rn@TaskContent ,@TypeId ,@PlannerId ,@TransactorId ,@FinishState ,@FinishTime rn我查询的时候根据这几个参数查询,不会知道哪个参数会传值过来 也许会一个值也没有 rn不知道我说清楚没 谢谢rn 论坛

sql语句多条件查询

04-02

在表user里有一个字段Status,值为0和1,现在想添加一个条件,如果为1则继续查询,为0则不显示查询,rn我这样写sql="select * from [user] WHERE usernum Is Not Null and Status="1""提示报错,求解rn[code=html]rn<%rnpages = 50rnset rs=server.createobject("adodb.recordset")rnsql="select * from [user] WHERE usernum Is Not Null"rnif request("show")<>"yes" then sql=sql+usersql rn sql=sql+" order by UserQQ"rn rs.open sql,conn,1,3rn if rs.eof and rs.bof thenrn response.write "暂时没有学生" rn'response.end rn rnelsern totalrs=rs.RecordCountrn rs.pageSize = pagesrn allPages = rs.pageCountrn page = Request("page")rnIf not isNumeric(page) then page=1rnif isEmpty(page) or int(page) <=1 thenrn page = 1rnelseif int(page) > allPages thenrn page = allPages rnend ifrnrs.AbsolutePage = pagern%>rnrn rn rn <%rnresponse.write " 总人数"&totalrs&""rn%>rnrn rn<% i=1rn Do While Not rs.eof and pages>0 rnusertype=rs("usertype")rnif usertype="2" thenrnusertype="管理员"rnelseif usertype="3" thenrnusertype="班长"rnelseif usertype="4" thenrnusertype="副班长"rnelseif usertype="5" thenrnusertype="团支书"rnelseif usertype="6" thenrnusertype="委员级别"rnelsernusertype="普通学生"rnend ifrn%>rn rn rn <%=rs("UserName")%>rn <%=split(rs("HomePhone")," ")(0)%>rn rn rn<%if i mod 2 <>0 then%>rn <%end if%>rn <% if i mod 2 =0 then%>rn rn rn <%end if%>rn<% rnrs.movenextrnpages = pages - 1rni=i+1rnif rs.eof then exit dornlooprnend ifrn%>rn rnrnrn<%rnrs.closernset rs=nothing rn%>rn[/code]rn 论坛

没有更多推荐了,返回首页