Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the learning methods of sql sentences?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Super strong: the use of case...when...then...else...end conditional queries (different from where) and type conversion in the SQL command

Case...when...then...else...end, which precedes from, can change the value of a field in a record and cannot decide whether to display the record.

Where, which comes after from, cannot change the value of a field in a record, but you can decide whether to display the record.

Case...when...then...else...end, which can be used to sum multiple fields of the same record with branch judgment.

In addition, forced type conversion is often needed for field judgment and processing.

Select to_number ('19fame recording xxx') from dual;-Octal

Get 415.

Select to_number ('fogging and recording xx') from dual;-hexadecimal

Get 15

Select to_number ('123') from dual;-Decimal

Get 123

The two conversion functions are most closely related to the date operation: to_date () and to_char ().

The to_date () function converts a character type to a date type in a certain format:

Specific usage: to_date ('2004-11-27 mm maidd`)

The former is a string and the latter is a converted date format. Note that the two should correspond to each other. For example, to_date ('2004-11-27 13-34-43 to_date,' yyyy-mm-dd hh34:mi:ss') will get a specific time

String handling function:

Select to_number (SUBSTR (rain_1,0,2), 'xx') from obs

(1) Access database

You know that there is an iif function in access, which can simplify a sequence of judgment assignments into an expression, such as

Iif (a > b), if it is true that a > b, then the result is 1, otherwise it is 2. It's really convenient.

Example

(1) figures

If Measures.CurrentMember is an empty unit, the following example returns 0, otherwise 1

IIf (IsEmpty (Measures.CurrentMember), 0,1)

(2) string

If Measures.CurrentMember is an empty unit, the following string returns the string "Yes", otherwise the string "No"

IIf (IsEmpty (Measures.CurrentMember), "Yes", "No")

In Access, I can use the IIF function for statistical summary, for example, to know the actual number of users who should pay the fee.

Access: Select sum (iif (amount > 0,1)) as num from fee

MS SQL: select sum (case when amount > 0 then 1 else 0 end) as num from fee

(2) Ms SQL database

IIF is case when in SQL. Then. Else... End

Example: select id,case when bz='1' then xx when bz='2' then yy else zz end as tt from xxx

Let me give you an example. There is a table of policies and regulations (policy_fgxx), which includes ID (primary key), bzh (standard number), zynr (main content),

Fbrq (date of release) four fields

Select * From policy_fgxx result:

ID bzh zynr fbrq

13 001

Hello

2010-05-07 0:00:00

15 NULL

I don't want the above result, I want the following result:

ID bzh zynr fbrq

13 001

Hello

2010-05-07 0:00:00

15 No 1990-06-06 0:00:00

In other words, when the standard number is empty, I want it to show empty, and when the release date is null, I want it to show the date I specified.

Next, I'll give you a way to write it, so you can think for yourself.

Select id,zynr, (case when bzh='' then 'none' else bzh end) AS bzh, (case when fbrq is null then '1982-06-02' else fbrq end) AS fbrq From policy_fgxx

Sqlite case when then else end

Sqlite > select datetime (CreateTime, 'unixepoch',' localtime') time, Message content, case Des when 0 then'Le 'else' Garden 'end name from Chat_af863d30e1c1e5eba27a0df37a75dba0 where rowid

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.