Geek Answers Handbook

Basic sql: selecting the same column several times in the same query, when each event depends on a different condition where

What is the best way to fulfill this request. I have the following table

mytable with columns

xy 1 a 2 b 3 c

and I would like (in pseudo sql)

 select x as x1 ,x as x2, x as x3 from mytable where ????

when

 x1 is x where y=a x2 is x where y=b x3 is x where y=c

so I would like as a result

 1, 2, 3

I am currently using cte and a very large dataset, I am trying to reduce query time, is it always necessary to have 3 table scans?

+4
source share
8 answers

You must use 3 queries. It will be much faster with proper indexing when it joins itself. In addition, it will be more readable.

If you need one request, this could be so :)

 SELECT (SELECT x FROM table WHERE y=1) AS x1, (SELECT x FROM table WHERE y=2) AS x2, (SELECT x FROM table WHERE y=3) AS x3
+8
source

I would do the following:

 SELECT tableRowA.x as x1 tableRowB.x as x2 tableRowC.x as x3 FROM table as tableRowA, table as tableRowB, table as tableRowC WHERE tableRowA.y = 1 tableRowB.y = 2 tableRowC.y = 3

A bit is easier to understand and pull out extra information if you need multiple columns from each row

+5
source

In the above example, there are only 3 lines of input and one line of output. I assume that at least one other column will be involved, so the input is:

 wxy --------- w1 1 a w1 2 b w1 3 c w2 4 a w2 5 b w2 6 c . . .

should be a day off:

 w x1 x2 x3 ----------- w1 1 2 3 w2 4 5 6 . . .

This can be done in one pass using the following query:

 select w, max(case when y = 'a' then x end) x1, max(case when y = 'b' then x end) x2, max(case when y = 'c' then x end) x3 from datatable where y in ('a','b','c') group by w
+2
source

Another solution:

 SELECT x, y FROM table WHERE y IN ('a', 'b')

You will have a result set:

 x | y ----- 1 | a 2 | b

This result can be used in the application to obtain the desired result.

+1
source

From your question, apparently, you would like the last three cases to be conditions, or to link three different conditions together. Will the following example satisfy your question:

 mytable: (unique keys 1..n) (col1) student-id | course-id | grade s1 gen101 g1 s1 cmp202 g2 s1 psy303 g3 s1 c4 g4 s2 c1 g5

Suppose we want students to have three special courses (gen101, cmp202, and psy303) and show that these classes ignore anyone else.

 select gen.student-id as student-id , gen.grade as gen101-gr , cmp.grade as cmp202-gr , psy.grade as psy303-gr from mytable gen , mytable cmp , mytable psy where gen.course-id = 'gen101' and gen.student-id = cmp.student-id and cmp.course-id = 'cmp202' and cmp.studnet-id = psy.student-id and psy.course-id = 'psy303'

This should give one line:

 student-id gen101-gr cmp202-gr psy303-gr s1 g1 g2 g3

Hope you have enough work.

+1
source 
 SELECT Case When y = 1 Then x1 When y = 2 Then x2 Else x3 End FROM mytable
0
source

My recommendation would be to select the results sorted or grouped by y column, and use this information to split the result set into multiple lists to process the application. If you want to do this only in the database, I am afraid that several table scans (or joins) are needed.

Another fix is ​​to transfer the information in column y to another table (with a link to a foreign key) in order to be able to join it more efficiently.

0
source

SQL Fiddle

MySQL 5.5.32 Schema setup :

 CREATE TABLE Table1 (`x` int, `y` varchar(1)) ; INSERT INTO Table1 (`x`, `y`) VALUES (1, 'a'), (2, 'b'), (3, 'c') ; CREATE TABLE mytable (`x` int, `y` varchar(1)) ; INSERT INTO mytable (`x`, `y`) VALUES (1, 'a'), (2, 'b'), (3, 'c') ;

Request 1 :

 SELECT x1.x as x1, x2.x as x2, x3.x as x3 FROM mytable x1 INNER JOIN mytable x2 ON x2.y='b' INNER JOIN mytable x3 ON x3.y='c' WHERE x1.y='a'

Results :

 | X1 | X2 | X3 | |----|----|----| | 1 | 2 | 3 |
0
source

More articles:


All Articles