Table table1 data as follows, how to make the KEY value respectively different cross combinations
The select * from table1 data below

 
The KEY value 
1 blue 
1 red 
2 large. 
2 small 
61 fine 
61 ordinary article 

After cross combinations can display the

 
Field 1 2 
1 blue/large/boutique 
2 blue/large/ordinary article 
Three blue/small/boutique 
4 blue/small/ordinary article 
Five red/large/boutique 
6 red/large/ordinary article 
7 red/small/boutique 
8 red/small/ordinary article 

I just want to ask dynamic statement how to write, static statements I've written out, is that the data is how to realize dynamic, thank you, wait,

CodePudding user response:

Nobody CSDN on weekend?

CodePudding user response:

SELECT @ R:=@ R + 1 ` KEY `, t.v alue the FROM (
SELECT @ R:=0, CONCAT_WS ('/', t1. Value, t2. Value, t3. Value) value FROM (
SELECT '1' ` KEY `, 'blue' VALUE FROM DUAL UNION ALL
SELECT '1' ` KEY `, 'red' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'large' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'small' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'boutique' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'common goods' VALUE FROM DUAL) t1
The JOIN (
SELECT '1' ` KEY `, 'blue' VALUE FROM DUAL UNION ALL
SELECT '1' ` KEY `, 'red' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'large' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'small' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'boutique' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'common goods' VALUE FROM DUAL) t2 ON 1=1
The JOIN (
SELECT '1' ` KEY `, 'blue' VALUE FROM DUAL UNION ALL
SELECT '1' ` KEY `, 'red' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'large' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'small' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'boutique' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'common goods' VALUE FROM DUAL) t3 ON 1=1
WHERE a t1. ` KEY `='1' AND t2. ` KEY `='2' AND t3. ` KEY ` t='61');

CodePudding user response:

My data will be change, add it, add a black, how to make this static doesn't make any sense

CodePudding user response:

I don't have your list, so he had to write a temporary table, you can add several hundred models, add tens of billions of color is no problem,
As long as your group is 3 groups there will be no problem,
Your table name is table1, for example, my that temporary table, buffer your table name table1,
My temporary table:
(
SELECT '1' ` KEY `, 'blue' VALUE FROM DUAL UNION ALL
SELECT '1' ` KEY `, 'red' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'large' VALUE FROM DUAL UNION ALL
SELECT '2' ` KEY `, 'small' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'boutique' VALUE FROM DUAL UNION ALL
SELECT '61' ` KEY `, 'common goods' VALUE FROM DUAL)

CodePudding user response:

My group is change, like taobao clothing size, there are large and small, size, color, is likely to be male, female, is change,

CodePudding user response:

reference 5 floor zly22169846 reply:

my packet is variable, like taobao clothing size, there are large and small, size, color, is likely to be male and female, are changing,

If classification also continues to increase, this statement is difficult, however, it is best to say your demand, may be on other link to resolve the problem,

CodePudding user response:

Already done, using a cursor + SQL statements, the cartesian product of logic, thank you

CodePudding user response:

Functions or stored procedures

CodePudding user response:

Must want to use a stored procedure, the cursor loop

CodePudding user response:

Why must use stored procedures, why must cycle, simple complicated,
Look at the code below,
First of all, build table script,
The CREATE TABLE ` table1 ` (
` KEY ` VARCHAR (2) NOT NULL DEFAULT ',
` VALUE ` VARCHAR (3) the NOT NULL DEFAULT '
);
Then, insert the test data, you can on this basis, add, modify, delete data,
Insert into ` table1 ` (` KEY `, ` VALUE `) values
(' 1 ', 'blue'),
(' 1 ', 'red'),
(' 2 ', 'large'),
(' 2 ', 'small'),
(' 61 ', 'boutique'),
(' 61 ', 'common goods');
Finally, execute the following code, it is not a a separate execution, but, after all chose to perform together,
The SET @ R1:=0;
SET @ : R2=0;
The SET @ S:=';
SELECT CONCAT (" SELECT @ : R2=@ R2 + 1 S1, CONCAT_WS ('/', ", S1, S2 ') FROM ', S2, 'WHERE', S3, '; ') INTO @ S FROM (SELECT GROUP_CONCAT (' T ', R, '. ', 'VALUE') S1, GROUP_CONCAT (' TABLE1 ', 'T', R) S2, REPLACE (GROUP_CONCAT (' T ', R, 'KEY=', T.K EY), ', ', 'AND') S3 FROM (SELECT @ R1:=@ R1 + 1 R, T.K EY FROM TABLE1 T GROUP BY T.K EY) T) T;
PREPARE stmt1 FROM @ S;
The EXECUTE stmt1;
Have what problem please reply, let's study,