|
My question is based on the number of vehicles "brand" sales statistics(The first layer is the "car" vehicle classification), Please use the recursive query(Also can use the right connections), query results: --Tbale 1: drop table PRODUCT_CAR; create table PRODUCT_CAR(Code varchar(100),Description varchar(100),parent_id varchar(20)) insert into PRODUCT_CAR select * from ( select '0' as code, 'car' as Description,'' as parent_id union all select '1' as code, 'BMW' as Description,'0' as parent_id union all select '2' as code, 'BENZ' as Description,'0' as parent_id union all select '3' as code, 'VW' as Description,'0' as parent_id union all select '4' as code, 'Hummer' as Description,'0' as parent_id union all select 'B5' as code, 'BMW 5 series' as Description,'1' as parent_id union all select 'B6' as code, 'BMW 6 series' as Description,'1' as parent_id union all select 'B7' as code, 'BMW 7 series' as Description,'1' as parent_id union all select 'Z1' as code, 'BZ Home Series' as Description,'3' as parent_id union all select 'Z2' as code, 'BZ Sercial series' as Description,'3' as parent_id union all select '1A' as code, 'BZ A series' as Description,'Z1' as parent_id union all select '1B' as code, 'BZ B series' as Description,'Z1' as parent_id union all select 'ZS' as code, 'BZ S series' as Description,'Z2' as parent_id union all select 'V1' as code, 'VW Home Series' as Description,'ZS' as parent_id union all select 'V2' as code, 'vw Sercial series' as Description,'3' as parent_id union all select 'V3' as code, 'AUDIO Sercial series' as Description,'3' as parent_id union all select 'V001' as code, 'VW SUVS series' as Description,'V1' as parent_id union all select 'VA01' as code, 'AD HOME Sercial' as Description,'V3' as parent_id union all select 'VA02' as code, 'AD SUVS Sercial' as Description,'V3' as parent_id union all select 'VA21' as code, 'AD A Sercial' as Description,'V3' as parent_id union all select 'VA211' as code, 'AD Q Sercial' as Description,'VA02' as parent_id ) a; SELECT * FROM PRODUCT_CAR ORDER BY CODE ASC; --Table 2 create table SEll_CAR( Car_Code varchar(100), Description varchar(100), quantity numeric(10), product_code varchar(20) ); insert into sell_car select * from ( select 'B53001' as Car_code , 'BMW 535 LI luxury cars' as Description, '21' as quantity, 'B5' as product_code union all select 'B75001' as Car_code , 'BMW 750 LI XDriver' as Description, '200' as quantity, 'B7' as product_code union all select '21101' as Car_code , 'AD Q7 luxury cars' as Description, '30' as quantity, 'Va211' as product_code union all select 'VA211' as Car_code , 'AD A6 LI luxury cars' as Description, '2' as quantity, 'VA01' as product_code union all select 'VW1001' as Car_code , 'VW Cross polo ' as Description, '100' as quantity, 'V1' as product_code ) b; select * from sell_car; My question is based on the number of vehicles "brand" sales statistics(The first layer is the "car" vehicle classification), Please use the recursive query(Also can use the right connections), query results: PRODUCT_CAR.code PRODUCT_CAR.Description sell.Sell_SUM 1 BMW 221 2 BENZ 0 3 vw 132 4 Hummer 0 |
|
@mfkpie8 : Many of your other associated questions seem to be various attempts at trying to solve this very specific problem, and it seems that you are getting very confused in the details and errors of implementation, which are generating more and more questions that may not be directly helping you to answer this question. Let's take this particular problem and show how you can solve these types of problems by approaching them in a piece-by-piece fashion and by breaking them out into separate steps. In the future when posting to this forum, we will need to see:
For this question, it took a month to discover which initial query you were trying yourself to solve this problem - many people are not keen to "give" you an answer without seeing that you have at least attempted the problem yourself. It's also difficult for people to try and track what you're specifically doing over multiple questions - if you're working on just one problem, you should try and keep all of the details of the resolution to that one question, if the details are related. Finally, if you find that you are running into problems are are not happy with the responses or response times on the forum, we would encourage you to open a technical support case with technical support for direct assistance. Here's a general strategy you could use to solve this problem: Part #1: Create a hierarchical list of cars and associated codes, including a column that records the "top-level code" (which will be used later as an identifier to perform a WITH RECURSIVE carlist ( code, parent_id, tlid, description ) AS ( ( SELECT code, parent_id, code as tlid, description -- initial subquery FROM product_car WHERE parent_id = '0' ) -- level to start sum aggregate at UNION ALL ( SELECT p.code, p.parent_id, c.tlid, p.description -- recursive subquery FROM product_car AS p JOIN carlist AS c ON p.parent_id = c.code AND p.parent_id <> p.code) ) SELECT * FROM carlist; This generates a list, similar to: code,parent_id,tlid,description '4','0','4','Hummer' '3','0','3','VW' '2','0','2','BENZ' '1','0','1','BMW' 'Z1','3','3','BZ Home Series' 'Z2','3','3','BZ Sercial series' ... This is a pretty standard recursive query, other than the slight "top-level" code modification added - there are examples of this in the documentation (as has been mentioned previously). Part #2: Create associated quantities for each car 'type', using the first table, grouping by the 'top-level code' We can do this by using the hierarchical list of codes we just created and using multiple table expressions, transform it into a new result set with the quantities we're looking for: WITH RECURSIVE carlist ( code, parent_id, tlid ) AS ( ( SELECT code, parent_id, code as tlid -- initial subquery FROM product_car WHERE parent_id = '0' ) -- level to start sum aggregate at UNION ALL ( SELECT p.code, p.parent_id, c.tlid -- recursive subquery FROM product_car AS p JOIN carlist AS c ON p.parent_id = c.code AND p.parent_id <> p.code) ), quantities ( tlid, qty ) AS (SELECT tlid, SUM(COALESCE(quantity, 0)) as qty -- aggregate over "top-level" ids FROM carlist LEFT JOIN sell_car ON sell_car.product_code = carlist.code GROUP BY tlid) SELECT code, description, quantities.qty FROM quantities, product_car WHERE quantities.tlid = product_car.code ORDER BY code; The last This query now gives you your desired result set: 1,BMW,221 2,BENZ,0 3,VW,132 4,Hummer,0 hi jeff albion thanks :I read your key or playing for a new logo Why I can't upload back inside the image to upload: When I click: browsing the selected files inside is blank A: excuse me, do you use what chat tools (online
(07 Oct '13, 13:13)
mfkpie8
Replies hidden
If you're referring to uploading images to the forum directly, you need a reputation of '100' or more. Currently, you will need to post them in another location until you have a sufficient reputation level. Regarding "chat tools" for obtaining support, we can only directly communicate to customers with a technical support plan. This forum only provides indirect, non-priority support assistance. If you wish to have a "direct interaction" with a technical support representative, you have to raise a new message / case / incident within your support area underneath your support plan and a support engineer will then be in contact with you to help you work on your problem.
(07 Oct '13, 13:48)
Jeff Albion
|
Have you tried the suggestions given on your other question? These samples seem rather similar to your requirements...
@mfkpie8: Please respond with whole sentences, otherwise it's starting to feel quite useless to try to give advice...
You mean I suggest the same problem? Is the recursive query tree structure? Because is two tables so I should how to operate
Want to know the classified by car brand inquiries out of the first layer of car sales For car classification in table 1 Table 2 for the car sales quantity
According to the brand (layer 1) according to sales situation
declare @product_code char(10),@rowNR int set @rownr=1 select product_code,row_number() over(order by parent_id) as rowNR,parent_id into #FT_1 from product_code where parent_id='cp' --Perform statistical line number and display finished the first layer categories //select * from #ft_1 while (1=1) begin select @product_code=product_code from #ft_1 where rownr=@rownr --assignment if (@@rowcount=0) break --How to terminate without a line with recursive ps(product_code,description,parent_id) as (select product_code,description,parent_id from product_code where parent_id=@product_code -According to the first layer of the first child of the classification tree union all select b.product_code,b.description,b.parent_id from ps p join product_code b on p.product_code=b.parent_id ) --Recursive query until the end select shipper.customer_id,sum(sh.quantity),description=@parentid from (((select * from ps --Start with sales BiaoShen customer table product category table splicing statistics according to product category sales quantity union all select product_code,description,parent_id from product_code where product_code=@product_code ) a join part on a.product_code=part.product_code) join shipper_line sh on part.part_id=sh.part_id) join shipper on sh.trans_no=shipper.trans_no group by shipper.customer_id,description
set @rownr=@rownr+1 end
ERROR PLS Please help me to correction
ERROR PLS
Rewriting this as WATCOM dialect will resolve the error. That dialect ends statements with semicolons, uses SET rather than SELECT to assign values to variables. There are other differences but those appear to be the ones that are relevant to this SQL.
You can use WATCOMSQL procedure to help you with the rewrite.
In the meantime, I will investigate and report back findings.