Home>

Access the php file installed on the network server from Android,
Execute the SQL statement against MySQL running in the server from the php file.
The content of the question is the SQL statement part, and if there is no corresponding data in the specified column, only that part will contain null
I would like to obtain the results, but I couldn't find the relevant description.

Applicable source code
test table
+ -------- + ------- + ------- +
| a | b | c |
+ -------- + ------- + ------- +
| 1 | AAA | WWW |
| 2 | BBB | YYY |
| 3 | CCC | ZZZ |
+ -------- + ------- + ------- +
SELECT a, b FROM test WHERE c = 'WWW';
SELECT a, b FROM test WHERE c = 'XXX';
SELECT a, b FROM test WHERE c = 'ZZZ';
I want this answer.
+ -------- + ------- +
| a | b |
+ -------- + ------- +
| 1 | AAA |
| null | null |
| 3 | CCC |
+ -------- + ------- +

Isn't it possible to obtain the above result by modifying SQL statements?

  • Answer # 1

    If youLEFT JOINthe same table, you can get the result.
    The response may be terrible, but ...

    select
      t2.a,
      t2.b
    from
      test t1
        left join test t2 on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c
        and t1.c in ('WWW', 'XXX', 'ZZZ')
    order by
      t1.a;

    Moving sample:
    http://sqlfiddle.com/#!9/4b8179/6

  • Answer # 2

    SELECT
     CASE WHEN c = 'WWW' or c = 'ZZZ' THEN a ELSE null end as a,
     CASE WHEN c = 'WWW' or c = 'ZZZ' THEN b ELSE null end as b
    FROM
     DatabaseName.TableName;

  • Answer # 3

    A is like a key, so it shouldn't be null?

    select a, case when c in ('WWW', 'XXX', 'ZZZ') then b end as b
    from test table
    order by a

  • Answer # 4

    If you use the character group of the Where clause as a reference, how about putting it into the from clause and LEFT JOIN it?

    select
      test.a,
      test.b
    from (
      select 'WWW' as a_keyword, 1 as sort_no
      union select 'XXX', 2
      union select 'ZZZ', 3
    ) as t1
    left join test
      on t1.a_keyword = test.c
    order by t1.sort_no
    ;

    I think this handwriting will be troublesome, so I think that text will be generated with some tools.