Table 1:
Table 2:
How do I create a view returns t_id, p_id, Name, number_of_times,
One number_of_times refers to the number of transactions in a transaction to meet a few times the number of sales promotion,
Such as t_id 1 bought 1 bag of 6 cans lemonade, but sales for two to form a promotion, so t_id 1, p_id1 this line number_of_times for 0.
In t_id 2 bought 5 packs of 6 cans of lemonade, 5/2=2.5, meet two promotions, so t_id 2, number_of_times p_id 1 to 2,
In t_id 1 bought two cans of two litres of milk, milk promotion requirement to buy two cans of reached a promotion, so 2/2=1, t_id number_of_times p_id 4 to 1,
This will help leaders how to write in MySQL
CodePudding user response:
The SELECT A.T _ID, p. _ID, B.N AME, c.a. MOUNT/B.A MOUT AS NUM_OF_TIMES
The FROM
(SELECT DISTINCT T_ID FROM TABLE_1) AS A
The JOIN
(SELECT * FROM TABLE_2) AS B ON 1=1
LEFT the JOIN TABLE_1 AS C ON A.T _ID=C.T _ID