问题描述:

I'm using MySQL.

This is table name item_supplier

supplier_ID Item_ID Date Price QTY

1 1 2012-01-01 00:00:00 500.00 2

1 1 2012-01-03 00:00:00 450.00 10

2 1 2012-01-01 00:00:00 400.00 5

3 1 2012-05-01 00:00:00 500.00 1

I need a select query showing a table something like this.

supplier_ID 2012-01-01 2012-01-03 2012-05-01

1 500.00(2) 450.00(10) null

2 400.00(5) null null

3 null null 500.00(1)

or, at least,

supplier_ID 2012-01-01 2012-01-03 2012-05-01

1 500.00 450.00 null

2 400.00 null null

3 null null 500.00

I hope someone can help me on this or give me a hint.

网友答案:

If there aren't a finite number of dates that are known beforehand, then you can't do what you want in MySQL alone.

Your best bet is to get a table like:

+---------+------------+-------------+-------------+
| Item_ID | Date       | supplier_ID | price       |
+---------+------------+-------------+-------------+
|       1 | 2012-01-01 |           1 | 500.00 (2)  |
|       1 | 2012-01-01 |           2 | 400.00 (5)  |
|       1 | 2012-01-03 |           1 | 450.00 (10) |
|       1 | 2012-05-01 |           3 | 500.00 (1)  |
|  ...    |    ...     |     ...     |    .....    |

Which can be done with:

SELECT Item_ID,Date,supplier_ID,CONCAT(FORMAT(Price,2),' (',QTY,')') AS price 
FROM item_supplier 
ORDER BY Item_ID,Date,supplier_ID;

Then on the C# side, loop through the results and print your desired output. Since the output is now sorted by Item_ID, Date, and then supplier_ID, it's simple to loop through the results and then output in the format you want.

网友答案:

First of My SQL does not support to Crosstab/Pivot Query. So you need to create Dynamic temp Table for Columns and then inset record into it. Like, First you have to fetch all date in one cursor and then create temp table and insert columns based on date's Cursor . After Creating table create another cursor for inserting rows. and fetch every row and update temp table.I have also done this using this way.... If you have any query please contact.

相关阅读:
Top