sumif criteria

the male clerk could not enter it.Select Top State,joinstate,(Case if state=1 and joinstate=0 then 2 if state=1 and joinstate=1 then 1 else 0 end) as Usestate from UserInfo(2)Select Id,username,namer= (Casewhen (scoreWhen (score> ' "and scoreWhen (score> ' andscoreWhen (score> ' "andscoreWhen (score> ' "andscoreWhen (score> ' andscoreWhen (score> ' andscoreWhen (score> ' andscoreWhen (score> ' 10000 ' andscoreWhen (score> ' 20000 ' andscoreElse ' Health ambassador ' End), (SELECT count (ID)From

Label:BEGIN forL_recordinch(SelectRecord_id,curr_period,period_start_date, (sysdate-Period_start_date) Difftime, (p.period_end_day-P.period_begin_day) Perioddiffday fromCps_patient_path_record T1Inner JoinCps_treat_period P onp.period_id=t1. Curr_periodwhereT1.path_state=1) LOOPIF(L_record. Difftime>=l_record.perioddiffday) Then UPDATECps_patient_path_recordSETPeriod_start_date=To_date (To_char (Sysdate,'YYYY/MM/DD'),'YYYY/MM/DD'), Curr_period=L_record. Curr_period+1 WHERErecord_

Label:The columns of table two are updated to table one (same library, cross-Library), and the A and B tables are associated by field Empid. Requires that updates between the associated records need to be one by one corresponding to the relationship. The statements are as follows:Update Set = from where = Table Two. EMPIDTo actually apply the SQL statement:Update Crm.dbo.Crm_Project Set CRM: Crm_project.projectid= from crm.dbo.crm_project,pm. Project where CRM: Crm_project.prjcode=PM.. Project.

Tags: a max begin sel str des proc new PRENo nonsense, First direct code First the SQL section (I add a @zx here as a judgment, generally do not need this parameter): ALTER Proc [dbo].[Proc_boss_show] @StrIndex varchar( -),--number of start bars @EndIndex varchar( -),--End Bar Number @StrWhere varchar(Max), @zx int --execution interval 1 execution top N 2 display all data as begin Declare @strSqlstr varchar( -) if(@zx=1) begin Set @strSqlstr='SELECT * FROM (sele

Original: http://blog.csdn.net/qing_gee/article/details/41646503When your query condition is a string variable, what do you do, such as the string may be "0001ME,0002BL" or "0001ME", then how to get the desired result according to the condition, I will tell you the InStr method of the great.1. At the beginning, the string str is converted to the parameters of the in function, as followsSELECT * from Stocks_orderlist s where S.scode in ("0001ME", "0002BL") LIMIT 10; Select CONVERT (CONCAT ("\ '",

varchar (=null),//@Param_datatime Datetime=null, @Param_value Numeric (9) =null,//@Param_flag varchar (=null),//@P aram_action int//)//as//begin//if ( @Param_action =0)//Begin//INSERT INTO Water_alarm (MONITORING_MN, factor_id,//datatime,//value, Flag VALUES (@Param_monitoring_mn,//@Param_factor_id, @Param_datatime,//@Param_value,//@Param_flag); End/Else if (@Param_action =1)//Begin//

SELECT @GuiGe=ISNULL(Guige,"'),@DocumentNO=Documentno,@OrderNO=OrderNo,@WLNO=Wlno,@Color=Color,@ISFenMa=Isfenma from# #projectWHEREId=@id --Defining Variables DECLARE @line INT =0 SELECT @line=COUNT(0) from# #projectAWHEREDocumentno=@DocumentNO andOrderNo=@OrderNO andWlno=@WLNO andColor=@Color andIsfenma=@ISFenMa --Data modification operation if data

_mark>= 80Then set _level= "B"; When _mark>= 70Then set _level= "C"; When _mark>= 60Then set _level= "D"; ElseSet _level= "E"; End Case;End;//Set @result = "";//Call Marks_level_case (@result);//Select @result;// /* stored procedure while control structure */ delimiter // create procedure sums_while (in n int ( 4), inout sums Int ( 4 while n>0 do set sums = sums + n; Set n = N-1; end while end ; // set @result =0;call sums_while ( 10, @result); select @result; // CREATE PROC

Tags: display ISP audio find Ros Sof-name directory nbspLocate all the files in the root directory that begin with test and pass the lookup results as parameters to the RM-RF command for deletion:1, Find/-name "test*" |xargs RM-RF2, Find/-name "test*"-exec rm-rf {} \;3, RM-RF $ (Find/-name "test")If you want to specify a recursive depth, you can:1, Find/-maxdepth 3-name "*.mp3" |xargs RM-RF2, Find/-maxdepth 3-name "test*"-exec rm-rf {} \;3, RM-RF $ (Find/-maxdepth 3-name "test")This will only fi

CaseWhen condition 1 then result 1When condition 2 then result 2When condition 3 then result 3When condition 4 then result 4.........When condition n then result nELSE result XENDCase has two formats. Simple case function and case search function.--Simple Case functionCase SexWhen ' 1 ' Then ' men 'When ' 2 ' then ' women 'Else ' other ' END--case search functioncase If sex = ' 1 ' Then ' man 'When sex = ' 2 ' Then ' women 'Else ' other ' ENDFor example:SELECT ID, name, CJ, (case at CJ From stud

4Mathematics 99 5Language 60 1Language 70 2Language 3 Language 4 Language 90 5Rows selectedSql> Select Name,score,rank () over (order by score) TT from T; NAME score TT---------- ----- ----------Language 60 1Mathematics 67 2Language 70 3Mathematics 77 4Mathematics 78 5Language 80 6Language 80 6Mathematics 88 8Language 90 9Mathematics 99 10Rows selectedEveryone should understand! Oh! Next look at the appA: Dense_rank------------------query the top three of each courseSelect Name,score from (sele

Label:Recently did an export work, in the process of using Xshell, found in their own computer with the CMD command to export the DMP file, in the Xshell tool to export the times wrong.Commands executed on CMD: exp ***/*** @oracle file=d:\tbissamt.dmp tables= (tbissamt) query=\ "where trans_date> ' 20150814 ' \"Commands executed on Xshell: Exp ***/*** @oracle file=tbissamt.dmp tables=\ (tbissamt\) query=\ "where trans_date\> ' 20150814 ' \"Use the Xshell tool to export, the compiler in the use o

Label:Oracle considers NULL to be the largest. In ascending order, by default, the null value is sorted back. The descending sort, by default, is preceded by a null value. There are several ways to change this situation: (1) Convert NULL to a specific value using the NVL function or the Decode function (2) Use case syntax to convert NULL to a specific value (Oracle9i later version support. Similar to SQL Server):1. When a value is specified, specify the position of the sortSELECT * FROM (Select

//Encapsulate Query Criteria@Test Public voidTransmitparameter () {map map=NewHashmap(); //map.put ("Sid", "1");Map.put ("S_name",""); Test1 (Student.class, map); //may be based on filtering to query! Some conditions may be null } Public voidTest1 (Class classs, mapp) {Session session=startcfg.getsession (); Transaction T=session.begintransaction (); //Splicing SQL statementsStringBuffer SB =NewStringBuffer (); Sb.append (" from"+session.getses

How to Use DetachedCriteria and Criteria, detachedcriteria How to Use DetachedCriteria and Criteria /** Download statistics* @ Return*/Public String downloadStatistics (){Logger.info ("directory content statistics being downloaded ");String type = getServletRequest (). getParameter ("type ");DetachedCriteria dc = DetachedCriteria. forClass (TbXymlb. class). Add (Restrictions. and (Restrictions. isNotNull ("

This is my database quot; tb_item_paramCREATETABLE 'TB _ item_param '('id' bigint (20) NOTNULLAUTO_INCREMENT, 'item _ cat_id' bigint (20) DEFAULTNULLCOMMENT #39; product category ID #39;, 'param _ data' textCOMMENT #39; parameter data, in json format #39;, 'created' datetimeDEFAULTNULL, code mybatismysql database This is my database Tb_item_param create table 'TB _ item_param '('id' bigint (20) not null AUTO_INCREMENT, 'item _ cat_id' bigint (20) default null comment 'item category ID

1 //in the query condition, if there is a Chinese2 $keyword=UrlDecode(I ("Request.keyword"));3 if($keyword!=""){4 $Model=New\think\model ();5 6 //SQL query Criteria7 $condition[' name '] =Array(' like ', '% '.$keyword." %");8 $condition[' nickname '] =Array(' like ', '% '.$keyword." %");9 $condition[' telephone '] =Array(' like ', '% '.$keyword." %");Ten $condition[' _logic '] = ' OR '; One A $count=$Model->table (' UserInfo ')->where ($condition),Count()

applicable scenario : Multi-condition query case, as shown in:By setting the URL parameter, combined with the Rul parameter set by the data source control, a simple multi-criteria query can be made.JavaScript functions:"Text/javascript">//methods for setting URL parametersfunction Setparmsvalue (parms, parmsvalue) {varUrlstrings =document. URL; varargs =geturlparms (); varValues =Args[parms]; //If the parameter does not exist, add the param

){ /** Rawurlencode encode strings into URL-specific formats*/ $add[] =$field.‘ = '.Rawurlencode($value); if($field= = ' name '){ Echo"Filter SQL Criteria by Name field"; } // .... Depending on the circumstances of the different URL filtering processing//And then in the assembly of the current user to select the good conditions of the multi-condition query//And then in the assembly paging URL//finally a multi-co

Hibernate condition or query loop multiple or conditionsToday the company let me make a list query page, because or for hibernate unfamiliar just out of experience, code knock less, all led to all kinds of difficulties ... There is a multi-level menu bar on the left side of the page because of the need to use the or (or) in the SQL query condition, but because I am using hibernate, I have been searching online for the following code:1 This . Createcriteria (); 2 Criteria.add (restrictions.or (R