I have a SQL case statement question.

I have a question because it is my first time to see the sql door. I knew that the case statement was used to set the conditional expression

CASE Column Name | Expression WHEN Conditional Expression 1 THEN Results 1 WHEN CONDITIONAL EXPRESSION 2 THEN RESULTS 2 ....... WHEN conditional expression nTHEN result n
END Value

I made it by referring to it like this.

select a,
case when test = '1' then test_time1 end value1,
case when test = '1' then test_time1 else test_time2 end value2,
from test_table where and test ='1' order by value1

What I'm curious about is that I have to express the value even when the case test = '1' value is 0, e.g.)

a             value1               value2          
-----------------------------------------------------
1 xx:xxx:xx:xx 1 value
2 None xx:xx:xx 0 value

To make it come out like this

from test_table where and test ='1' order by value1

This sentence needs to be corrected. If you change the value of test='1' to 0, the value of 0 comes out, but the value of 1 doesn't come out ;;; Or test = 0! It's the same if you add it like this... Please give me your guidance. ㅜ<

sql

2022-09-22 13:34

1 Answers

Are you saying that you want to make (value1, value2) look up when test is 1 and (None, value2) when test is 0?

If so, try the following query.

CREATE TABLE IF NOT EXISTS `test_table` (
  `a` int(6) unsigned NOT NULL,
  `test` int(3) unsigned,
  `value1` int(3) unsigned,
  `value2` int(3) unsigned,
  PRIMARY KEY (`a`)
) DEFAULT CHARSET=utf8;
INSERT INTO `test_table` (`a`, `test`, `value1`, `value2`) VALUES
  ('1', '1', '1', '2'),
  ('2', '0', '1', '3');

select a, 
case when test = '1' then value1 else 'None' end,
value2
from test_table where test in ('0', '1')

The above code can be executed at http://sqlfiddle.com.

2022-09-22 13:34

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656