Geek Answers Handbook

Difficult MySQL query instead of a Python script to achieve the result?

Before I write a Python script, I want to see if only MySQL can create the result.

I have a list of products:

PID Product ----------- 1 AAA 2 ABC 3 BAC 4 CAB 5 CBA

I have a list of companies ordering these products several times:

 CID PID ------- 1 1 2 3 1 5 3 2 1 1 2 3

Desired Result:

 CID AAA ABC BAC CAB CAB CBA --------------------------- 1 YY 2 Y 3 Y

How will I do this in Python?

  • Create a temporary table with columns (CID AAA ABC BAC CAB CAB CBA)
  • Run 2 cycles and update the desired table when the desired column matches.

It's just interesting to know if there is only a MySQL solution.

ps: This is just a sample, and the actual problem has several 100 products and several thousand companies. I created a temporary table for 100 products by transposing in Excel and converting it to a MySQL table.

The following is the approach I finally resorted to. Thanks for the feedback.

 ########### Python script to generate the MySQL query ############## #MySQL Connection String Goes here# #Generate MySQL 'CASE' logic cursor = db.cursor() if __name__ == '__main__': cursor.execute("select PID, Product from products") productlist = cursor.fetchall() for product in productlist: print ("max(case when PID = %s then 'Y' else '' end) as `%s`,") % (product[0], product[1]) db.close()

Use the generated request in the format suggested by the nickname.

 select cid, max(case when pid = 1 then 'Y' else '' end) as AAA, max(case when pid = 2 then 'Y' else '' end) as ABC, max(case when pid = 3 then 'Y' else '' end) as BAC, max(case when pid = 4 then 'Y' else '' end) as CAB, max(case when pid = 5 then 'Y' else '' end) as CBA from companies group by cid
+4
source share
5 answers
 select cid, max(case when pid = 1 then 'Y' else '' end) as AAA, max(case when pid = 2 then 'Y' else '' end) as ABC, max(case when pid = 3 then 'Y' else '' end) as BAC, max(case when pid = 4 then 'Y' else '' end) as CAB, max(case when pid = 5 then 'Y' else '' end) as CBA from companies group by cid
+3
source

The fact that the other answers seem to be dancing around is the fact that for a large number of products there is no practical way to do this only in MySQL.

See the answer to this question: Wrap rows in columns in MySQL

So the answer to your question is probably: go and write that Python script.

+2
source

Why do you have duplicate data? Twice there are 1 1 and 2 3.

I do not understand the desire to present data in this way. If this will represent your user interface, this is a bad idea. Stick to python, I say and do it in one cycle: 1) create a table 2) select data 3) foreach result in the results of the set select cid, pid ... mark table with 'Y'

0
source

This will result in all the results being combined into only 1 column instead of 5 (or 100):

 SELECT o.CID , GROUP_CONCAT( CASE WHEN d.dummy IS NOT NULL THEN ' Y ' ELSE ' ' END ORDER BY p.Product SEPARATOR ' ' ) AS Products FROM ( SELECT DISTINCT CID FROM Ordering ) AS c CROSS JOIN Product p LEFT JOIN (SELECT 1 AS dummy) AS d ON EXISTS ( SELECT * FROM Ordering AS o WHERE o.PID = p.PID AND o.CID = c.CID ) GROUP BY c.CID

If, however, you have 100 products and 10K companies, this can be as slow as hell.

0
source

I'm not sure about the desired result, but it's pretty easy to create something similar in MySQL usind GROUP BY , which will make it easier to create the desired view in Python:

 SELECT orders.cid, products.product FROM orders, products WHERE orders.pid=products.pid GROUP BY products.product
-1
source

More articles:


All Articles