A MySQL query problem, online, etc. will be able to paste immediately

Source: Internet
Author: User
Tags mysql query
MySQL table design Query

I'm so confused, I can't get around this bend.

I have two tables, the T1 table stores the data, and the T2 table stores the Chinese names of T1 field fields.

T1



T2 table



Now you want to write an SQL statement, you can find out the T1 table ID in (1,2,3,4,5) of the records of each field value and replace the field name to the T2 table corresponding to the text field name,

The results are similar to
Array
"Add when attacking" = 6,
"Crit Effect" = 7,
....
)

How should the statement be written? It was supposed to be easy, but I couldn't think of it.

Is there a problem with the table design? There's no better way


Reply to discussion (solution)

I don't see how the result array corresponds to the data in the table.

I'm sorry, the result array is not exactly calculated by the number I write

I'll write it again.

Array
"Add $n point damage when attacking" =>24,
"Health value recovery increased by $n points per second" =>48,
"Crit damage increased $n" =>27,
"Equipment Grade reduction $n" =>6,
"Gold drops increase $n" =>100
)

Of course, if we can find out
Array
0=> "Add 24 damage when attacking",
0=> "Health value recovery increased by 48 points per second",
0=> "Critical hit damage increased by 27",
0=> "Equipment class reduced by 6",
0=> "gold drop increased by 100"
)
But it seems unlikely ... There might be a problem with this design.

Array
0=> "Add 24 damage when attacking",
1=> "Health value recovery increased by 48 points per second",
2=> "Critical hit damage increased by 27",
3=> "Equipment class reduced by 6",
4=> "gold drop increased by 100"
)

The above is written 0 is wrong.

48 should be 43 ..... Waterfall Sweat.

Select SUM (col1), sum (col2), sum (col3), sum (COL4), sum (COL5) from T1 where ID in (1,2,3,4,5);

This makes it possible to query the sum of five col fields, but there are no rules behind your update, and it seems that you can only write dead.

Select SUM (col1), sum (col2), sum (col3), sum (COL4), sum (COL5) from T1 where ID in (1,2,3,4,5);

This makes it possible to query the sum of five col fields, but there are no rules behind your update, and it seems that you can only write dead.

This is a method, although the number of Col x may be dozens of but after all, not much change

But there is no statement that can "find out the fit of each item" directly?

That statement is the same as querying every item.

It feels like you have a problem with your watch design. This is a relationship between the two, the general design of 3 tables.
I'll think of you as an additional property of the weapon in the game:
Table A: Weapons table
ID Name
1 A
Table B: Property sheet
ID DESC
1 attack Attach N
2 Health Value Recovery n

Table C: Weapon attribute mapping table
ID a_id b_id N
1 1 1 24
2 1 2 30

The data in Table C shows the weapon a attack plus 24, health recovery 30

Personal humble opinion! Don't like to spray!

It feels like you have a problem with your watch design. This is a relationship between the two, the general design of 3 tables.
I'll think of you as an additional property of the weapon in the game:
Table A: Weapons table
ID Name
1 A
Table B: Property sheet
ID DESC
1 attack Attach N
2 Health Value Recovery n

Table C: Weapon attribute mapping table
ID a_id b_id N
1 1 1 24
2 1 2 30

The data in Table C shows the weapon a attack plus 24, health recovery 30

Personal humble opinion! Don't like to spray!

It's a property in the game. Yes, you're right, I made a record of the properties of each piece of equipment, maybe for more intuitive? I can't say for sure. I think about it. The form of three tables ...

That statement is the same as querying every item.

This method is not to say no, but if there are 100 fields to write this feeling is not very suitable .... I thought there was an order. You can automatically find all the fields and the.

Your result is "cross-table."
You need to assemble the query commands from the T2.
You can use stored procedures, or you can use PHP

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.